I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement will produce deadlock errors? I know that it is only a read lock so multiple selects won't be a problem, but what if there is an INSERT/UPDATE/DELETE statement (with subqueries possible with joins) and a SELECT statement (possible with joins or subqueries)? Is it possible that the error will be thrown on the SELECT instead of the INSERT/UPDATE/DELETE
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The direct answer to your question's title is No.
SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.
Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.
UPDATE 2011-08-08 16:49 EDT
You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
Based on that statement, the sequences of event to cause this could theoretically be as follows:
Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)
UPDATE 2015-09-22 18:40 EST
Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:
This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.