Insert intention lock causes deadlock with composite primary key

Question

I would like to ask for help :)

Two INSERT statements are issued simultaneously by 2 users into a doctrine-generated many-to-many table:

INSERT INTO my_table (col1, col2) VALUES (2271885, 16678); <-- user 1
INSERT INTO my_table (col1, col2) VALUES (2271883, 2550); <-- user 2

A deadlock occurs (it is a write-heavy table), and the analysis reveals that both transactions attempt to write into the same space in the table:

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 178586 page no 9618 n bits 176 index PRIMARY of table `my_company`.`my_table`...

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 178586 page no 9618 n bits 176 index PRIMARY of table `my_company`.`my_table`...

I guess I am struggling to understand why the deadlock occurs in the first place. It is a classic doctrine many-to-many table, nothing fancy.

Yes, it is write-heavy, but then againt, the MySQL documentation clearly states this about insert intention locks:

Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

Why would MySQL calculate that (2271885, 16678) and (2271883, 2550) are going to end up in the same gap?

The only thing I can think of is that we are dealing with composite primary key instead of a simple integer? What is the solution here? Drop the primary key and create an auto-increment?

Thank you for your help.

Radferd

I am facing this. Did you find why this is happening or if there's a workaround? – Brunel

Answer 1

Okay, I have been searching for an answer for weeks but none coming up as well, but I think I finally have an answer to this.

First, this problem only happens to composite primary key as far as I observe. The obvious solution to this that someone did reply some other post is to lock the table with LOCK TABLE_NAME. This approach is valid and would solve the deadlock problem but in my case, it would cause another problem. By locking the entire table with such command would cause MySQL to end the transaction immediately before actually locking the table, which in a lot of business use cases, transaction is essential for data integrity.

What we could do instead, is to lock the entire table with command such as SELECT * FROM TABLE_NAME FOR UPDATE. Obviously that command itself would cause problem by selecting all the data from the table which will be a performance killer when the table data is large enough. But then, instead of that, we could use SELECT COUNT(*) FROM TABLE_NAME FOR UPDATE to achieve the same result, which will be always reading from the saved count index of the table and would cause minimal impact to the database. And here we have the solution. Before the actual insert for composite primary keys, we could use SELECT COUNT(*) FROM TABLE_NAME FOR UPDATE to lock the entire table and prevent such deadlock from happening.

TLDR; lock the entire table with the command SELECT COUNT(*) from TABLE_NAME FOR UPDATE;

Rondeau