View SQL statement Lock information

Problem:

Recently used quartz cluster, always reported deadlock problem, so need to look at the execution of SQL caused by the lock conflict.

Steps:

1. Create the specified table in the library you want to test innodb_lock_monitor

CREATE table Innodb_lock_monitor (x int) Engine=innodb;

2. Execute SQL

BEGIN;SETTx_isolation='SERIALIZABLE';SETAutocommit= 0;UPDATEQrtz_triggersSETTrigger_state= '7897' WHERESched_name= 'Clusterquartzschedular'  andJob_name= 'Addrefundjob';

3. Execute the Query lock command:

SHOW ENGINE INNODB STATUS;

4. Get the result of execution

5. Copy the contents of the status cell into the Notepad editor for more information:

Show engine InnoDB statusTABLELOCKTable' Pay_quartz '. ' Qrtz_triggers ' TRX ID 5e7bd8 lock mode ixrecord LOCKSSpaceId0Page no3846N Bits168 Index' Idx_qrtz_t_j ' of Table' Pay_quartz '. ' Qrtz_triggers ' Trx ID 5e7bd8 lock_mode xrecord Lock, Heap no -Physical Record:n_fields5; Compact format; Info bits0 0:Len  A; Hex 636c757374657251756172747a5363686564756c6172;ASCclusterquartzschedular;;1:Len  A; Hex 616464526566756e644a6f62;ASCaddrefundjob;;2:Len 7; Hex 44454641554c54;ASC DEFAULT;; 3:Len  -; Hex 616464526566756e6454726967676572;ASCAddrefundtrigger;;4:Len 7; Hex 44454641554c54;ASC DEFAULT;; RECORD LOCKSSpaceId0Page no2464N Bits the Index`PRIMARY` of Table' Pay_quartz '. ' Qrtz_triggers ' Trx ID 5e7bd8 lock_mode X locks Rec but notGaprecord Lock, Heap no -Physical Record:n_fields -; Compact format; Info bits0 0:Len  A; Hex 636c757374657251756172747a5363686564756c6172;ASCclusterquartzschedular;;1:Len  -; Hex 616464526566756e6454726967676572;ASCAddrefundtrigger;;2:Len 7; Hex 44454641554c54;ASC DEFAULT;; 3:Len 6; Hex 0000005e7bd8;ASC    ^{ ;; 4:Len 7; Hex 26000005b02d24;ASC &    -$;; 5:Len  A; Hex 616464526566756e644a6f62;ASCaddrefundjob;;6:Len 7; Hex 44454641554c54;ASC DEFAULT;; 7: SQLNULL; 8:Len 8; Hex 8000014a93f7d3a0;ASCJ;;9:Len 8; Hex 8000014a93f33fc0;ASCJ.;;Ten:Len 4; Hex80000000;ASC     ;;  One:Len 4; Hex37383937;ASC 7897;;  A:Len 4; Hex 43524f4e;ASCCRON;; -:Len 8; Hex 800001495c7369e0;ASCi\si;; -:Len 8; Hex8000000000000000;ASC         ;;  the: SQLNULL;  -:Len 2; Hex8000;ASC   ;;  -:Len  -; Hex aced0005737200156f72672e71756172747a2e4a6f62446174614d61709f;ASCSR Org.quartz.JobDataMap; (Total603bytes); RECORD LOCKSSpaceId0Page no3846N Bits168 Index' Idx_qrtz_t_j ' of Table' Pay_quartz '. ' Qrtz_triggers ' Trx ID 5e7bd8 lock_mode X locks Gap before Recrecord lock, Heap no2Physical Record:n_fields5; Compact format; Info bits0 0:Len  A; Hex 636c757374657251756172747a5363686564756c6172;ASCclusterquartzschedular;;1:Len  A; Hex 7061795374617475734a6f62;ASCpaystatusjob;;2:Len 7; Hex 44454641554c54;ASC DEFAULT;; 3:Len  -; Hex70617953746174757354726967676572;ASCPaystatustrigger;;4:Len 7; Hex 44454641554c54;ASC DEFAULT;;

View SQL statement Lock information