Why does this query cause a deadlock?

It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table.

The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess is clustered and also part of the IX_system_Queue_DirectionByStatus key value).

Anyway, my guess is this query would only succeed on the rare chance that the index values it is selecting and updating are not conflicting. Is it deadlocking each time you execute (I assume that it would be).

Here is a link that explains deadlocks in more detail: http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

I don't expect you to mark this post as an answer but sharing more information here by other SQL Server experts on this topic.

• http://web.archive.org/web/20111222024618/http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx

• http://rusanu.com/2009/05/16/readwrite-deadlock/