首页 > 代码库 > 查看sql语句加锁信息

查看sql语句加锁信息

2024-08-10 12:23:20   221人阅读

问题：

最近使用quartz集群，总是报deadlock问题，所以需要查看一下执行的sql导致的加锁冲突。

步骤：

1、在要测试的库中创建指定表innodb_lock_monitor

create table innodb_lock_monitor(x int) engine=innodb;
2、执行sql
BEGIN;SET tx_isolation=‘SERIALIZABLE‘;SET autocommit = 0;UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = ‘7897‘ WHERE SCHED_NAME = ‘clusterQuartzSchedular‘ AND JOB_NAME = ‘addRefundJob‘;

3、执行查询锁命令：

SHOW ENGINE INNODB STATUS;

4、得到执行结果

 技术分享

5、拷贝Status单元格的内容到notepad编辑器中查看详细信息：

show engine innodb statusTABLE LOCK table `pay_quartz`.`QRTZ_TRIGGERS` trx id 5E7BD8 lock mode IXRECORD LOCKS space id 0 page no 3846 n bits 168 index `IDX_QRTZ_T_J` of table `pay_quartz`.`QRTZ_TRIGGERS` trx id 5E7BD8 lock_mode XRecord lock, heap no 18 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 22; hex 636c757374657251756172747a5363686564756c6172; asc clusterQuartzSchedular;; 1: len 12; hex 616464526566756e644a6f62; asc addRefundJob;; 2: len 7; hex 44454641554c54; asc DEFAULT;; 3: len 16; hex 616464526566756e6454726967676572; asc addRefundTrigger;; 4: len 7; hex 44454641554c54; asc DEFAULT;;RECORD LOCKS space id 0 page no 2464 n bits 88 index `PRIMARY` of table `pay_quartz`.`QRTZ_TRIGGERS` trx id 5E7BD8 lock_mode X locks rec but not gapRecord lock, heap no 20 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 22; hex 636c757374657251756172747a5363686564756c6172; asc clusterQuartzSchedular;; 1: len 16; hex 616464526566756e6454726967676572; asc addRefundTrigger;; 2: len 7; hex 44454641554c54; asc DEFAULT;; 3: len 6; hex 0000005e7bd8; asc    ^{ ;; 4: len 7; hex 26000005b02d24; asc &    -$;; 5: len 12; hex 616464526566756e644a6f62; asc addRefundJob;; 6: len 7; hex 44454641554c54; asc DEFAULT;; 7: SQL NULL; 8: len 8; hex 8000014a93f7d3a0; asc    J    ;; 9: len 8; hex 8000014a93f33fc0; asc    J  ? ;; 10: len 4; hex 80000000; asc     ;; 11: len 4; hex 37383937; asc 7897;; 12: len 4; hex 43524f4e; asc CRON;; 13: len 8; hex 800001495c7369e0; asc    I\si ;; 14: len 8; hex 8000000000000000; asc         ;; 15: SQL NULL; 16: len 2; hex 8000; asc   ;; 17: len 30; hex aced0005737200156f72672e71756172747a2e4a6f62446174614d61709f; asc     sr  org.quartz.JobDataMap ; (total 603 bytes);RECORD LOCKS space id 0 page no 3846 n bits 168 index `IDX_QRTZ_T_J` of table `pay_quartz`.`QRTZ_TRIGGERS` trx id 5E7BD8 lock_mode X locks gap before recRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 22; hex 636c757374657251756172747a5363686564756c6172; asc clusterQuartzSchedular;; 1: len 12; hex 7061795374617475734a6f62; asc payStatusJob;; 2: len 7; hex 44454641554c54; asc DEFAULT;; 3: len 16; hex 70617953746174757354726967676572; asc payStatusTrigger;; 4: len 7; hex 44454641554c54; asc DEFAULT;;

查看sql语句加锁信息


联系
我们