Take the 2-minute tour ×

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:


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.


share|improve this question
Seen this already? Does your update statement have a parallel plan? –  Martin Smith Mar 28 '11 at 21:32
And are you saying that these deadlocks never occur when READ_UNCOMMITTED is in effect? It isn't at all obvious to me how that would affect the update statement shown. –  Martin Smith Mar 28 '11 at 21:41
Wow! I wasn't expecting such an overwhelming response in such a short amount of time! Deadlocks still occur under READ_UNCOMMITTED but only when there are many, many, many more concurrent threads going on (around 1000). I apologize for the vagueness. –  akwok Mar 28 '11 at 23:27

3 Answers 3

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.

share|improve this answer
I would have +1 for brilliant analysis.. but tempered that with -1 for, shall we say, delivery? Omission of 2 words or so would have read.. nicer? –  RichardTheKiwi Mar 28 '11 at 23:02
Thanks for the response, Remus. I understand that the design sucks, but it's done on purpose! I'm working on a project that's supposed to show that 1.) different read anomalies exist depending on the type of isolation level chosen and to show 2.) empirical performance hits for choosing an isolation level that restricts concurrency moreso than necessary. In any case, your comments do make sense, and I will look into it a bit more; at least you were able to provide me a direction to do further reading on :-) –  akwok Mar 28 '11 at 23:29
@akwok: I see. Consider that the UPDATE has to first find the rows to update, then update them. The 'find' part is influenced by isolation level. Even REPEATABLE_READ can choose to scan at a high granularity lock level (page, table). Second thing to consider is that the 'updates' part will conflict with another update under all isolation levels, including snapshot. Furthermore, updates of different rows will conflict, due to hash collisions: rusanu.com/2009/05/29/… –  Remus Rusanu Mar 28 '11 at 23:59
@akwok: as for performance impact of overzealous concurrency levels, hear hear: blogs.msdn.com/b/dbrowne/archive/2010/05/21/… –  Remus Rusanu Mar 29 '11 at 0:03
@Richard: I don't like small print, what can I say :) hulu.com/watch/10335/saturday-night-live-pet-chow –  Remus Rusanu Mar 29 '11 at 0:08

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.

share|improve this answer

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.

share|improve this answer

Your Answer


