Why does SQLServer introduce transactional issues?

[cvandyck]

My environment:

JBoss 3.2.1
MSSqlServer 2000 (sp3a) / mysql3.x
Windows XP

We have a couple of pretty lengthy stateless session bean methods that have a transaction attribute of "Required", and basically sift through all of the entity EJBs in our system, writing data to those entities.

Because all of our entity setXXX() methods have their transaction attributes as "Required", updating these entities enrolls them in the transaction.

With mysql serving as the datastore for the CMP engine, this works perfectly. However, with MSSqlServer, I find that halfway through the stateless session bean process, everything stops. completely. I think that it is a deadlock of sorts, but I can't tell because there is nothing on the console, and the last thing in the boot.log is an SQL Update that apparently never gets executed because of this lock situation.

I guess that I'm pretty confused about how to troubleshoot this. If I take out the offending entity update statements in the code, then everything works fine with SQLServer, but I need those update statements so that I can mark the last time those entities were updated. I have tried changing the setXXX method on the entity bean to "RequiresNew" and "NotSupported" in a debugging effort, but was met with the same result both times.

After such a freeze, I get this on the console after a couple of minutes:

16:20:56,953 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e199c0a801260113cd63982764be
16:20:56,953 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e185c0a801260113cd63ae588dbd
16:20:56,968 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=acb22731c0a80126018790d712600fb7
16:20:56,968 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e474c0a801260113cd63830dc9f0
16:20:56,968 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e398c0a801260113cd63efa36e01
16:20:56,984 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e3c0c0a801260113cd631e47e551
16:20:56,984 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=831ecb84c0a8012601fafb25f83a6573
16:20:57,000 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=31a9ba5fc0a8080b00fa955724c1670f
16:20:57,000 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=4f910f36c0a8012601f43d694dc22984
16:20:57,015 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=318c6a11c0a8080b00fa9557392c8b14
16:20:57,015 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=68e6cb1ec0a80126019300896b689f6f
16:20:57,031 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=68e920bcc0a80126019300897bffc685
16:20:57,031 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=21a91244c0a8012601327777684eea31
16:20:57,031 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=42616f42c0a8080901ab2ddb85c51bea
16:21:50,468 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e199c0a801260113cd6300a1dd39
16:21:50,468 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e185c0a801260113cd63d02826be
16:21:50,468 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=c028aa8ec0a8080b01300800d7d5066c
16:21:50,484 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e474c0a801260113cd63cd7353ae
16:21:50,484 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=2082e398c0a801260113cd634eac9077
16:21:50,500 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=aeffbe5cc0a8081600b27bb505bf0d6d
16:21:50,500 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=319cff96c0a8080b00fa9557faba1e4a
16:21:50,515 WARN [AbstractInstanceCache] Unable to passivate due to ctx lock, id=31ae443dc0a8080b00fa9557343890fd

This is now my mssql-ds.xml looks:


<local-tx-datasource>
<jndi-name>ContentXMLDS</jndi-name>
<connection-url>jdbc:microsoft:sqlserver://localhost:1089;DatabaseName=contentxml;SelectMethod=cursor</connection-url>
<driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
<user-name>sa</user-name>
-removed-
<min-pool-size>0</min-pool-size>
<max-pool-size>1000</max-pool-size>
<blocking-timeout-millis>5000</blocking-timeout-millis>
</local-tx-datasource>


Basically, the only thing I changed to switch from mysql to sqlserver was to remove the mysql-ds.xml, and replace it with my mssql-ds.xml.

In short, I'd like to be able to figure out why it is freezing. Is there a recommended way to do this? My console does not give me enough information for this task, as well as the log files.

Thanks,
Collin

[tallpsmith]

Speaking from some non-J2EE experience, but with exactly the same symptoms with SQL Server 2000, I think I can explain something that might help you track it down.

It sounds like it all comes back to the way SQL Server decides to promote row level locks to table locks. I think SQL Server sees this Connection doing a decent amount to some tables and decides for performance reasons to promote the row/page lock to a table lock.

This is all well and good except I am guessing your Session bean (or whatever the current thread walks across) at some point in time decides to use a DIFFERENT connection to do some look up of some thing.

Usually what has happened to me is that lookup somehow needs to look at the table the first connection has a table lock on, and of course blocks waiting for it to complete. Since this is all in the same thread as the first connection, DEADLOCK.

Why this seems to happen in SQL Server and not other DB's is because SQL Server seems particularly eager to promote the row/page lock to table lock. Never seen it do this on Postgres either. I read heaps of docs on SQLServer and the promotion to table locks can often happen much quicker if you have no or old statistics on your tables.

The only work around we managed to do for our non-J2EE app was to damn well ensure a single thread only ever had one connection to the DB, so that it could not dead lock itself. We managed to do this in our app, but I can imagine some scenarios where this doesn't work for everyone.

There is also a HotFix for the SQL Server 2000 JDBC driver you can get if you quote a particular Knowledge base #, but this didn't help us, but perhaps you could try it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;817326

(Should the link not work the KB article is 817326)

hope this helps.

Paul Smith

[cvandyck]

tallpsmith -- wow, thanks for the detailed response. we eventually addressed the problem by segregating our long method that did not alter any tables and the shorter method which updated all rows used in the first method into two different transactions.

So the first transaction would be run, and it returned a list of table/rows which the controller then used to spawn the second method that updated those tables.

It wasn't the most elegant way to get around it, but at least it works on both mysql and sqlserver.

I'll keep this information in mind, should it ever come up again (which I'm sure it will!!).

Thanks!
Collin