Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a page, and both try to up-convert the U page lock to an intent exclusive (IX) lock. Sometimes there is only one page involved; sometimes several.

We captured a deadlock trace using the trace flag 1222. The SQL Server log shows many, many deadlocks with the following pattern (in bottom-to-top order):

waiter id=processe0dc2088 mode=U requestType=convert
waiter id=process2f9db2478 mode=U requestType=convert
waiter-list
owner id=processe0dc2088 mode=IX
owner id=process2f9db2478 mode=IX
owner-list
pagelock fileid=1 pageid=5794 dbid=2 objectname=tempdb.dbo.Item_Package_Site_Costs_Work id=lock1b22de480 mode=IX associatedObjectId=72057594131775488
resource-list

Both processes are running the same UPDATE statement to set a flag on this tempdb table. This tempdb table holds information that needs to persist between client calls until the client is done. The table has a fairly long index that starts with a GUID representing a unique process ID.

I am having difficulty understanding and simulating this deadlock. I have tried various amounts of records with simulated data.

My questions:

Why are these processes acquiring U locks and then converting to IX? I would expect the DELETE to acquire IX locks to begin with.

How can I prevent the deadlock?

The statement causing the deadlock is below. The process has just done a lookup of costs for a list of items at a single store. It is trying to note that there was a cost found.

Note that there is a deprecated (NOLOCK) on an UPDATE statement. Would this be a contributing factor?

UPDATE tempdb..Item_Package_Site_Costs_Work
SET ItemPkgSiteCost_VINCostFound = 1,
    ItemPkgSiteCost_VendCost_Key = SiteCosts_VendCost_Key
FROM tempdb..Item_Package_Site_Costs_Work (NOLOCK)
    INNER JOIN #SiteCosts
        ON ItemPkgSiteCost_GUID = @ProcGUID
        AND SiteCosts_Item_Type = 0 -- Standard
        AND ItemPkgSiteCost_Site_Key = SiteCosts_Input_Site_Key 
        AND ItemPkgSiteCost_Item_Key = SiteCosts_Item_Key
        AND ItemPkgSiteCost_ItemPkg_Key = SiteCosts_Input_Sel_ItemPkg_Key
        AND ItemPkgSiteCost_VendItem_Key = SiteCosts_VendItem_Key
        AND ISNULL(ItemPkgSiteCost_Qty_Recv, 1) = SiteCosts_Input_Qty_Recv

The customer's server @@version is:

Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

So far I have not been able to capture the query plan used at the time of the deadlock, and the normal ways I try to retrieve the query plan are not returning anything(sys.dm_exec_query_plan and sys.dm_exec_text_query_plan both return NULL).

UPDATE 2013-08-29

The customer installed SQL Server 2005 SP 4, but they are still seeing this deadlock. I will pursue removing the deprecated (NOLOCK) on the tables being modified and see if this fixes the deadlocks.

share|improve this question
4  
Two processes cannot hold an update lock on the same resource. Not possible. The error log snippet suggests two processes with IX converting to U. Can you post the deadlock XML? DDL for the table would also be useful. And just because if I don't say it, someone else will... 2005 SP1? –  Mark Storey-Smith Aug 6 '13 at 22:59
1  
Second glance... Yes, remove the NOLOCK. If that doesn't fix it, previous comments apply. It's a daft scenario that I've not seen before and I'm sure someone will reproduce it out of curiosity if I don't. –  Mark Storey-Smith Aug 6 '13 at 23:21
    
@MarkStorey-Smith Good point about the SP1. Recommended to the customer to try SP4. –  Paul Williams Aug 7 '13 at 13:39
    
Would it help at all to break this into 2 statements? First to select the ID to update (looking much like your example) followed by a much simpler single table and single where statement update? –  Paul Aug 7 '13 at 14:03
    
I found this article that looks useful. In particular paragraph 2 of "Update locks" seems to describe your situation? sql-server-performance.com/2004/advanced-sql-locking –  Paul Aug 7 '13 at 15:07

1 Answer 1

First of all, the process is holding IX and want to convert to U. This is expected with the hint in the query.

Both queries will take the IX lock during the select and then convert to U when the change (if any) needs to happen.

This can be solved by adding: WITH (XLOCK) as a hint to the temporary table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.