Sql-server – PAGELOCK Conversion Deadlock From Update to Intent Exclusive

deadlocksql serversql-server-2005

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.

Best Answer

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.

Related Question