mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
mysql> INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (24.79 sec)
查询死锁日志
***************************1.ROW***************************TYPE: InnoDB
Name:
STATUS:
=====================================2022-04-2120:14:58 0x7f021ce44700 INNODB MONITOR OUTPUT
=====================================
Per SECOND averages calculated FROM the LAST5 seconds
------------------------
LATEST DETECTED DEADLOCK
------------------------2022-04-2120:14:41 0x7f021ce44700
***(1)TRANSACTION:
TRANSACTION559396809, ACTIVE 51 sec inserting
mysql TABLESINUSE1, locked 1LOCK WAIT 3LOCK struct(s), heap SIZE1136,2ROWLOCK(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1765549133 localhost root UPDATEINSERTINTO t4(`kdt_id`,`admin_id`,`biz`,`role_id`,`shop_id`,`operator`,`operator_id`,`create_time`,`update_time`)VALUES('18','2','retail','2','0','0','0',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)***(1) WAITING FOR THIS LOCKTO BE GRANTED:
RECORD LOCKS SPACE id 5473 page no 4 n bits 72INDEX uniq_kid_aid_biz_rid OFTABLE`okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000014; ASC ;;
1: len 4; hex 00000001; ASC ;;
2: len 4; hex 00000001; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000002; ASC ;;
***(2)TRANSACTION:
TRANSACTION559396670, ACTIVE 61 sec inserting
mysql TABLESINUSE1, locked 13LOCK struct(s), heap SIZE1136,2ROWLOCK(s), undo log entries 1
MySQL thread id 3647800, OS thread handle 139647051384576, query id 1765551572 localhost root UPDATEINSERTINTO t4(`kdt_id`,`admin_id`,`biz`,`role_id`,`shop_id`,`operator`,`operator_id`,`create_time`,`update_time`)VALUES('15','1','retail','2','0','0','0',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)***(2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5473 page no 4 n bits 72INDEX uniq_kid_aid_biz_rid OFTABLE`okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000014; ASC ;;
1: len 4; hex 00000001; ASC ;;
2: len 4; hex 00000001; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000002; ASC ;;
***(2) WAITING FOR THIS LOCKTO BE GRANTED:
RECORD LOCKS SPACE id 5473 page no 4 n bits 72INDEX uniq_kid_aid_biz_rid OFTABLE`okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000014; ASC ;;
1: len 4; hex 00000001; ASC ;;
2: len 4; hex 00000001; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000002; ASC ;;
*** WE ROLL BACK TRANSACTION(2)------------
TRANSACTIONS
---------------TRANSACTION 559396809, ACTIVE 68 sec3LOCK struct(s), heap SIZE1136,3ROWLOCK(s), undo log entries 1
MySQL thread id 3647802, OS thread handle 139647067092736, query id 1765549133 localhost root cleaning up
TABLELOCKTABLE`okami`.`t4` trx id 559396809LOCK mode IX
RECORD LOCKS SPACE id 5473 page no 4 n bits 72INDEX uniq_kid_aid_biz_rid OFTABLE`okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000014; ASC ;;
1: len 4; hex 00000001; ASC ;;
2: len 4; hex 00000001; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000002; ASC ;;
Record LOCK, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000012; ASC ;;
1: len 4; hex 00000002; ASC ;;
2: len 4; hex 00000002; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000006; ASC ;;
RECORD LOCKS SPACE id 5473 page no 4 n bits 72INDEX uniq_kid_aid_biz_rid OFTABLE`okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000014; ASC ;;
1: len 4; hex 00000001; ASC ;;
2: len 4; hex 00000001; ASC ;;
3: len 6; hex 72657461696c; ASC retail;;
4: len 8; hex 0000000000000002; ASC ;;
分析死锁日志
事务1执行的SQL: INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
事务1持有的锁: (间隙锁) RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec
事务1等待获取的锁: (插入意向锁) RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396809 lock_mode X locks gap BEFORE rec INSERT intention waiting
事务2执行的SQL: INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
事务2持有的锁: (间隙锁) RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec
事务2等待获取的锁: (插入意向锁) RECORD LOCKS SPACE id 5473 page no 4 n bits 72 INDEX uniq_kid_aid_biz_rid OF TABLE `okami`.`t4` trx id 559396670 lock_mode X locks gap BEFORE rec INSERT intention waiting
事务1要获取插入意向锁,需要等待事务2释放间隙锁
事务2要获取插入意向锁,需要等待事务1释放间隙锁
造成死锁