Why my pessimistic Locking in JPA with Oracle is not working

Question

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:

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.

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:

[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.

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.

Answer 1

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.

Answer 2

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:

1. When the first job makes the select for update everything goes as expected but the version on the object changes.
2. 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.

This solution has various counterparts:

1. SynchronizedCronJobTask must have a version field and be under version control with @Version
2. You need to handle OptimisticLockException, and it should be catch outside the transactional service method in order to make rollback when de lock happens.
3. IMHO is a non elegant solution, much worse than simply a lock where the Cron Jobs wait for the previous Jobs to finish.