如何阅读死锁信息
死锁信息： ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-11-10 10:48:46 7f84bdac8700 *** (1) TRANSACTION: TRANSACTION 82430953, ACTIVE 133 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 3 MySQL thread id 30, OS thread handle 0x7f8483fff700, query id 602 localhost dball updating delete from t1 where id=4 *** (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 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;; *** (2) TRANSACTION: 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) ------------ TRANSACTIONS ------------ Trx id counter 82430968 Purge done for trx's n:o < 82430968 undo n:o < 0 state: running but idle History list length 406 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 82430958, not started MySQL thread id 31, OS thread handle 0x7f84bdac8700, query id 603 localhost dball cleaning up ---TRANSACTION 82430932, not started MySQL thread id 32, OS thread handle 0x7f84bdb09700, query id 604 localhost dball init show engine innodb status ---TRANSACTION 82430953, ACTIVE 140 sec 3 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 4 MySQL thread id 30, OS thread handle 0x7f8483fff700, query id 602 localhost dball cleaning up TABLE LOCK table `test`.`t1` trx id 82430953 lock mode IX 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 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000004e9cbe9; asc ;; 2: len 7; hex 1a000003fe0f49; asc I;; 3: len 2; hex 6161; asc aa;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000004e9cbe9; asc ;; 2: len 7; hex 1a000003fe0f6c; asc l;; 3: len 2; hex 6262; asc bb;; 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;; 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 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 000004e9cbe9; asc ;; 2: len 7; hex 1a000003fe0fb2; asc ;; 3: len 2; hex 6464; asc dd;; -------- FILE I/O -------- 锁等待时的信息： ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-11-10 10:43:51 7f84bdac8700 *** (1) TRANSACTION: TRANSACTION 82430941, ACTIVE 52 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 3 MySQL thread id 30, OS thread handle 0x7f8483fff700, query id 582 localhost dball updating delete from t1 where id=4 *** (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 82430941 lock_mode X locks rec but not gap waiting 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 000004e9cbde; asc ;; 2: len 7; hex 150000039d2c91; asc , ;; 3: len 2; hex 6464; asc dd;; *** (2) TRANSACTION: TRANSACTION 82430942, ACTIVE 40 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 583 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 82430942 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 000004e9cbde; asc ;; 2: len 7; hex 150000039d2c91; 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 000004e9cbde; asc ;; 2: len 7; hex 150000039d2c6e; asc ,n;; 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 000004e9cbde; asc ;; 2: len 7; hex 150000039d2c4b; asc ,K;; 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 82430942 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 000004e9cbdd; asc ;; 2: len 7; hex 140000013c0714; asc < ;; 3: len 2; hex 6363; asc cc;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 82430967 Purge done for trx's n:o < 82430967 undo n:o < 0 state: running but idle History list length 405 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 82430932, not started MySQL thread id 32, OS thread handle 0x7f84bdb09700, query id 601 localhost dball init show engine innodb status ---TRANSACTION 82430958, ACTIVE 33 sec 2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 3 MySQL thread id 31, OS thread handle 0x7f84bdac8700, query id 599 localhost dball cleaning up TABLE LOCK table `test`.`t1` trx id 82430958 lock mode IX 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;; ---TRANSACTION 82430953, ACTIVE 48 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 3 MySQL thread id 30, OS thread handle 0x7f8483fff700, query id 600 localhost dball updating delete from t1 where id=4 ------- TRX HAS BEEN WAITING 11 SEC 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 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;; ------------------ TABLE LOCK table `test`.`t1` trx id 82430953 lock mode IX 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 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000004e9cbe9; asc ;; 2: len 7; hex 1a000003fe0f49; asc I;; 3: len 2; hex 6161; asc aa;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000004e9cbe9; asc ;; 2: len 7; hex 1a000003fe0f6c; asc l;; 3: len 2; hex 6262; asc bb;; 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;; 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 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;; -------- FILE I/O -------- 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;
解释说明：
------------------------
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)
