7 Replies Latest reply on Nov 11, 2003 8:38 PM by Aaron

    Optimistic Locking in 3.2.2

    senthilcool Newbie

      My email to Alexey Loubyansky
      ---------------------------------------

      The latest version of the JDBCOptimisticLock (1.2.2.11 -- which is included in JBoss 3.2.2) extends NoLock and hence is not doing optimistic locking.
      The code (cmp entity beans) I have works fine with 3.2.1 performimg an optimistic lock based on the timestamp column strategy. But it does not do the same
      with 3.2.2.
      Pls let me know if the implementation has changed in 3.2.2 and if I need to do anything differently for it to work.

      Response from Alex
      -------------------------

      as you have seen the implementation really changed ;) But it should work and perform much better. Why don't we discuss it on jboss-user list?

        • 1. Re: Optimistic Locking in 3.2.2
          Alexey Loubyansky Master

          Could you, please, post DDs and maybe relelvant code snippets if there are. Is the timestamp column represented with a declared CMP field in ejb-jar.xml?

          PS: sorry, for the delay.

          • 2. Re: Optimistic Locking in 3.2.2
            senthilcool Newbie

            I have attached the DD's and following is the code snippet that updates an entity (from a stateless session bean).

            public void updatePartyName(String partyId, String name)
            throws FinderException {
            IPartyLocalHome partyHome = getPartyLocalHome();
            IPartyLocal party = partyHome.findByPrimaryKey(partyId);
            party.setName(name);
            party.setStatus("ACTIVE");
            }

            The timestamp column does not have a declared CMP field in the ejb-jar.xml.

            • 3. Re: Optimistic Locking in 3.2.2
              Aaron Newbie

              We've been noticing similar problems with 3.2.2, more specifically between 3.2.2 RC3 and 3.2.2. What we have noticed is when updating a ejb, where it has an ejb relationship with another entity.

              We get an exception (specifically the expected row update exception) when updating a normal ejb field _and_ the ejb relationship field in the same transaction.

              --Aaron

              • 4. Re: Optimistic Locking in 3.2.2
                Aaron Newbie

                Further to this we have found that the problem lies in the way JBoss is creating the JDBC prepared statements. When trying to modify multiple fields including an ejb relationship and an ordinary field. JBoss creates 2 new JDBC prepared statements which in turn fire off 2 new update requests.

                On the first update a normal sql update is called incrementing the ol_version field for this record. Then the second update tries to update the ejb relationship field. It tries to find the record to update using the initial ol_version value and fails.


                see below

                12:53:28,269 DEBUG [Advisor] Executing SQL: SELECT name, emailAddress, phoneNumber, ol_version, branch FROM Advisor WHERE (id=?)
                12:53:28,269 DEBUG [Advisor] Executing SQL: UPDATE Advisor SET name=?, ol_version=? WHERE id=? AND ol_version=?
                12:53:52,519 DEBUG [Advisor] Rows affected = 1
                12:53:52,519 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                12:53:52,534 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                12:53:52,534 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                12:54:02,534 DEBUG [Advisor] Executing SQL: UPDATE Advisor SET ol_version=?, branch=? WHERE id=? AND ol_version=?
                12:55:11,050 ERROR [LogInterceptor] TransactionRolledbackLocalException in method: public abstract chelmer.company.ejb.Advisor chelmer.company.ejb.AdvisorHome.findByPrimaryKey(java.lang.Long) throws javax.ejb.FinderException, causedBy:
                javax.ejb.EJBException: Update failed. Expected one affected row: rowsAffected=0id=2

                • 5. Re: Optimistic Locking in 3.2.2
                  Alexey Loubyansky Master

                  Ok, that could be the case with 3.2.2. Could you try the same with 3.2.3RC1? PLEASE, let me know. Thank you.

                  • 6. Re: Optimistic Locking in 3.2.2
                    senthilcool Newbie

                    Optimistic locking with the timestamp column works in 3.2.3RC1. It also works better, does not fire an extra query to get the timestamp field used in the locking as was being done in 3.2.1.

                    I also noticed that the findByPrimaryKey is selecting the primary key field twice!!
                    SELECT PARTY.party_uuid, PARTY.party_uuid, PARTY.status_code, PARTY.name, PARTY.last_modified_date
                    FROM PARTY WHERE party_uuid=?
                    (The DD's are attached in the 2nd post)

                    Alex, can u pls point me to the new implementation in the source code.

                    Thanks for the help.

                    cheers,
                    senthil

                    • 7. Re: Optimistic Locking in 3.2.2
                      Aaron Newbie

                      Updating to 3.2.3RC1 also corrected the error we were having in our regression tests. However during our tests and trying to work out what was going wrong we've found that the SQL statements produced by JBoss differ depending on the ordering of finds and set's to EJB's in a transaction.

                      Ill outline in code below what i mean:

                      BEFORE:
                      =======
                      // Update database record for advisor
                      company.ejb.Advisor advisor = advisorHome.findByPrimaryKey(delta.getId());
                      advisor.setName(delta.getName());
                      advisor.setEmailAddress(delta.getEmailAddress());
                      advisor.setPhoneNumber(delta.getPhoneNumber());

                      if (delta.getBranch()!=null)
                      {
                      Long id = null;
                      try
                      {
                      id = delta.getBranch().getId();
                      company.ejb.Branch relatedEjb = branchHome.findByPrimaryKey(id);
                      advisor.setBranch(relatedEjb);
                      }
                      catch(FinderException e)
                      {
                      throw e;
                      }
                      }
                      else
                      {
                      advisor.setBranch(null); // clear relationship
                      }

                      14:54:17,642 DEBUG [Advisor#findByPrimaryKey] Executing SQL: SELECT id FROM Advisor WHERE id=?
                      14:54:17,642 DEBUG [Advisor] Executing SQL: SELECT name, emailAddress, phoneNumber, ol_version, branch FROM Advisor WHERE (id=?)
                      14:54:17,642 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:54:17,642 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:54:17,735 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:54:17,735 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:54:17,751 DEBUG [Advisor#findAdvisorByName] Executing SQL: SELECT DISTINCT t0_a.id FROM Advisor t0_a WHERE ((t0_a.name = ?))
                      14:54:17,751 DEBUG [AdvisorFacadeBean] Failed to find advisor ByName name=[AdvNEWER77]
                      14:54:17,751 DEBUG [Advisor#findByPrimaryKey] Executing SQL: SELECT id FROM Advisor WHERE id=?
                      14:54:17,751 DEBUG [Advisor] Executing SQL: SELECT name, emailAddress, phoneNumber, ol_version, branch FROM Advisor WHERE (id=?)
                      14:54:17,751 DEBUG [Advisor] Executing SQL: UPDATE Advisor SET name=?, emailAddress=?, ol_version=? WHERE id=? AND ol_version=?
                      14:54:17,751 DEBUG [Advisor] Rows affected = 1
                      14:54:17,767 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:54:17,767 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:54:17,767 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:54:17,767 DEBUG [Advisor] Executing SQL: UPDATE Advisor SET ol_version=?, branch=? WHERE id=? AND ol_version=?
                      14:54:17,767 DEBUG [Advisor] Rows affected = 1
                      14:54:17,767 DEBUG [Advisor#findByPrimaryKey] Executing SQL: SELECT id FROM Advisor WHERE id=?
                      14:54:17,767 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?

                      AFTER:
                      =====
                      // Update database record for advisor
                      company.ejb.Advisor advisor = advisorHome.findByPrimaryKey(delta.getId());
                      if (delta.getBranch() != null)
                      {
                      Long id = null;
                      try
                      {
                      id = delta.getBranch().getId();
                      company.ejb.Branch relatedEjb = branchHome.findByPrimaryKey(id);
                      advisor.setBranch(relatedEjb);
                      }
                      catch (FinderException e)
                      {
                      throw e;
                      }
                      }
                      else
                      {
                      advisor.setBranch(null); // clear relationship
                      }
                      advisor.setName(delta.getName());
                      advisor.setEmailAddress(delta.getEmailAddress());
                      advisor.setPhoneNumber(delta.getPhoneNumber());


                      14:56:55,335 DEBUG [Advisor#findByPrimaryKey] Executing SQL: SELECT id FROM Advisor WHERE id=?
                      14:56:55,335 DEBUG [Advisor] Executing SQL: SELECT name, emailAddress, phoneNumber, ol_version, branch FROM Advisor WHERE (id=?)
                      14:56:55,335 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:56:55,335 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:56:55,428 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:56:55,428 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:56:55,428 DEBUG [Advisor#findAdvisorByName] Executing SQL: SELECT DISTINCT t0_a.id FROM Advisor t0_a WHERE ((t0_a.name = ?))
                      14:56:55,428 DEBUG [AdvisorFacadeBean] Failed to find advisor ByName name=[AdvNEWER44]
                      14:56:55,428 DEBUG [Advisor#findByPrimaryKey] Executing SQL: SELECT id FROM Advisor WHERE id=?
                      14:56:55,428 DEBUG [Branch#findByPrimaryKey] Executing SQL: SELECT id FROM Branch WHERE id=?
                      14:56:55,428 DEBUG [Advisor] Executing SQL: SELECT name, emailAddress, phoneNumber, ol_version, branch FROM Advisor WHERE (id=?)
                      14:56:55,444 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:56:55,444 DEBUG [Branch] Executing SQL: SELECT name, ol_version FROM Branch WHERE (id=?)
                      14:56:55,444 DEBUG [Advisor] Executing SQL: UPDATE Advisor SET name=?, emailAddress=?, ol_version=?, branch=? WHERE id=? AND ol_version=?
                      14:56:55,444 DEBUG [Advisor] Rows affected = 1