In our case, we have two tables, table1 and table2, with a has_many relationship.

This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. in simple words If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.Īn insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. 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.

Mysql deadlock example