show engine innodb status\G查看mysql死锁记录

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 230465130, 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 27736562 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('165897816111628002764043','158633914081628001644095','-1'                                                      ,'165897816071028002763869-grmhgd','2022-07-28 11:16:01.113','165897816073528002763932-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 230465130 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 3136353839373831363037333932383030323933323730382d677a6c6162; asc 165897816073928002932708-gzlab; (total 31 bytes);
 1: len 24; hex 313635383937383136303735343238303032393332373135; asc 165897816075428002932715;;

*** (2) TRANSACTION:
TRANSACTION 230465132, 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 27736593 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('165897816112728002932862','161882270442028002548195','161                                                      698315238928001126133','165897626035528002522733-gzlab0','2022-07-28 11:16:01.125','165897816098428002932822-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 230465132 lock_mode X
Record lock, heap no 175 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 3136353839373632363034343732383030323532323835362d677a6c6162; asc 165897626044728002522856-gzlab; (total 31 bytes);
 1: len 24; hex 313635383937363236303439363238303032353232383634; asc 165897626049628002522864;;

Record lock, heap no 248 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 3136353839373831363037333932383030323933323730382d677a6c6162; asc 165897816073928002932708-gzlab; (total 31 bytes);
 1: len 24; hex 313635383937383136303735343238303032393332373135; asc 165897816075428002932715;;

*** (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 230465132 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 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 231269278
Purge done for trx's n:o < 231269278 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 118575, OS thread handle 0x7f6c82bd7700, query id 34064879 localhost root init
show engine innodb status
---TRANSACTION 231269274, not started
MySQL thread id 118573, OS thread handle 0x7f6c83b76700, query id 34064868 172.1.1.34 arp cleaning up
---TRANSACTION 231269240, not started
MySQL thread id 118574, OS thread handle 0x7f6c7b420700, query id 34064824 172.1.1.34 arp cleaning up
---TRANSACTION 231269265, not started
MySQL thread id 118570, OS thread handle 0x7f6c7aa8d700, query id 34064840 172.1.1.33 arp cleaning up
---TRANSACTION 231269244, not started
MySQL thread id 118568, OS thread handle 0x7f6c7b4a1700, query id 34064826 172.1.1.32 arp cleaning up
---TRANSACTION 231269233, not started
MySQL thread id 118567, OS thread handle 0x7f6c82346700, query id 34064839 172.1.1.33 arp cleaning up
---TRANSACTION 231269058, not started
MySQL thread id 118565, OS thread handle 0x7f6c829d3700, query id 34064822 172.1.1.34 arp cleaning up
---TRANSACTION 231269022, not started
MySQL thread id 118562, OS thread handle 0x7f6c83162700, query id 34064837 172.1.1.35 arp cleaning up
---TRANSACTION 231269059, not started
MySQL thread id 118561, OS thread handle 0x7f6c82fdf700, query id 34064823 172.1.1.34 arp cleaning up
---TRANSACTION 231269276, not started
MySQL thread id 118557, OS thread handle 0x7f6c7a889700, query id 34064878 172.1.1.35 arp cleaning up
---TRANSACTION 231269260, not started
MySQL thread id 118554, OS thread handle 0x7f6c98289700, query id 34064827 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
1088389 OS file reads, 10018030 OS file writes, 6093828 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.45 writes/s, 2.41 fsyncs/s