QuartZ使用会表锁,多个QuartZ项目链接一个DB导致死锁,问题很恶心

QuartZ使用的DB,当多个项目同时使用一个DB,很容易导致死锁

具体是'QRTZ_TRIGGERS'表,当使用'show OPEN TABLES where In_use > 0;' 语句查看被锁的表时,可以观察到表被死锁,控制台抛出异常是行锁导致超时。

控制台抛出异常信息如下:
error.jpg


通过以下SQL可以定位到导致死锁的位置

SHOW OPEN TABLES WHERE In_use > 0;

UNLOCK TABLES;

SHOW PROCESSLIST;

SHOW STATUS LIKE '%lock%';

SELECT * FROM QRTZ_TRIGGERS WHERE TRIGGER_STATE='ACQUIRED'

通过查询 SHOW PROCESSLIST; 定位到是哪个链接影响的数据库操作,等待其完成即可。

这样验证了猜想,是多个QuartZ同时执行一个DB导致的死锁问题


SELECT * FROM QRTZ_TRIGGERS WHERE TRIGGER_STATE='ACQUIRED' 可以发现问题导致之后,QRTZ_TRIGGERS 表不会自动更新,此时需要删除QuartZ的JOB信息, 以下是所能想到的删除记录,测试了下删除后自动生成QuartZ的表数据不会与之前的影响,类似于完整删除掉这个JOB。

START TRANSACTION; 

DELETE FROM QRTZ_CRON_TRIGGERS WHERE TRIGGER_NAME LIKE 'test_%'

DELETE FROM QRTZ_JOB_HISTORY WHERE JOB_NAME LIKE 'test_%'

DELETE FROM QRTZ_SIMPLE_TRIGGERS WHERE TRIGGER_NAME IN (SELECT TRIGGER_NAME FROM QRTZ_TRIGGERS WHERE JOB_NAME LIKE 'test_%')

DELETE FROM QRTZ_TRIGGERS WHERE JOB_NAME LIKE 'test_%'

DELETE FROM QRTZ_JOB_DETAILS WHERE JOB_NAME LIKE 'test_%'

DELETE FROM QRTZ_TRIGGER_HISTORY WHERE TRIGGER_NAME LIKE 'test_%'

COMMIT;