7 Replies Latest reply on Aug 17, 2004 5:20 AM by Alexey Loubyansky

    Database locking problem with CMP and SQL Server

    David Salter Novice

      I have developed some entity beans and have deployed them on JBoss 4.0RC1 (and also JBoss 3.2.4) against a SQL Server 2000 database. CMP BeanA has a 1 to many relationship with CMP BeanB which in turn has a 1 to many relationship with CMP BeanC. i.e. 1 BeanA has many BeanB and 1 BeanB has many BeanC. I have developed a stateless session bean as a facade to the entity beans. The session bean has a createNew() method that takes a dto. This method in the session bean creates a BeanA, a BeanB and a BeanC and sets their attributes as appropriate.

      I have a test client that invokes the stateless session bean repeatedly to try to stress test the system and see how many transactions it can perform per second. This works fine with one test client (which calls a remote interface on the session bean). When I have two clients accessing the session bean however, I see deadlocks appearing in the database. After 10 or 20 seconds of running like this, the database gets completely blocked and I see that there are several connections blocking other connections in the database. As the database is effectively then blocked for all our requests, all the test clients stop responding.

      Does anyone else have this problem? Is it a common problem with the architecture that I have? Are there any hints or resources that can help solve this problem?


      My test environment is:

      Windows XP,
      JBoss 4.0 RC1 (also tried on JBoss 3.2.4) - using java 1.4.2_03
      MS SQL Server 2000
      Microsoft JDBC drivers for MSSQL Server
      Test clients are simple java application running in JDK 1.5.0-beta2

      JBoss, SQL Server and the test clients are all located on the same machine at present for testing.

        • 1. Re: Database locking problem with CMP and SQL Server
          fredrikbromee Newbie

          Hi, I don't know if what I'll say applies to your situation but here goes:

          What is it your test client is trying to do? Is it only creating more and more
          of BeanA, or is it doing updates on them? Is createNew() the only method
          being called?

          These deadlocks that occur, are they only in the db, or do you get them in
          the application as well (with Jboss giving ApplicationDeadLockExceptions)?

          It is quite easy to end up in a deadlock situation. I don't know if this is your case but anyway:
          Thread 1 (client 1) has a lock on bean A and is about to
          update bean B and at the same time
          Thread 2 (client 2) has a lock on bean B and is about to update bean A
          This would result in a deadlock.

          If you've got the for-pay documentation, you could read chapter 5 of the
          Jboss Admin and Devel guide. It's got a section on avoiding deadlocks.
          One of the tips is to order your bean access , that is first update bean A, then bean B, then C etc.

          It could also have something to do with your db connection's isolation level.

          Hope this helps,
          /fredrik

          • 2. Re: Database locking problem with CMP and SQL Server
            David Salter Novice

            Hi,

            createNew() is the only method being called. This creates the CMP beans only and the relationships between them. I am not knowingly doing any updates, but the CMP engine may be.

            I have just turned off hyperthreading on my pc (having had bad experiences of it before) and the situation has got slightly better. I no longer get blocks in the database that stop all clients from running. Now all I get is deadlocks in the database. SQL Server is then recovering from these deadlocks and is creating a deadlock victim and therefore rolling back one of my CMP transactions.

            The performance is incredibly bad however (presumably because of the deadlocks in the database) when comparing running with 2 clients to one client.

            Are there any best practices/guidelines for getting good performance out of CMP beans without getting database deadlocks? Also, do you know what I should do when I get a deadlock in my app - is it just a case of trapping the deadlock in the session bean facade and then running the whole transaction again?

            Cheers,

            Dave.

            • 3. Re: Database locking problem with CMP and SQL Server
              fredrikbromee Newbie

              You could turn on Jboss logging to see what sql stmts jboss generates.
              Change in log4j.xml:

              ...
               <!-- Append messages to the console -->
               <!-- ============================== -->
              
               <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
               <errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
               <param name="Target" value="System.out"/>
               <param name="Threshold" value="DEBUG"/>
              
              ...
               <category name="org.jboss.ejb.plugins.cmp">
               <priority value="DEBUG" />
               </category>
              
               <!-- Limit JBoss categories to INFO -->
               <category name="org.jboss">
               <priority value="INFO"/>
               </category>
              ...
              
              


              My guess is that your app from within a transaction first inserts in
              table A, then in table B, and then maybe updates A (for the relation).
              But check what jboss generates.

              I'm no expert but I'd say that trying to catch a deadlock exception and
              then try to run the tx again wouldn't be my first option. If like in this case
              you know which method that causes the deadlock I'd try first to make
              sure the deadlock never happens. Agreed, in this case it might be hard
              since CMR is quite tricky to get right (at least in my opinion).

              HTH, /fredrik




              • 4. Re: Database locking problem with CMP and SQL Server
                David Salter Novice

                I've put a profile trace on the database and that is exactly what I see.

                CMP does an insert to initially create the bean and then does an update afterwards to save the relationships.

                I can't change the way it does this can I?

                Does this mean that I can't effectively use CMP and CMR for the scenario I have without a high chance of getting deadlocks?

                Cheers,

                Dave

                • 6. Re: Database locking problem with CMP and SQL Server
                  David Salter Novice

                  Thanks for the tip!

                  The no-select-berfore-insert works as expected, but I still get inserts and then updates when using the INSERT after ejbPostCreate option

                  My jboss.xml looks like



                  <?xml version="1.0"?>
                   <!DOCTYPE jboss PUBLIC
                   "-//JBoss//DTD JBOSS 4.0//EN"
                   "http://www.jboss.org/j2ee/dtd/jboss_4_0.dtd">
                  <jboss>
                   <enterprise-beans>
                   </enterprise-beans>
                   <container-configurations>
                   <container-configuration extends="Standard CMP 2.x EntityBean">
                   <container-name>INSERT after ejbPostCreate Container</container-name>
                   <insert-after-ejb-post-create>true</insert-after-ejb-post-create>
                   </container-configuration>
                   </container-configurations>
                  </jboss>
                  


                  Any ideas why it doesn't work. I'm using 4.0 RC1 - does it work on that version?

                  Ta,

                  Dave.


                  • 7. Re: Database locking problem with CMP and SQL Server
                    Alexey Loubyansky Master

                    Yes, it does. You need to actually use this container

                    <jboss>
                     <enterprise-beans>
                     <entity>
                     <ejb-name>D</ejb-name>
                     <configuration-name>INSERT after ejbPostCreate Container</configuration-name>
                     </entity>
                    ...