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

Question

We get exceptions like this

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

when loading the testing of our application on MSSQL 2008 R2 (jTDS or official MS JDBC does not matter) . We never get this exception when running the same tests with PostgreSQL or MySQL .

We do not just want to increase the number of auxiliary threads for c3p0 (which solves the problem, but for how long?). We want to know what the problem is, because it works with other DBMSs. "

Applications behave like:

• Send X Requests
• Wait a while → DEADLOCK
• Send X Requests
• Wait a while → DEADLOCK

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

Thanks Adrian

(Btw. BoneCP works without any problems).

Answer 1

SQL Server has a much stricter locking strategy than PostgreSQL or InnoDB.

In particular, it blocks SELECT in rows (tables?) That are updated from another connection / transaction (at the default setting).

You must make sure that you do not select the same rows in one session that are updated from another.

If you cannot change the sequence of your code, you can avoid using dirty reads in SQL Server.

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

Edit
Another option (if you are on SQL Server 2005 or later) is to use the new "snapshot isolation" to avoid selection locks.