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!