Deadlock on 2 inserts in MySQL

Question

I'm trying to get this query to work:

insert into `risks` (`created_at`, `description`, `metadata`, `notes`, `project_id`, `risk_number`, `updated_at`)
values (
           '2022-12-02 13:56:08.742',
           'g',
           '{\"origin\":\"Initial\"}',
           NULL,
           1,
           (select max(r.risk_number) + 1 from risks as r where `r`.`project_id` = 1 for update),
           '2022-12-02 13:56:08.742'
       );

There is a unique constraint on risk_number and project_id, so for a project all risk_numbers are unique.

When you run this query 3 times in a transaction, you will get a deadlock (start transaction 1, start transaction 2, start transaction 3, commit 1, commit 2, commit 3).

The show engine innodb status output:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-05 11:48:11 140425202587200
*** (1) TRANSACTION:
TRANSACTION 3940037, ACTIVE 11 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 26, OS thread handle 140425164801600, query id 15255 localhost 127.0.0.1 root optimizing
/* ApplicationName=WebStorm 2022.3 */ insert into `risks` (`created_at`, `description`, `metadata`, `notes`, `project_id`, `risk_number`, `updated_at`)
values (
           '2022-12-02 13:56:08.742',
           'g',
           '{\"origin\":\"Initial\"}',
           NULL,
           1,
           (select max(r.risk_number) + 1 from risks as r where `r`.`project_id` = 1 for update),
           '2022-12-02 13:56:08.742'
       )

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 90874 page no 6 n bits 184 index risks_project_id_risk_number_unique of table `riskchallenger`.`risks` trx id 3940037 lock_mode X locks gap before rec
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 0000000b; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 90874 page no 6 n bits 184 index risks_project_id_risk_number_unique of table `riskchallenger`.`risks` trx id 3940037 lock_mode X waiting
Record lock, heap no 117 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 4; hex 8000000d; asc     ;;
 2: len 4; hex 00000077; asc    w;;


*** (2) TRANSACTION:
TRANSACTION 3940036, ACTIVE 16 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 1
MySQL thread id 25, OS thread handle 140425503385152, query id 15245 localhost 127.0.0.1 root executing
/* ApplicationName=WebStorm 2022.3 */ insert into `risks` (`created_at`, `description`, `metadata`, `notes`, `project_id`, `risk_number`, `updated_at`)
values (
           '2022-12-02 13:56:08.742',
           'g',
           '{\"origin\":\"Initial\"}',
           NULL,
           1,
           (select max(r.risk_number) + 1 from risks as r where `r`.`project_id` = 1 for update),
           '2022-12-02 13:56:08.742'
       )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 90874 page no 6 n bits 184 index risks_project_id_risk_number_unique of table `riskchallenger`.`risks` trx id 3940036 lock_mode X
Record lock, heap no 117 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 4; hex 8000000d; asc     ;;
 2: len 4; hex 00000077; asc    w;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 90874 page no 6 n bits 184 index risks_project_id_risk_number_unique of table `riskchallenger`.`risks` trx id 3940036 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 0000000b; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

Previously I had tried this without the for update, which resulted in both transactions getting a shared lock (with select), and then both waiting for an exclusive lock (for insert), but not being able to get one because of the shared lock held by the other transaction.

I've also tried this with for update and set transaction isolation level read committed. This worked, but resulted in duplicate key errors on risk_number, which makes sense as this is what the locks are there for.

Can someone point me to some solution so I can keep trying, as I'm at a loss for what else to try.

Thanks in advance!

Answer 1

It looks like you want risk_number to be an ordinal value per project, like auto-increment, but starting over at 1 for each project.

In general, you can't use the max() + 1 method of incrementing an id in InnoDB without either serializing the transactions, or else getting race conditions that cause the concurrent sessions to calculate duplicate keys.

MySQL has trouble with deadlocks vis. tables with two unique keys (e.g. a primary key and another unique key). It seems that it doesn't lock both unique keys atomically, which it should do. So there's a risk that concurrent transactions will have timing such that they each lock one of these indexes and then wait for the other transaction. Hence, deadlock.

The only way I've found to prevent this when using InnoDB is to force the transactions to execute serially by using LOCK TABLES. This acquires a table lock atomically, so there's no chance of the transactions interleaving their lock acquisition.

Unfortunately, using a table lock limits the concurrently advantage of InnoDB. It means concurrent transactions cannot insert concurrently. It's up to you to release the table lock as quickly as possible, but it can never be as quick as InnoDB's potential concurrency.

The MyISAM storage engine can do a primary key with an auto-increment that renumbers per project (or whatever other attribute in the primary key). But MyISAM achieves this in exactly the same way as I suggested — by using a table lock. That's the only way it can ensure that concurrent inserts aren't in a race condition.

The other solution is to forget about storing an ordinal risk_number. Just rely on the table's auto-increment to order the rows (aside from the timestamp, which is ordered but not unique, so there an be ties). If you need ordinals later, use the ROW_NUMBER() window function.