SQL Server 2008: getting deadlocks ... no locks

Question

Currently, I have been conducting some experiments in the SQL Server 2008 database. More specifically, I have a JDBC application that uses hundreds of parallel threads to perform thousands of tasks, each of which performs the following query in the database:

UPDATE from Table A where rowID='123' 

However, I get a ton of deadlock errors (SQL Exception 1205) when I set the isolation level above READ_UNCOMMITTED. This happens even if I set row locks, table locks and exclusive lock hints! And even in Snapshot Isolation, which does not use locks, I still get deadlock errors.

I skipped the trace through SQL Profiler to get a stub graph when this happens, but it was not very useful. He showed the victim process associated with the thread pool, linked by hundreds of other processes. You can check it out here:

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

Does anyone have any hints why this is happening? Over the past few days, I have gone crazy trying to figure it out. My current hypothesis is that it is related to any available workflows in my DB instance, the amount of available memory, or something that is not related to the actual locks at the query level.

Thanks!

Answer 1

You are faced with a more esoteric beast: a dead end resource. You have a thread that cannot spawn child tasks ( sys.dm_os_tasks ) to do its work, because all workers ( sys.dm_os_workers ) are busy. In turn, busy workers perform tasks that are likely blocked by normal gateways by the victim.

There are two lessons that I see here to take home:

1) The UPDATE you posted is trying to jump in parallel. If the update is exactly the same as you posted, then this means one and only one: there is no index on rowId .

2) You bounced on the upper ceiling set by the max worker threads parameter. It is not surprising that you abuse threads in the client ( hundreds of concurrent threads to execute thousands of task ) and multiply this on the server due to unwanted parallelism.

A BeginExecuteNonQuery design will use asynchronous execution ( BeginExecuteNonQuery ) over a truly asynchronous connection ( AsynchronousProcessing=true ) and use the pool of pending requests so that it does not exceed a certain threshold. Most likely, all the same, you would pass a whole batch of update values ​​using the table parameter evaluated , and then update the entire set or rows packed in one expression. I understand that all my links are for .Net, not Java, I don’t care, you can dig out the equivalent Java functionality yourself.

So, it’s interesting that you discovered such an esoteric dead end, it appears only because your design, well ... sucks.

Answer 2

Deadlocks / locks like this are weird and point to something outside the SQL server. For what it costs, we had a lot of deadlock problems that turned out to be a bottleneck on the disk!

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

Answer 3

You cannot do anything in SQL Server without locks - even the most basic query, plastered with NOLOCK statements, gives a scheme lock and possibly a few page locks at a minimal level.

To solve the deadlock, you need to get traces of the T1204 deadlock (for more details see “Troubleshooting the Silencer”, part 1 ), which will list the exact locks and objects involved in the deadlock - this should be enough information to find out (with the appropriate number of scratches on head), exactly what went wrong.

Changing the level of isolation without fully understanding the cause of the deadlock seems a little dangerous to me ...

As a hunch, this reminds me of a question I had several years ago - is this an UPDATE that blocks the execution of a SELECT ? (The T1024 trace will tell you this) And do you have a non-clustered index on rowID ? If so, you can take a look at this MSDN article , specifically Example 6: Nonclustered Indexes. If not, then check out this article, as this may help explain some other relevant deadlock scenarios, as well as post the results of T1024 trace if you need help analyzing it.