Menu
I am Working around MySQL error “Deadlock found when trying to get lock; try restarting transaction”
I found out that the transaction can be safely reattempted
Deadlocks are not dangerous. Just try again.
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
There was a single "super insert..select" statement that inserted into the bottleneck table after selecting a joined combo of two other tables and using sub-statements conditions on the bottleneck table as well as a few tiny tables.
The bottle neck was "waiting for lock", it was waiting for write lock no doubt. InnoDB seemed the perfect fit. (for write-heavy table) And the hesitations on this one statement are now all gone.
But now the problem arises: For every 100 executions of the statement, about 1 or 2 of them will fail because of the deadlock. I believe the deadlock only occurs between instances of the "super insert..select". I will install an auto retry of that statement, but I wondered
Question: Does MySQL JDBC have an option to turn on auto-retry of statements after receiving the exception, or do I have to write my own code for that?
com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
In this case, I do not have start transaction or stop transaction commands. Just that one "super insert..select" statement
800
To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, to avoid deadlock you must acquire lock always in the same order even in different transaction (e.g. always table A first, then table B).
5.2 Deadlock Detection. InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable.
With the implementation of the InnoDB engine, MySQL offers a simplified and easy way to diagnose and better understand such deadlocks. The Innodb engine automatically detects it and kills one of the transactions, allowing one transaction to proceed and populating an error on the transaction that was rolled back.
You can repeat the statement but in a perfect world you should rollback your transaction and start it again. And your transactions would be short in that world :)
177
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
