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:

https://i.sstatic.net/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.

Answer 2

You can't do anything in SQL Server without locks - even the most basic query plastered with NOLOCK statements will issue a schema lock and probably a few page locks at the bare minimum.

In order to resolve a deadlock you need to get a T1204 deadlock trace (see Deadlock Troubleshooting, Part 1 for more details) which will list the exact locks and objects involved in the deadlock - this should be enough information to figure out (with the appropriate amount of head scratching) exactly what went wrong.

Changing the isolation level without fully understanding the reason behind a deadlock seems a tad dangerous to me...

As a hunch this reminds me of an issue I had a few years back - is the UPDATE statement deadlocking against a SELECT statement? (The T1024 trace will tell you this) And do you have a non-clustered index on rowID? If so you might want to take a look at this MSDN article, specifically Example 6: Nonclustered indexes. If not then still take a look through that article as it may help explain some other relevant deadlock scenarios, also post the results of the T1024 trace if you need help analysing it.

Answer 3

Deadlocks/locks like this are strange and point to something outside SQL server. For what is worth, we had a lot of deadlock issues that turned out to be disk bottleneck!

I suggest you run perfmon (and obviously after that a lot of other tools) and see how it is doing.