死锁案例1-根据主键只删除也会出现死锁

set global tx_isolation='read-committed';

set global innodb_print_all_deadlocks=on;

set global innodb_status_output_locks=ON;

RC隔离级别。

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into t1 values(1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee'),(6,'ff');
delete from t1 where id=1; delete from t1 where id=6;
delete from t1 where id=2; delete from t1 where id=5;
delete from t1 where id=3; delete from t1 where id=4;
delete from t1 where id=4; delete from t1 where id=3;

mysql> select * from t1;
+----+------+
| id | c1 |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | ff |
+----+------+
6 rows in set (0.00 sec)

 

RC隔离级别
session1:	session2:
begin;delete from t1 where id=1;
	begin;delete from t1 where id=6;
delete from t1 where id=2;
	delete from t1 where id=5;
delete from t1 where id=3;
	delete from t1 where id=4;
delete from t1 where id=4;
	delete from t1 where id=3;

 

########################################################################################

解释说明：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-10 10:48:46 7f84bdac8700    #死锁时间
*** (1) TRANSACTION:      #事务1
TRANSACTION 82430953, ACTIVE 133 sec starting index read  #事务编号，事务活跃时间，事务状态-根据索引读取数据。（fetching rows 表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql）thread declared inside InnoDB 说明事务已经进入innodb层。）
mysql tables in use 1, locked 1   #当前事务使用1个表，有1个表锁。(意向锁是表锁IX)
LOCK WAIT 3 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 3  #锁等待，锁链长度为3，每个锁链表节点代表一个锁结构(包括表锁，记录锁或 autoinc 锁)。锁堆内存大小。当前事务有3条undo log记录。
MySQL thread id 30, OS thread handle 0x7f8483fff700, query id 602 localhost dball updating  #线程id  为30
delete from t1 where id=4    #事务最后一个sql（等待着的sql），查看不到事务完整的SQL。
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  #事务想要获取的锁
RECORD LOCKS space id 2994 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 82430953 lock_mode X locks rec but not gap waiting #行锁空间id,页号，锁结构上留有80个bit位。（记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap ;  间隙锁（LOCK_GAP）: lock_mode X locks gap before rec;  Next-key 锁（LOCK_ORNIDARY）: lock_mode X;  插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention）
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000004; asc ;;    #主键
1: len 6; hex 000004e9cbee; asc ;;  #事务id号，16进制
2: len 7; hex 1d000001c62882; asc ( ;; #回滚指针
3: len 2; hex 6464; asc dd;;  #列值

*** (2) TRANSACTION:  #事务2
TRANSACTION 82430958, ACTIVE 118 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 3
MySQL thread id 31, OS thread handle 0x7f84bdac8700, query id 603 localhost dball updating
delete from t1 where id=3
*** (2) HOLDS THE LOCK(S):#持有锁的信息
RECORD LOCKS space id 2994 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 82430958 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000004e9cbee; asc ;;
2: len 7; hex 1d000001c62882; asc ( ;;
3: len 2; hex 6464; asc dd;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000004e9cbee; asc ;;
2: len 7; hex 1d000001c6285f; asc (_;;
3: len 2; hex 6565; asc ee;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000006; asc ;;
1: len 6; hex 000004e9cbee; asc ;;
2: len 7; hex 1d000001c6283c; asc (<;;
3: len 2; hex 6666; asc ff;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2994 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 82430958 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000004e9cbe9; asc ;;
2: len 7; hex 1a000003fe0f8f; asc ;;
3: len 2; hex 6363; asc cc;;

*** WE ROLL BACK TRANSACTION (2)