SQL Server 2008: Getting deadlocks... without any locks

Question

I'm currently conducting some experiments on a SQL Server 2008 database. More specifically, I have a JDBC application that uses hundreds of concurrent threads to execute thousands of tasks, each of which runs the following query on the database:

UPDATE from Table A where rowID='123'

However, I'm getting a ton of deadlock errors (SQL Exception 1205) whenever I set the isolation level to be higher than READ_UNCOMMITTED. It happens even if I set row locking, table locking, and exclusive lock hints! And even in Snapshot Isolation, which doesn't utilize locks, I still get deadlock errors.

I ran a trace via SQL Profiler to get the deadlock graph when this happens, but it wasn't of much use. It showed the victim process, connected to a "Thread Pool", connected to hundreds of other processes. You can check it out here:

http://i.stack.imgur.com/7rlv3.jpg

Does anyone have any hints as to why this is happening? I've been going crazy over the past few days trying to figure it out. My current hypothesis is that it's something related to either available worker threads in my DB instance, the amount of memory available, or something that isn't related to actual query-level locks.

Thanks!

Answer 1

You have encountered a more esoteric beast: a resource deadlock. What you have there is a thread than cannot spawn child tasks (sys.dm_os_tasks) to execute its work because all workers (sys.dm_os_workers) are busy. In turn, the busy workers execute tasks that are blocked, likely on ordinary locks, by the victim.

There are two lessons I see here to take home:

1) The UPDATE you posted is attempting to go parallel. If the update is exactly as you posted, then it means one and only one thing: no index on rowId.

2) You have bounced on the upper ceiling set by max worker threads setting. No wonder, considering that you abuse threads in the client (hundreds of concurrent threads to execute thousands of task) and multiply this in the server due to unwanted parallelism.

A sensible design would use async execution (BeginExecuteNonQuery) on a truly async connection (AsynchronousProcessing=true) and use a pool of pending requests so it does not go above a certain threshold. More likely, still, is that you would pass in an entire batch of update values by a table valued parameter and then update an entire set or rows, batched, in a single statement. I understand that all my links are for .Net, not for Java, I don't care, you can dig out the equivalent Java functionality yourself.

So while is interesting that you discovered such an esoteric deadlock, it only shows up because your design, well... sucks.