Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction".

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

The story is here if you are curious.

share|improve this question
    
+1 for this is good question because it brings to light an InnoDB quirk most people are not aware of in conjunction with SELECTs against InnoDB tables. –  RolandoMySQLDBA Aug 8 '11 at 21:00
add comment

1 Answer 1

up vote 2 down vote accepted

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.

You also have special cases of SELECT where you can lock specific rows on demand.

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:

  • Your SQL UPDATEs a single row but generates an error
  • The UPDATE causes a rollback of the one row
  • The row has a lingering lock

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)

share|improve this answer
    
@Rolando - isn't it true with InnoDB's MVCC that reads never block (except when you use for update of course)? –  Jack Douglas Aug 9 '11 at 18:04
    
@Jack - If you read the links in the first 3 bullet points, you will see that MVCC ia not at issue. It is a deep deadlock in the clustered index that not even MVCC can mitigate. –  RolandoMySQLDBA Aug 9 '11 at 19:20
    
@Rolando - I've read the links a little more carefully and I still can't see any indication that that a normal select can lock rows or in any way be blocked by DML in another transaction. That is certainly true for Oracle and as far as I can tell it is true for InnoDB too? Excepting the case when the select statement actually does DML via a function or some other round-about route of course. –  Jack Douglas Aug 9 '11 at 19:55
    
@Jack - If SELECTs can blockade an index, how much more so an UPDATE as shown by my first 3 links. –  RolandoMySQLDBA Aug 9 '11 at 21:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.