If id is not indexed or has a nonunique index, the statement does lock the preceding gap.Ī next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. A gap might span a single index value, multiple index values, or even be empty. To understand the deadlock situation, let’s familiarize ourselves with the different types of locks involved, as defined by the official MySQL documentation:Ī gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. In this blog post, we will deep dive into the nature of these locks, understand their impact on transactions, and present the solutions we implemented to mitigate deadlock occurrences. Upon investigation, we discovered that it was due to the interplay of various locks in our MySQL database.
We were initially unsure about the cause of this deadlock. The deadlock occurred while attempting to insert scores into a table after completing a candidate’s test. Recently, during one of our tests, we encountered a deadlock issue that was reported by Sentry.
