Database locking problem with CMP and SQL Server

[david.salter]

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.

[fredrikbromee]

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

[david.salter]

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.

[fredrikbromee]

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

[david.salter]

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

[aloubyansky]

You can optimize instance creation
http://www.jboss.org/wiki/Wiki.jsp?page=CMPInstanceCreation

[david.salter]

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.

[aloubyansky]

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>
...