C3p0 - APPARENT DEADLOCK on MSSQL, but not PostgreSQL or MySQL

Question

We are getting exceptions like this

com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@5b7a7896 -- APPARENT DEADLOCK!!! Complete Status: 
Managed Threads: 3
Active Threads: 3
Active Tasks: 
    com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@55bc5e2a (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
    com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@41ca435f (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
    com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@460d33b7 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
Pending Tasks: 

when load testing our application on MSSQL 2008 R2 (jTDS or official MS JDBC doesn't matter). We never get this exception when running the same tests against PostgreSQL or MySQL.

We don't just want to increase the number of helper threads for c3p0 (which solves the problem, but how long?). We want to know what's the problem as it is workings with other DBMS'.

The applications behaves like:

• Send X requests
• Wait for a while -> DEADLOCK
• Send X requests
• Wait for a while -> DEADLOCK

Does anyone know or has an idea why we have this behavior with MSSQL?

Thanks, Adrian

(Btw. BoneCP works without any problem too.)

Answer 1

SQL Server has a much more restrictive locking strategy compared to PostgreSQL or InnoDB.

Especially it will block SELECTs on rows (tables?) that are updated from a different connection/transaction (in the default installation).

You should make sure that you are not selecting the same rows in one session that are being updated from another.

If you can't change the sequence of your code, you might get away with using "dirty reads" in SQL Server.

If I remember that correctly, this is accomplished by adding WITH NOLOCK to the SELECT statements (but I'm not entirely sure)

Edit
A different possibility (if you are on SQL Server 2005 or later) would be to use the new "snapshot isolation" to avoid blocking selects.