死锁复现
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t` */
insert into `t`(`id`,`c`,`d`) values (0,0,0),(5,5,5),(10,10,10)| 事务1 | 事务2 | |
| T1 | START TRANSACTION ; SELECT * FROM t WHERE id =3 LOCK IN SHARE MODE |
|
| T2 | START TRANSACTION ; SELECT * FROM t WHERE id =3 LOCK IN SHARE MODE |
|
| T3 | INSERT INTO t VALUES(3, 3,3);(阻塞) | |
| T4 | INSERT INTO t VALUES(3, 3,3);（死锁） |
死锁分析
- T1事务1加主键索引间隙锁（0,5）读锁成功
- T2事务2加主键索引间隙锁（0,5）读锁成功
- T3事务1加主键索引意向插入锁（0,5）阻塞
- T4事务2加主键索引意向插入锁（0,5）阻塞，进入循环等待，死锁
SHOW ENGINE INNODB STATUS 查看加锁信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-10 16:12:57 0x10bc
*** (1) TRANSACTION:
TRANSACTION 486543, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 7296, query id 2948 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486543 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** (2) TRANSACTION:
TRANSACTION 486544, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 4284, query id 2953 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486544 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486544 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS这里注意点，意向插入锁是没有读锁的，它是写锁性质的
上面的案例貌似不怎么贴合实际，因为程序里主动显示的加读锁是不常见的，下面复现一个间隙锁加写锁不冲突，而间隙锁与意向插入锁冲突死锁的例子(两个事务同时更新主键不存在的记录)。
| 事务1 | 事务2 | |
| T1 | START TRANSACTION ; UPDATE t SET d =0 WHERE id =3 |
|
| T2 | START TRANSACTION ; UPDATE t SET d =0 WHERE id =3 |
|
| T3 | INSERT INTO t VALUES(3, 3,3);(阻塞) | |
| T4 | INSERT INTO t VALUES(3, 3,3);（死锁） |
LATEST DETECTED DEADLOCK
------------------------
2024-03-10 16:27:22 0x10bc
*** (1) TRANSACTION:
TRANSACTION 486545, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 7296, query id 3007 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486545 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** (2) TRANSACTION:
TRANSACTION 486546, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 4284, query id 3012 localhost ::1 root update
INSERT INTO t VALUES(3, 3,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486546 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000046952; asc iR;;
2: len 7; hex 4b0000019207d7; asc K ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS另外两个事务一个加读锁间隙锁，一个加写锁间隙锁导致和意向插入锁冲突死锁的例子就不举了，原理跟上面一模一样。