首页 > 代码库 > 查看sql语句加锁信息
查看sql语句加锁信息
问题:
最近使用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语句加锁信息
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。