3 Replies Latest reply on Sep 28, 2005 11:49 AM by Schacher

    locking

    Schacher Newbie

      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

        • 1. Re: locking
          Chris Malan Novice

          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,

          • 2. Re: locking
            Michael Small Newbie

            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.

            • 3. Re: locking
              Schacher Newbie

              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