Concurrent transactions occasionally fail with 'Deadlock found when trying to get lock; try restarting transaction' when two transactions lock the same rows in a different order.
Keep transactions short, always access tables and rows in a consistent order, add proper indexes to reduce lock scope, and add retry logic for the specific deadlock error code.
Step-by-Step Guide
Run SHOW ENGINE INNODB STATUS to inspect the last deadlock's details
Ensure all transactions touch tables/rows in the same consistent order
Keep transactions as short as possible - commit as soon as work is done
Add indexes on columns used in WHERE/JOIN clauses to shrink lock scope
Use SELECT ... FOR UPDATE deliberately, and only where truly needed
Add retry logic in the application layer specifically for error code 1213
Consider READ COMMITTED isolation if REPEATABLE READ is causing excess locking
Batch large UPDATE/DELETE operations into smaller chunks
Found an issue with this solution?
