locking

[schachi]

i created a sessionbean, which generates unique filenames. the sessionbean is called within different threads in a short time,
so i have to synchronize the processing

annotation: it would have been to "complicated" to write down the code of the filename-generation, so in this example i generate unique integers
instead of filenames (its easier to understand).

Variante A)

Code Sessionbean:

...
 public synchronized String getNextFilename() throws Exception {
 Collection col = em.createQuery("from FATable where id = :id")
 .setParameter("id", 1)
 .getResultList();
 faData = (FAData) col.iterator().next();
 faData.setCurrentNr(faData.getCurrentNr()++);
 return (Integer.toString(faData.getCurrentNr)); // ...
 }
...

DB at start:

 --------------
!id !currentNr!
 --------------
! 1 ! 15 !
 --------------

Processing:

thread A: call method sessionbean.getNextFileName
thread B: call method sessionbean.getNextFileName
thread A: entry method getNextFileName
thread A: em.createQuery("from FATable where id = :id").setParameter("id", 1).getResultList()
 --> container runs query: select id, currentNr from FATable where id = 1
thread A: faData.setCurrentNr(faData.getCurrentNr()+1) (15+1=16)
thread A: leave method getNextFileName

thread B: entry method getNextFileName
 thread B: em.createQuery("from FATable where id =
 :id").setParameter("id", 1).getResultList()
 --> container runs query: select id, currentNr from FATable where id = 1

thread A: container runs updatequery: update FATable currentNr = 16 where id = 1
thread A: commit
thread B: faData.setCurrentNr(faData.getCurrentNr()+1) (15+1=16)!
thread B: leave method getNextFileName
thread B: container runs updatequery: update FATable currentNr = 16 where id = 1
thread B: commit

DB at end:

 --------------
!id !currentNr!
 --------------
! 1 ! 16 !
 --------------

Problem:
The method was called twice, so the currentNr should be 17 instead of 16!
Reason for this "error": the isolation-level of the db-connection is commited read, and Thread B enters the method, before Thread A transmit the commit!


Variante B)
i tried to lock the row

Code Sessionbean:

...
 public synchronized String getNextFilename() throws Exception {
 Collection col = em.createQuery("from FAData where id = :id")
 .setParameter("id", 1)
 .getResultList();
 faData = (FAData) col.iterator().next();
 HibernateSession hs = (HibernateSession)em;
 Session session = hs.getHibernateSession();
 session.lock(faData, LockMode.UPGRADE);
 faData.setCurrentNr(faData.getCurrentNr()++);
 return (Integer.toString(faData.getCurrentNr)); // ...
 }
...

DB at start:

 --------------
!id !currentNr!
 --------------
! 1 ! 15 !
 --------------

Processing:
(Prosa)

thread A: call method sessionbean.getNextFileName
thread B: call method sessionbean.getNextFileName
thread A: entry method getNextFilename
thread A: em.createQuery(from FATable where id = 1).getResultList()
 --> container runs query: select id, currentNr from FATable where id = 1
thread A: session.lock(faData, LockMode.UPGRADE)
 --> container runs query: select id, currentNr from FATable where id = 1 for update
thread A: faData.setCurrentNr(faData.getCurrentNr()+1) (15+1=16)
thread A: exit method getNextFileName
thread B: entry method getNextFilename
thread B: em.createQuery(from FATable where id = 1)
 --> container runs query: select id, currentNr from FATable where id = 1
thread B: session.lock(faData, LockMode.UPGRADE)
 --> container runs query: select id, currentNr from FATable where id = 1 for update (db locks, until thread A released this row)
thread A: container runs updatequery: update FATable currentNr = 16 where id = 1
thread A: commit (release the lock)
thread B: faData.setCurrentNr(faData.getCurrentNr()+1) (15+1=16)!
thread B: exit method getNextFilename
thread B: container runs updatequery: update FATable currentNr = 16 where id = 1
thread B: commit

DB at end:

 --------------
!id !currentNr!
 --------------
! 1 ! 16 !
 --------------

Problem:
The method was called twice, so currentNr should be 17 instead of 16
Reason for this "error":
a) the isolation-level of the db-connection is commited read, and Thread B enters the method, before Thread A transmit the commit
b) DB locks thread B not until the .lock-statement (of Thread B) (i need the lock allready on the query-statement)

Variante C)
i made some experiments with the version-attribute, but this "only" (of course) prevents from overwriting data (exception will be thrown).

How can i solve this problem in a J2EE environment?
I need a safe solution, to synchronize the threads (thread B is locked until thread A transmit the commit).
Or is it better to delegate the lock to the DB? (then i would need a solution, where i can query the database und lock the resultset in one step
(simular to the Hibernate-query: createQuery(....).setLockMode(....).getResultlist)

how do you handle this problem in your application?

thank you for help
(i hope, you understand my english)
marc

[chrismalan]

Hi Marc,

I have the same scenario. It's transaction numbers generated with every online sale. They are date based with the number of the transaction for that day appended.

Try prepending @TransactionAttribute(TransactionAttributeType.REQUIRED) before your getNextFileName() method.

Then, instead of using a query to get hold of the last filename, do it like this:

FAData fdata = manager.find(FAData.class, new Integer(1));

fdata is then a POJO instance. You can get the value you want from it (it seems to hold only one row), increment the value and put it back and do:

manager.persist(fdata);

Give that a go and see if it works.

Good luck,

[michael.c.small]

You could also you a native query to lock the the record for each call. For example:

this.entityManager.createNativeQuery(
 "SELECT * FROM FATable WHERE id = :id FOR UPDATE", FA.class)
 .setParameter("id", id)
 .getResultList();

The above example uses the MySQL notation for row locking so you might need to alter that depending on your database vendor. Once the transaction associated with the call has completed, the database will release the lock.

[schachi]

thank you for reply.

this is working for me:

 public synchronized String getNextFilename() throws Exception {
 Collection col = em.createQuery("from FAData where id = :id")
 .setParameter("id", 1)
 .getResultList();
 faData = (FAData) col.iterator().next();
 HibernateSession hs = (HibernateSession)em;
 Session session = hs.getHibernateSession();
 session.refresh(faData, LockMode.UPGRADE);
 faData.setCurrentNr(faData.getCurrentNr()++);
 return (Integer.toString(faData.getCurrentNr)); // ...
 }

the database locks the refresh-statement of thread B, until thread A transmit the commit.
then the data will be resfreshed and locked for thread B (and so on).

- find/persist didn't worked for me
- the createnativequery has the disadvantage that it is in some cases database-specific
thank you anyway!

regards
marc