Deadlock prevention and handling

from the Artful MySQL Tips List


1. The shorter the transaction, the less likely is a deadlock.

2. Use snapshot isolation, http://dev.mysql.com/doc/refman/5.7/en/commit.html.

3. A deadlock when multiple transactions try to lock the same keys in differing orders. To prevent that, ensure that keys are locked in the same order.

4. Avoid user interaction in transaction sequences.

5. In a busy system, deadlocks are not completely preventable, so transaction code [i]must[/i] include retry capability.



Return to the Artful MySQL Tips page