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

I'm trying to implement some kind of semaphores for cron jobs that runs in different JBoss nodes. I'm trying to use the database (Oracle 11g) as a locking mechanism using one table to syncronize the cron jobs in the different nodes. The table is very simple:

我正在尝试为在不同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);

So when a job starts it searches in the table for a entry of its cronjobtype, and checks if it is already running. If not it updates the entry setting running flag to true. This first select is made with JPA CriteriaApi using Hibernate and Pessimistic Lock.

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

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

All those opperations are made within one transaction.

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

When one process runs, the querys it makes are the following:

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

[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=?

There is no problem with this warning, you can see a first select and then a select for update, so Oracle should block other select operations on this row. But that's the point, the queries are not being blocked so two jobs can enter and make the select and update without problem. The lock is not working, we can see it if we run two cron jobs simultaneously:

此警告没有问题，您可以看到第一个选择然后选择更新，因此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=?

I've tried to make this select for update on a SQL tool (SQLWorkbenchJ) with two connections and the bloking is working fine within this tool. But if i make this select for update on the SQL tool and launch the cron jobs, they are not bloked and run without problems.

我试图在具有两个连接的SQL工具（SQLWorkbenchJ）上进行此选择以进行更新，并且该工具中的bloking工作正常。但是如果我在SQL工具上选择更新并启动cron作业，那么它们就不会运行并且运行没有问题。

I think the problem comes from JPA, Hibernate or the Oracle driver but i'm not sure. Any idea on where is the problem? Should i use anotehr strategy? Thanks in advance.

我认为问题来自JPA，Hibernate或Oracle驱动程序，但我不确定。问题在哪里？我应该使用anotehr策略吗？提前致谢。

3 个解决方案

#1

Finally i managed to make it work but with some modiffications. The idea is to use LockModeType.PESSIMISTIC_FORCE_INCREMENT instead of PESSIMISTIC_WRITE. Using this lock mode the Cron Jobs behave as follows:

最后，我设法让它工作，但有一些修改。我们的想法是使用LockModeType.PESSIMISTIC_FORCE_INCREMENT而不是PESSIMISTIC_WRITE。使用此锁定模式，Cron Jobs的行为如下：

When the first job makes the select for update everything goes as expected but the version on the object changes.
当第一个作业进行select for update时，一切都按预期进行，但对象上的版本发生了变化。
If another job tries to make the same select while the first is still on its transaction, JPA launches a OptimisticLockException so if you catch that exception you can be sure that it was thrown for a read lock.
如果另一个作业尝试在第一个仍然在其事务上时进行相同的选择，则JPA会启动一个OptimisticLockException，因此如果您捕获该异常，则可以确保它已被抛出以进行读锁定。

This solution has various counterparts:

该解决方案有各种对应方：

SynchronizedCronJobTask must have a version field and be under version control with @Version
SynchronizedCronJobTask必须具有版本字段，并且使用@Version进行版本控制
You need to handle OptimisticLockException, and it should be catch outside the transactional service method in order to make rollback when de lock happens.
您需要处理OptimisticLockException，它应该在事务服务方法之外捕获，以便在de lock发生时进行回滚。
IMHO is a non elegant solution, much worse than simply a lock where the Cron Jobs wait for the previous Jobs to finish.
恕我直言是一个非常优雅的解决方案，比Cron Jobs等待前任乔布斯完成的锁只更糟糕。

#2

Set locking mode to PESSIMISTIC_READ, because you need that second server knew about changes of first server before changing data.

将锁定模式设置为PESSIMISTIC_READ，因为在更改数据之前，您需要第二台服务器知道第一台服务器的更改。

#3

I can confirm Ricardos observation. I have several Lock-Modes tested with a H2-Database, and all worked as expected. Neither of the pessimistic Lock-Modes worked correctly in combination with an Oracle database. I did not try optimistic locking, but it's amazing that there's a lockmode that doesn't work with the top dog at all.

我可以确认Ricardos观察。我有几个使用H2数据库测试的锁模式，并且都按预期工作。两种悲观的锁模式都没有与Oracle数据库结合使用。我没有尝试乐观锁定，但令人惊讶的是，锁定模式根本不适用于*狗。

#1