mysql - 使用可序列化事务后，Quartz 调度程序自身陷入死锁

Question

我有一个带有 MySQL InnoDB 表、集群 Quartz 调度程序和用于数据库层的 MyBatis 的系统。

Quartz 每 30 秒运行一次作业，检查数据库中的一些内容。一切都工作正常，直到最近，当我不得不为服务方法添加 org.mybatis.guice.transactional.Transactional(isolation=Isolation.SERIALIZABLE, executorType=ExecutorType.BATCH) 注释时，该方法实际上甚至不触及该服务方法的数据。显然失败的触发器的工作会触动。

所以，现在发生的情况是，在使用该事务化服务方法一段时间后，它可能在并发调用自身时失败(尽管日志中没有任何关于此的信息)，每 30 秒我都会收到一个Quartz 作业中出现死锁的日志异常，MySQL 告诉我死锁如下:

LATEST DETECTED DEADLOCK

160226 12:20:00 * (1) TRANSACTION: TRANSACTION 28465C8, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 229934, OS thread handle 0x7efcefcf7700, query id 76969784 server-node2 192.168.0.8 system-username Sorting result SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'CloudScheduler' AND TRIGGER_STATE = 'WAITING' AND NEXT_FIRE_TIME <= 1456482030017 AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= 1456481940018)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 737 n bits 128 index PRIMARY of table cloudsystem.QRTZ_TRIGGERS trx id 28465C8 lock mode S locks rec but not gap waiting Record lock, heap no 55 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 19; hex 5468657265436c6f75645363686564756c6572; asc CloudScheduler;; 1: len 29; hex 41646d696e5573657253657474696e7355706461746554726967676572; asc UserSettingsUpdateTrigger;; 2: len 7; hex 44454641554c54; asc DEFAULT;; 3: len 6; hex 0000028465c2; asc e ;; 4: len 7; hex 20000004ca2763; asc 'c;; 5: len 23; hex 41646d696e5573657253657474696e6773557064617465; asc UserSettingsUpdate;; 6: len 17; hex 41646d696e5573657253657474696e6773; asc UserSettings;; 7: SQL NULL; 8: len 8; hex 800001531d1811b0; asc S ;; 9: len 8; hex 800001531d179c80; asc S ;; 10: len 4; hex 80000005; asc ;; 11: len 8; hex 4143515549524544; asc ACQUIRED;; 12: len 4; hex 43524f4e; asc CRON;; 13: len 8; hex 80000153195c6d28; asc S \m(;; 14: len 8; hex 8000000000000000; asc ;; 15: SQL NULL; 16: len 2; hex 8000; asc ;; 17: len 0; hex ; asc ;;

* (2) TRANSACTION: TRANSACTION 28465C2, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 10 lock struct(s), heap size 1248, 15 row lock(s), undo log entries 1 MySQL thread id 230109, OS thread handle 0x7efcef9e7700, query id 76969780 server-node1 192.168.0.7 system-username Updating UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'ACQUIRED' WHERE SCHED_NAME = 'CloudScheduler' AND TRIGGER_NAME = 'UserSettingsUpdateTrigger' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'WAITING' * (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 737 n bits 120 index PRIMARY of table cloudsystem.QRTZ_TRIGGERS trx id 28465C2 lock_mode X locks rec but not gap Record lock, heap no 55 PHYSICAL RECORD: n_fields 18; compact format; info bits 0 0: len 19; hex 5468657265436c6f75645363686564756c6572; asc CloudScheduler;; 1: len 29; hex 41646d696e5573657253657474696e7355706461746554726967676572; asc UserSettingsUpdateTrigger;; 2: len 7; hex 44454641554c54; asc DEFAULT;; 3: len 6; hex 0000028465c2; asc e ;; 4: len 7; hex 20000004ca2763; asc 'c;; 5: len 23; hex 41646d696e5573657253657474696e6773557064617465; asc UserSettingsUpdate;; 6: len 17; hex 41646d696e5573657253657474696e6773; asc UserSettings;; 7: SQL NULL; 8: len 8; hex 800001531d1811b0; asc S ;; 9: len 8; hex 800001531d179c80; asc S ;; 10: len 4; hex 80000005; asc ;; 11: len 8; hex 4143515549524544; asc ACQUIRED;; 12: len 4; hex 43524f4e; asc CRON;; 13: len 8; hex 80000153195c6d28; asc S \m(;; 14: len 8; hex 8000000000000000; asc ;; 15: SQL NULL; 16: len 2; hex 8000; asc ;; 17: len 0; hex ; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 759 n bits 120 index IDX_QRTZ_T_NFT_ST of table cloudsystem.QRTZ_TRIGGERS trx id 28465C2 lock_mode X locks rec but not gap waiting Record lock, heap no 51 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 19; hex 5468657265436c6f75645363686564756c6572; asc CloudScheduler;; 1: len 7; hex 57414954494e47; asc WAITING;; 2: len 8; hex 800001531d1811b0; asc S ;; 3: len 29; hex 41646d696e5573657253657474696e7355706461746554726967676572; asc UserSettingsUpdateTrigger;; 4: len 7; hex 44454641554c54; asc DEFAULT;;

*** WE ROLL BACK TRANSACTION (1)

所以这让我想知道。 “其他地方”的可序列化事务的实例可能失败，但这不应导致这种现象，这种现象每次都会重复发生。也不应该有多个调度程序作业同时运行。

另一个问题是 @Transactional 注释似乎不会回滚事务——在这些死锁开始出现后，数据库中的数据已损坏。有趣的是，在应用程序中事务不可序列化的其他地方，异常回滚确实可以正常工作。

MyBatis 文档对于这些问题并不是很详细，但是我还需要做些什么来使我的事务安全地可序列化

@Transactional(isolation=Isolation.SERIALIZABLE, executorType=ExecutorType.BATCH)
public void doCoolStuff(...) {

}

Answer 1

这不是一个明确的答案，但同时包含太多信息，无法发表评论。

我想分享一些我的想法。

1. 您对下面的确认百分百确定吗？您可能已经知道，如果您的服务和 Quartz 作业在任何时候使用相同的表，我都会预料到会出现此类问题。

"annotation for a service method that actually does not even touch the data that the apparently failing trigger's job would touch."

在我看来，您的 Quartz 作业和事务正在发生冲突。因此，您可能需要实现某种同步策略。例如，您可以添加事务管理器，或者可以在代码中添加某种同步块(synchronized block)来避免此问题。

干杯，