Background :
In MySQL 5.7.18 I have a table named 'test' defined as below:
| test | CREATE TABLE `test` (
`id` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `test_name_x01` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
It has only 1 row :
id | name
2 | eva
Now I start 2 transctions, both in REPEATABLE-READ isolation level, and do the commands as below:
- T1 : begin;
- T2 : begin;
- T2 : select * from test where name='eva' for update;
- T1 : select * from test where name='eva' for update; (now T1 holds)
- T2 : insert into test values (1, 'eva'); (dead lock, T1 is rolled back)
InnoDB log:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-05-06 20:24:28 0x126df8000
*** (1) TRANSACTION:
TRANSACTION 112142, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 139, OS thread handle 4950491136, query id 997169 localhost root Sending data
select * from test where name='eva' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 72 index test_name_x01 of table `promotion`.`test` trx id 112142 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 657661; asc eva;;
1: len 1; hex 32; asc 2;;
*** (2) TRANSACTION:
TRANSACTION 112141, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 138, OS thread handle 4947148800, query id 997170 localhost root update
insert into test values (1, 'eva')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 72 index test_name_x01 of table `promotion`.`test` trx id 112141 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 657661; asc eva;;
1: len 1; hex 32; asc 2;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 72 index test_name_x01 of table `promotion`.`test` trx id 112141 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 657661; asc eva;;
1: len 1; hex 32; asc 2;;
*** WE ROLL BACK TRANSACTION (1)
My thoughts
Based on my research, below is what I think might be the cause of deadlock. But need professional confirmation.
1. T1 : begin;
2. T2 : begin;
3. T2 : select * from test where name='eva' for update;
step 3 requires: IX lock, next-key lock on index 'name' (negative infinity, 'eva']
4. T1 : select * from test where name='eva' for update;
step 4 requires: IX lock, next-key lock on index 'name' (negative infinity, 'eva'] IX lock is compatible to IX, so T1 can acquire it without T2's release. next-key lock contains actually 2 parts: record X + gap, since conflicting locks can be held on a gap by different transactions, T1 also holds gap lock without waiting for T2. So T1 is only waiting for T2 to release the record lock(on secondary index name='eva' and clustered index id='1') to proceed.
5. T2 : insert into test values (1, 'eva'); (dead lock, T1 is rolled back)
step 5 : Here the insert requires insert intension lock, which is not compatible with gap lock. So T2 is waitting for T1 to release its gap lock. But meanwhile T1 is waitting for T2 to release record X lock.
========================================================================
Updates, found out more interesting facts that my explanation above cannot explain.
Moreover for step 5, after trying with different ID values, I have below observation :
If the table is preloaded with multiple rows with different IDs and with same name 'eva', deadlock is ONLY reproducible if the ID value try to insert in step 5 is less than the minimum ID of all existing rows.
For example preload table with
id | name
2 | eva
4 | eva
for step 5 above,
insert (0, 'eva') => deadlock
insert (1, 'eva') => deadlock
insert (3, 'eva') => NO deadlock
insert (5, 'eva') => NO deadlock
