Understand the basics of locks and deadlocks in MySQL (Part I)
Let’s not get scared of MySQL deadlock anymore
Deadlocks are difficult to be totally avoided in transactional databases like InnoDB in MySQL. When you first see a deadlock in your application, you may get scared by its error message and get overwhelmed by the logs shown by SHOW ENGINE INNODB STATUS. In this series of posts, the deadlocks in MySQL will be demystified, and you will learn to read the deadlock message and also solve deadlock problems in your application.
What is deadlock?
A deadlock is a situation when two or more transactions mutually hold and request a lock that the other needs. As a result, a cycle of dependencies is created and the transactions cannot proceed. By default, InnoDB automatically detects deadlocks and rolls back one transaction (the victim) to break the cycle. Normally, the transaction that infects a smaller number of rows will be picked.
What is a transaction?
In MySQL, a transaction is a group of statements that are performed as an atomic group, which means either all the statements run successfully, or none of them do.
Besides the atomicity property mentioned above, a transaction also has consistency, isolation, and durability, which as a whole are…
