8 Replies Latest reply on Aug 30, 2008 12:10 AM by Damian Harvey

    Solution for reliably retrieving incrementing value

    Damian Harvey Apprentice

      I have an entity Booking in my multi-tenant app that in addition to it's ID also requires a Booking Number that increments for each company. I have multiple companies that need this number to increment separately (and some use different increment steps).


      I was just selecting the max value of this number from the database for the particular company, however it was possible (and not that difficult) for two users from the same company to save the Booking at the same time and end up with the same Booking number.


      What is the best way to solve this?


      My solution so far involved creating a UniqueNumber table that holds increment counters for each company per entity. I then have an Application Scoped SFSB that reads from this table and increments the counter there and then before returning a value. This still doesn't work though. Both users still get the same number. The crazy thing is that I can see the Hibernate update calls and the second select should get the updated value but it doesn't.


      I've been staring at this for too long and my brain is numb. Can anyone suggest a better approach or point me to where I've made the wrong move?


      Here's my UniqueNumberGenerator:


      @Synchronized
      @Scope(ScopeType.APPLICATION)
      @Stateful
      @AutoCreate
      @Name("uniqueKeyGenerator")
      public class UniqueKeyGenerator implements IUniqueKeyGenerator, Serializable {
      
           @In
           private EntityManager entityManager;
           
           @Logger
           private static Log log;
           
           //This can be reset to whatever via the components.xml
           private Long defaultUniqueKey = 1000L;
           
           /** Fetches the next unique key from the Unique Key table. It then increments the value in the table.
            * This is to prevent duplicate unique keys (eg. Booking Number)
            * 
            * @param company
            * @param entityClass
            * @return
            */
           public long getUniqueKey(Company company, String entityClass) {
                     
                PersistenceContexts.instance().changeFlushMode(FlushModeType.MANUAL);
                
                UniqueKey uniqueKey = null;
                try {
                     uniqueKey = (UniqueKey)entityManager.createQuery("select u from UniqueKey u"+
                          " WHERE u.company = :company"+
                          " AND u.entityClass = :entityClass")
                          .setParameter("company", company)
                          .setParameter("entityClass", entityClass)
                          .getSingleResult();
                     
                     
                } catch(NoResultException e) {
                     
                } catch(NonUniqueResultException e) {
                     
                }
                
                if(uniqueKey == null) {
                     uniqueKey = new UniqueKey(company, entityClass, defaultUniqueKey, new Date());
                     entityManager.persist(uniqueKey);
                }
                
                long result = uniqueKey.getIdentifier();
                
                //Now increment the value
                uniqueKey.setIdentifier(result + 1L);
                uniqueKey.setUpdatedDate(new Date());
                
                entityManager.flush();
                
                PersistenceContexts.instance().changeFlushMode(FlushModeType.COMMIT);
                
                return result;
           }
           
           @Remove @Destroy
           public void destroy() {
           }
      }
      



      Many Thanks,


      Damian.


        • 1. Re: Solution for reliably retrieving incrementing value
          Guillaume Jeudy Master

          I don't know how much good

          @Synchronized

          do you. Are your concurrent calls coming from the same Seam application or different apps ? If they are different app instances then
          @Synchronized

          won't save you and you need this locking at the database level. Even if you are running only from 1 Seam instance I suggest you switch to database level locking otherwise you might get this problem later on if your app deployment becomes clustered or other apps use the same database counter.


          Why do you use FLUSH MODE MANUAL ? You use the default transaction management REQUIRED for EJB3 so you may be only joining a transaction which might be why you need to flush early since the caller transaction is not complete yet (committed) at the end of getUniqueKey() method. This is not the desired behavior you should probably set REQUIRED NEW to ensure that a new transaction is used for the execution of getUniqueKey() method.


          With database locking approach you will have to update/increment the value first (this will exclusively lock the database row for the duration of the transaction) then select the value. You will probably have to use a native query to trigger the update like so:


          UPDATE UNIQUE_KEY SET VALUE = VALUE + 1 WHERE COMPANY = ? AND ENTITYCLASS = ?



          You can execute a stored procedure if you want to handle both the INSERT and UPDATE case. Unfortunately there is no facilities for SPs in JPA and you will have to get the underlying JDBC connection from the Session to call the SP directly. Alternatively you can run a script to insert an empty row as part of your application deployment procedure so you dont have to handle INSERT/UPDATE case in app code.

          • 2. Re: Solution for reliably retrieving incrementing value
            Damian Harvey Apprentice

            Thanks Guillaume,


            I came to the same conclusion overnight about the database locking. I added JPA versioning (@Version) to my UniqueKey entity and also called


            entityManager.lock(uniqueKey, LockModeType.READ);
            


            after I read the uniqueKey from the database. This seems to have solved my issue.


            I think you're right that @Synchronized won't do me any good. I believe that this is just to prevent concurrent access by the same user?


            I was calling the flush() to ensure that I could see the Hibernate logging at a time when I wanted it rather than at the end of the method. Not much difference I suppose but at the time it seemed logical.


            Cheers,


            Damian.

            • 3. Re: Solution for reliably retrieving incrementing value
              Guillaume Jeudy Master

              I'm not sure how Synchronized annotation works but from reading the doc it seems to synchronize on multiple user access so would act like a regular synchronized java keyword on your method. You can always read the source code to make sure. However this wouldn't work if you had more than 1 seam app instance since instances are not aware of each other.


              Nice solution with JPA, haven't thought about that. Don't you need an additional version column in database to support it though? Also im not sure if acquiring a READ lock would prevent concurrent transactions of updating the value. If READ lock emulates SELECT FOR UPDATE behavior then I guess you are fine.

              • 4. Re: Solution for reliably retrieving incrementing value
                Damian Harvey Apprentice

                It's multi-user access from the same Seam App instance so you'd think it would work. I had to add a column for the version. Reading the Hibernate in Action book, it suggests that the JPA LockModeType.READ is equivalent to Hibernate's LockMode.UPGRADE - both of which result in a SELECT...FOR UPDATE.


                Cheers,


                Damian.

                • 5. Re: Solution for reliably retrieving incrementing value
                  Damian Harvey Apprentice

                  Of course the problem with this now is that I can't recover the Hibernate Session when concurrent access is detected and a StaleObjectStateException is thrown. I have to throw the user to the home page with an explanation handled via pages.xml. Not nice.


                  This is an old problem, and I haven't seen a nice solution to it yet : link1  and link2

                  • 6. Re: Solution for reliably retrieving incrementing value
                    Guillaume Jeudy Master

                    Yes this is how optimistic locking behaves.


                    If you predict that concurrent access happens often then I suggest you try my initial database driven solution with no versioning which behaves like a pessimistic locking strategy ensuring concurrent users have to wait until getUniqueKey() autonomous transaction completes. Triggering the SQL UPDATE first will create an exclusive database lock on that row until the transaction completes. It is preferable with this approach that you set database row locking scheme on your table to make sure you don't lock a datapage. Checkout your database vendor to find out default locking scheme and available options.


                    Since I expect this autonomous transaction to complete pretty rapidly I don't think you will create excessive contention by using my strategy.


                    You will ensure a unique value is returned on every call and no exceptions will be thrown. Let me know if you go that route.


                    Ok I guess the main drawback is that you are no longer using JPA interface but have to fallback to JDBC. If you can find a way to emulate


                    UPDATE UNIQUE_KEY SET VALUE = VALUE + 1

                    in EJB-QL/HQL then you can probably still use JPA.

                    • 7. Re: Solution for reliably retrieving incrementing value
                      Michael Courcy Newbie

                      I have to throw the user to the home page with an explanation handled via pages.xml. Not nice.


                      Just curious : why catching the StaleObjectStateException and proposing to finish the process with an incremented booking number is difficult ?


                      • 8. Re: Solution for reliably retrieving incrementing value
                        Damian Harvey Apprentice

                        You no longer have a Hibernate Session once the exception is thrown so I can't increment the number. Is that what you mean?