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.
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).
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.
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.