Deadlock handling in InnoDB

[Alex]

I was hoping someone could confirm my understanding of how InnoDB
handles deadlocks (error 1213) and timeouts (error 1206). The way I
understand it, with AUTOCOMMIT=0, if I issue 3 SQL statements
(updates), A, B, and C (in that order), and get one of the errors above
while issuing statement C, InnoDB will have rolled back statements A
and B. To recover from this error condition, I need to re-issue
statements A and B, followed by C. Then, if all three go through, I
can call COMMIT and have A, B, and C committed. Is this correct?

Thank you in advance,

Alex

[Bill Karwin]

Alex wrote:

I was hoping someone could confirm my understanding of how InnoDB
handles deadlocks (error 1213) and timeouts (error 1206). The way I
understand it, with AUTOCOMMIT=0, if I issue 3 SQL statements
(updates), A, B, and C (in that order), and get one of the errors above
while issuing statement C, InnoDB will have rolled back statements A
and B. To recover from this error condition, I need to re-issue
statements A and B, followed by C. Then, if all three go through, I
can call COMMIT and have A, B, and C committed. Is this correct?

Read these pages:
http://dev.mysql.com/doc/mysql/en/in...-handling.html
http://dev.mysql.com/doc/mysql/en/in...detection.html

"A transaction deadlock or a timeout in a lock wait causes InnoDB to
roll back the whole transaction."

That seems to support your understanding. Yes, if statement C in your
scenario causes a deadlock or timeout error, then the whole transaction
is rolled back. This includes the uncommitted work done in the same
transaction by the statements A and B.

The documentation admits that this behavior is not in accordance with
the SQL standard.

Regards,
Bill K.

[Alex]

Thank you, Bill. My understanding comes from the same docs you're
citing. I at one point was misinterpreting the above, thinking that
only statement C would need to be re-issued, so wanted to confirm that
my newfangled interpretation was correct. Sounds like there's at least
one other person who understands it the same way I do. :-)

Can anyone else offer a 'definitive' confirmation or refutal?

Thank you in advance,

Alex