show engine innodb status\G及pt-deadlock-logger都可以查询到deadlock记录

但从使用上我觉得show engine innodb status\G显示的结果更清晰。

讯享网

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2022-07-28 11:19:31 7f6c82cd9700 * (1) TRANSACTION: TRANSACTION , ACTIVE 1 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 258 lock struct(s), heap size 30248, 26585 row lock(s), undo log entries 48 MySQL thread id 99343, OS thread handle 0x7f6c9848d700, query id  172.1.1.34 arp update INSERT INTO ACT_AUTH_USER(ID,USER_ID,CORP_ORG_ID,PROC_INST_ID,CREATE_TIME,TASK_ID,UPDATE_TIME,ETL_FLAG,SECRET_LEVEL)values('43','95','-1' ,'69-grmhgd','2022-07-28 11:16:01.113','32-grmhgd','2022-07-28 11:16:01.116','O','') * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4332 page no 3368 n bits 320 index `IDX_ACT_AUTH_U_TASK_ID` of table `arp`.`ACT_AUTH_USER` trx id  lock_mode X locks gap before rec insert intention w aiting Record lock, heap no 248 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 00382d677a6c6162; asc 08-gzlab; (total 31 bytes); 1: len 24; hex 0135; asc 15;; * (2) TRANSACTION: TRANSACTION , ACTIVE 1 sec inserting mysql tables in use 1, locked 1 26 lock struct(s), heap size 2936, 102 row lock(s), undo log entries 145 MySQL thread id 99315, OS thread handle 0x7f6c82cd9700, query id  172.1.1.33 arp update INSERT INTO ACT_AUTH_USER(ID,USER_ID,CORP_ORG_ID,PROC_INST_ID,CREATE_TIME,TASK_ID,UPDATE_TIME,ETL_FLAG,SECRET_LEVEL)values('62','95','161 ','33-gzlab0','2022-07-28 11:16:01.125','22-gzlab0','2022-07-28 11:16:01.128','O','') * (2) HOLDS THE LOCK(S): RECORD LOCKS space id 4332 page no 3368 n bits 320 index `IDX_ACT_AUTH_U_TASK_ID` of table `arp`.`ACT_AUTH_USER` trx id  lock_mode X Record lock, heap no 175 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 05362d677a6c6162; asc 56-gzlab; (total 31 bytes); 1: len 24; hex 0634; asc 64;; Record lock, heap no 248 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 30; hex 00382d677a6c6162; asc 08-gzlab; (total 31 bytes); 1: len 24; hex 0135; asc 15;; * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4332 page no 3368 n bits 320 index `IDX_ACT_AUTH_U_TASK_ID` of table `arp`.`ACT_AUTH_USER` trx id  lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex d756d; asc supremum;; * WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter  Purge done for trx's n:o <  undo n:o < 0 state: running but idle History list length 1397 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id , OS thread handle 0x7f6c82bd7700, query id  localhost root init show engine innodb status ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c83b76700, query id  172.1.1.34 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c7b, query id  172.1.1.34 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c7aa8d700, query id  172.1.1.33 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c7b4a1700, query id  172.1.1.32 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c, query id  172.1.1.33 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c829d3700, query id  172.1.1.34 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c, query id  172.1.1.35 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c82fdf700, query id  172.1.1.34 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c7a, query id  172.1.1.35 arp cleaning up ---TRANSACTION , not started MySQL thread id , OS thread handle 0x7f6c, query id  172.1.1.32 arp cleaning up -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0  OS file reads,  OS file writes,  OS fsyncs 0.00 reads/s, 0 avg bytes/read, 2.45 writes/s, 2.41 fsyncs/s