12

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.

Ricardo Vila
  • 1,626
  • 1
  • 18
  • 34
  • 1
    And do you implement "active" waiting inside JBOSS? In the moment when you return a connection to the connection pool JBOSS will issue ROLLBACK on it and all the locks are lost. Also your cron-jobs have to use SELECT FOR UPDATE, pure reading is never blocked in Oracle. – ibre5041 Apr 30 '15 at 09:48
  • I'm not sure about what you mean with active waiting, i don't use any JBOSS special feature. I wanted that the waiting feature was made on database select operation. – Ricardo Vila Apr 30 '15 at 10:36
  • I think it is not about pessimistic locking, but about transactions in general. Even pure single UPDATE would work, as long as transaction is open and connection is kept in the thread. In JPA when the code leaves transactional boundary rollback is issued and all locks are lost. – ibre5041 Apr 30 '15 at 11:02
  • Ibre, the problem i'm facing is that a cron job starts a transaction, reads the proper row with 'for update' if the data says other cron job is running it does nothing but close te transaction else it updates the row setting running to true and closing the transaction. I think this aproach is fine but maybe it is not the proper way to achieve what im looking for. – Ricardo Vila Apr 30 '15 at 11:19

3 Answers3

4

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.
Ricardo Vila
  • 1,626
  • 1
  • 18
  • 34
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.

Jörg Vollmer
  • 198
  • 1
  • 7
  • I have posted my problem in more detail here: https://stackoverflow.com/questions/48885249/jpa-pessimistic-lock-not-working#51619055 – Jörg Vollmer Aug 01 '18 at 07:07
0

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

Alexander Fedyukov
  • 964
  • 8
  • 18
  • 1
    I've tried PESSIMISTIC_READ but it doesn't work. Also, for this situation the right lock mode is PESSIMISTIC_WRITE beacuse it does block reads and updates. With PESSIMISTIC_READ only updates are blocked. – Ricardo Vila Apr 30 '15 at 10:34
  • You have a mistake. All vice versa: PESSIMISTIC_READ locks read and write, but PESSIMISTIC_WRITE - only write. – Alexander Fedyukov Apr 30 '15 at 10:42
  • Hi Alexander. Are you sure? In this thread http://stackoverflow.com/questions/1657124/whats-the-difference-between-pessimistic-read-and-pessimistic-write Joseph's answer says: - PESSIMISTIC_READ. ... This lock mode does not block other transactions from reading the data. However i've tried to use PESSIMISTIC_READ but with no luck. Maybe i'm missing something. – Ricardo Vila Apr 30 '15 at 11:00
  • Not longer sure. Sorry. Mix up again. – Alexander Fedyukov Apr 30 '15 at 11:03