为什么我对Oracle的JPA中的悲观锁定无法正常工作

Question

我正在尝试为在不同JBoss节点中运行的cron作业实现某种信号量。我正在尝试使用数据库（Oracle
11g）作为一种锁定机制，该锁定机制使用一张表同步不同节点中的cron作业。该表非常简单：

CREATE TABLE SYNCHRONIZED_CRON_JOB_TASK
(
   ID            NUMBER(10)           NOT NULL,
   CRONJOBTYPE   VARCHAR2(255 Byte),
   CREATIONDATE  TIMESTAMP(6)         NOT NULL,
   RUNNING       NUMBER(1)
);

ALTER TABLE SYNCHRONIZED_CRON_JOB_TASK
   ADD CONSTRAINT PK_SYNCHRONIZED_CRON_JOB_TASK
   PRIMARY KEY (ID);

因此，当作业启动时，它将在表中搜索其cronjobtype的条目，并检查其是否已在运行。如果不是，它将条目设置运行标志更新为true。第一次选择是使用Hibernate和Pessimistic
Lock通过JPA CriteriaApi进行的。

query.setLockMode(javax.persistence.LockModeType.PESSIMISTIC_WRITE);

所有这些操作都在一次交易中完成。

当一个进程运行时，它进行的查询如下：

[Server:server-two] 10:38:00,049 INFO  [stdout] (scheduler-2) 2015-04-30 10:38:00,048 WARN  (Loader.java:264) - HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
[Server:server-two] 10:38:00,049 INFO  [stdout] (scheduler-2) Hibernate: select * from ( select distinct synchroniz0_.id as id1_127_, synchroniz0_.creationDate as creation2_127_, synchroniz0_.running as running3_127_, synchroniz0_.CRONJOBTYPE as CRONJOBT4_127_ from SYNCHRONIZED_CRON_JOB_TASK synchroniz0_ where synchroniz0_.CRONJOBTYPE=? ) where rownum <= ?
[Server:server-two] 10:38:00,053 INFO  [stdout] (scheduler-2) Hibernate: select id from SYNCHRONIZED_CRON_JOB_TASK where id =? for update
[Server:server-two] 10:38:00,056 INFO  [stdout] (scheduler-2) Hibernate: update SYNCHRONIZED_CRON_JOB_TASK set creationDate=?, running=?, CRONJOBTYPE=? where id=?

此警告没有问题，您可以看到第一个选择，然后看到一个更新选择，因此Oracle应该在此行上阻止其他选择操作。但这就是重点，查询没有被阻止，因此可以输入两个作业，并进行选择和更新而不会出现问题。锁不起作用，如果同时运行两个cron作业，我们可以看到它：

[Server:server-one] 10:38:00,008 INFO  [stdout] (scheduler-3) 2015-04-30 10:38:00,008 WARN  (Loader.java:264) - HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
[Server:server-two] 10:38:00,008 INFO  [stdout] (scheduler-2) 2015-04-30 10:38:00,008 WARN  (Loader.java:264) - HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
[Server:server-two] 10:38:00,009 INFO  [stdout] (scheduler-2) Hibernate: select * from ( select distinct synchroniz0_.id as id1_127_, synchroniz0_.creationDate as creation2_127_, synchroniz0_.running as running3_127_, synchroniz0_.CRONJOBTYPE as CRONJOBT4_127_ from SYNCHRONIZED_CRON_JOB_TASK synchroniz0_ where synchroniz0_.CRONJOBTYPE=? ) where rownum <= ?
[Server:server-one] 10:38:00,009 INFO  [stdout] (scheduler-3) Hibernate: select * from ( select distinct synchroniz0_.id as id1_127_, synchroniz0_.creationDate as creation2_127_, synchroniz0_.running as running3_127_, synchroniz0_.CRONJOBTYPE as CRONJOBT4_127_ from SYNCHRONIZED_CRON_JOB_TASK synchroniz0_ where synchroniz0_.CRONJOBTYPE=? ) where rownum <= ?
[Server:server-two] 10:38:00,013 INFO  [stdout] (scheduler-2) Hibernate: select id from SYNCHRONIZED_CRON_JOB_TASK where id =? for update
[Server:server-one] 10:38:00,014 INFO  [stdout] (scheduler-3) Hibernate: select id from SYNCHRONIZED_CRON_JOB_TASK where id =? for update
[Server:server-two] 10:38:00,016 INFO  [stdout] (scheduler-2) 2015-04-30 10:38:00,015 DEBUG (SynchronizedCronJobService.java:65) - Task read SynchronizedCronJobTask [id=185, type=AlertMailTaskExecutor, creationDate=2015-04-25 07:11:33.0, running=false]
[Server:server-two] 10:38:00,018 INFO  [stdout] (scheduler-2) Hibernate: update SYNCHRONIZED_CRON_JOB_TASK set creationDate=?, running=?, CRONJOBTYPE=? where id=?
[Server:server-one] 10:38:00,022 INFO  [stdout] (scheduler-3) 2015-04-30 10:38:00,022 DEBUG (SynchronizedCronJobService.java:65) - Task read SynchronizedCronJobTask [id=185, type=AlertMailTaskExecutor, creationDate=2015-04-25 07:11:33.0, running=false]
[Server:server-one] 10:38:00,024 INFO  [stdout] (scheduler-3) Hibernate: update SYNCHRONIZED_CRON_JOB_TASK set creationDate=?, running=?, CRONJOBTYPE=? where id=?

我试图在具有两个连接的SQL工具（SQLWorkbenchJ）上选择进行更新，并且bloking在该工具中运行良好。但是，如果我选择在SQL工具上进行更新并启动cron作业，则它们不会被阻塞并且不会出现问题。

我认为问题来自JPA，Hibernate或Oracle驱动程序，但我不确定。有什么问题的想法吗？我应该使用anotehr策略吗？提前致谢。

Answer 1

最终，我设法使它起作用，但是有些麻烦。这个想法是使用LockModeType.PESSIMISTIC_FORCE_INCREMENT而不是PESSIMISTIC_WRITE。使用此锁定模式，Cron作业的行为如下：

1. 当第一个作业进行更新选择时，一切都会按预期进行，但是对象上的版本会更改。
2. 如果在第一个作业仍在事务中时另一个作业尝试进行相同的选择，则JPA会启动OptimisticLockException，因此，如果捕获到该异常，则可以确保它被抛出读锁定。

该解决方案具有多种对应方式：

1. SynchronizedCronJobTask必须具有版本字段，并且受@Version进行版本控制
2. 您需要处理OptimisticLockException，并且应该在事务服务方法之外捕获它，以便在解除锁定时进行回滚。
3. IMHO is a non elegant solution, much worse than simply a lock where the Cron Jobs wait for the previous Jobs to finish.