4 Replies Latest reply on Dec 10, 2015 6:11 AM by atchijov-vgw

    PESSIMISTIC_WRITE  seems to be not working with two applications running in two separate Wildfly instances

    atchijov-vgw

      Hi,

        I wonder if some one can help me to solve following problem?

       

        We have two separate Widlfly applications (Game server and Back Office server) running on two separate servers against common DB (we use 8.2 Final + mySQL). In some cases, both BO and Game server need to update record related to the same player. Also, sometimes two threads in Game server need to update record related to same player.  We are using locking (with PESSIMISTIC_WRITE) to ensure correct updates. It seems that this locking achieve desired effect in case of two threads from Game server trying to do update, but it does not help when Game server and BO trying to update the same record.

       

        Does it make sense? Does JPA perform locking in "software" (rather than using appropriate SQL calls)? I can not imagine that our setup is that unusual... so most likely the problem is due to us doing something wrong. What could it be?

       

        You help, any help, will be highly appreciated.

       

      Cheers,

        Andrei

        • 1. Re: PESSIMISTIC_WRITE  seems to be not working with two applications running in two separate Wildfly instances
          smarlow

          JPA (pessimistic) locking should always be done via the database server (never via in memory Java locks).  I would enable Hibernate SQL trace logging "org.hibernate.SQL" to show the executed SQL commands (while running a single user request that uses the Game Server and than BO).

           

          With pessimistic locking, you want to see the updated row first read with a "Select ... FOR UPDATE", which holds a database lock until the transaction completes.  Are you caching the updated value in Java memory?

          • 2. Re: PESSIMISTIC_WRITE  seems to be not working with two applications running in two separate Wildfly instances
            atchijov-vgw

            Hi Scott,
              Thanks a lot for quick reply

             

              As per your suggestion, I have enabled hibernate SQL logging (along with mysql logging) and I do see "select ... for update" happening at what looks like appropriate moments. And I do see some commits. Though I do not see commit anywhere near after "select ... for update"... is this indication that for some reason "select ... for update" happening outside of transaction?   [EDIT: nvm, I do see commit where it should be]

             

            I do not see any "start transaction" - is it normal?

             

            Cheers,

              Andrei

             

            P.S. I assume that you may want to know that we are using transaction-type="JTA"

            • 3. Re: PESSIMISTIC_WRITE  seems to be not working with two applications running in two separate Wildfly instances
              smarlow

              Hi Andrei,

               

              You could also enable TRACE logging for "org.jboss.as.jpa" and "com.arjuna", which should help you understand the application flow better.  The "com.arjuna" output is verbose but shows you when the JTA transaction starts and ends.  The "org.jboss.as.jpa" output, will show the "transaction scoped" entity manager being associated with the JTA transaction and each call to the entity manager. 

               

              When you read the TRACE output, you probably would not read every line but instead, find the relevant SQL UPDATE.  Then find the start of the JTA transaction that contains the SQL UPDATE, as well as the end.  Then just review the TRACE output within that transaction.  This gives you a better sense of what exactly happened within the transaction, so you can judge whether it is as you expect. 

               

              I think its normal to not see any "start transaction" at the SQL level, as Hibernate is not expected to execute any "start transaction" SQL commands.

               

              Scott

              • 4. Re: PESSIMISTIC_WRITE  seems to be not working with two applications running in two separate Wildfly instances
                atchijov-vgw

                Hi Scott,

                   Want to let you know that the problem was resolved and it had nothing to do with the way JPA does PESSIMISTIC_WRITE locking. The problem was due to the fact that the entity in question was not locked from the moment it was obtained by the query. It was locked just before we were ready to update it, as a result even though locking was working properly, the cross JVM updates were not. Game server and BO - both obtain entity at the same time, than one of them lock it and update it, but the problem was that another one still hold entity as it was before first update have happened, so once second app performed update, first update was lost. I assume this was working inside single JVM because the entity is shared between threads.

                 

                   Changing from  .lock() to .refresh() have resolved the issue for now.

                 

                Cheers,

                Andrei