May 11, 2011 at 6:59 am
I am trying to understand following deadlock graph. I spent some time on my own and google-ing and eventulally came to this forum.
Here is what it has captured.
There are two Processes (82, 72) working on same table - Products with PK - ProductID. This has non-clustered-index on ProductID.
Deadlock graph shows that both processes are trying to work on updating some record for which they want to acquire RID lock and Key Lock.
As I understand - one process has got 'X' lock on RID and waiting for 'U' lock on KEY and second process has got 'U' lock on Key and waiting for 'U' lock on RID.
1) how is this possible, even if I assume both are updating same record?
2) what is the meaning of 'U' or 'X' lock on KEY? and when does processes do this?
3) and how can I avoid this?
Not sure if this is useful info - Note that UpdateProcedure1 also has a Insert statement that insert record in Products, though that happens only if some condition fails, etc.
<Column id="1" name="TextData">
<deadlock-list>
<deadlock victim="processb84d48">
<process-list>
<process id="processb84d48" taskpriority="0" logused="276" waitresource="RID: 9:1:928935:14" waittime="5000" ownerId="704932495" transactionname="user_transaction" lasttranstarted="2011-05-04T08:21:51.023" XDES="0x14aec6e0" lockMode="U" schedulerid="4" kpid="2208" status="suspended" spid="82" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-05-04T08:21:51.043" lastbatchcompleted="2011-05-04T08:21:51.030" clientapp=".Net SqlClient Data Provider" hostname="GPTDNFSQL1Q" hostpid="4480" loginname="App" isolationlevel="read uncommitted (1)" xactid="704932495" currentdb="9" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="Alpha_1.dbo.UpdateProcedure1" line="665" stmtstart="47790" stmtend="53056" sqlhandle="0x03000900a9d0835a5e2d8300d89e00000100000000000000"> UPDATE Products SET EntryDate = @EntryDate, LastUpdate = @LastUpdate, UpdatedBy = @UpdatedBy, SponsorAccount = @SponsorAccount, OpenedDate = @OpenedDate, ClosedDate = @ClosedDate, NSCCActiveDate = @NSCCActiveDate, NAVCodeID = @NAVCodeID, LOIAmount = @LOIAmount, LOIExpirationDate = @LOIExpirationDate, DistributionTypeID = @DistributionTypeID, PositionDate = CASE WHEN @OrigProductId = -1 THEN @PriceDate ELSE PositionDate END, PositionSourceID = CASE WHEN @OrigProductId = -1 THEN @PositionSourceID ELSE PositionSourceID END, Quantity = CASE WHEN @OrigProductId = -1 THEN @Quantity ELSE Quantity END, TeamID = @TeamID, RegTextLine1 = @RegTextLine1, RegTextLine2 = @RegTextLine2, RegTextLine3 = @RegTextLine3, RegTextLine4 = @RegTextLine4, RegTextLine5 = @RegTextLine5, RegTextLine6 = @RegTextLine6, RegTextLine7 = @RegTextLine7, AddressLineCode = @AddressLineCode WHERE ProductId=@RecordID </frame>
</executionStack>
<inputbuf> Proc [Database Id = 9 Object Id = 1518588073] </inputbuf>
</process>
<process id="processe314c8" taskpriority="0" logused="488" waitresource="KEY: 9:72057601536098304 (3a006a7be3de)" waittime="4953" ownerId="704932437" transactionname="user_transaction" lasttranstarted="2011-05-04T08:21:51.013" XDES="0x255544e0" lockMode="U" schedulerid="6" kpid="5932" status="suspended" spid="72" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-05-04T08:21:51.043" lastbatchcompleted="2011-05-04T08:21:51.030" clientapp=".Net SqlClient Data Provider" hostname="GPTDNFSQL1Q" hostpid="4480" loginname="App" isolationlevel="read uncommitted (1)" xactid="704932437" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="Alpha_1.dbo.UpdateProcedure2" line="60" stmtstart="4506" stmtend="4772" sqlhandle="0x03000900f1743745c30567016e9d00000100000000000000"> UPDATE Products SET UpdateVersion = @UpdateVersion, RecStatus = @RecStatus WHERE ProductId = @RecordID </frame>
<frame procname="Alpha_1.dbo.UpdateProcedure1" line="748" stmtstart="55110" stmtend="55260" sqlhandle="0x03000900a9d0835a5e2d8300d89e00000100000000000000"> EXEC @UserStatus = UpdateProcedure2 @RecordID, @UserStatus, 0, 0 </frame>
</executionStack>
<inputbuf> Proc [Database Id = 9 Object Id = 1518588073] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057601536098304" dbid="9" objectname="Alpha_1.dbo.Products" indexname="PK_Products" id="lock4011780" mode="U" associatedObjectId="72057601536098304">
<owner-list>
<owner id="processb84d48" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="processe314c8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<ridlock fileid="1" pageid="928935" dbid="9" objectname="Alpha_1.dbo.Products" id="lock19656c40" mode="X" associatedObjectId="107340968886272">
<owner-list>
<owner id="processe314c8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processb84d48" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
</Column>
Appreciate any help and response on this...thank you so much.
May 11, 2011 at 9:11 am
Praxo (5/11/2011)
I am trying to understand following deadlock graph. I spent some time on my own and google-ing and eventulally came to this forum.Here is what it has captured.
There are two Processes (82, 72) working on same table - Products with PK - ProductID. This has non-clustered-index on ProductID.
Deadlock graph shows that both processes are trying to work on updating some record for which they want to acquire RID lock and Key Lock.
As I understand - one process has got 'X' lock on RID and waiting for 'U' lock on KEY and second process has got 'U' lock on Key and waiting for 'U' lock on RID.
1) how is this possible, even if I assume both are updating same record?
2) what is the meaning of 'U' or 'X' lock on KEY? and when does processes do this?
3) and how can I avoid this?
Not sure if this is useful info - Note that UpdateProcedure1 also has a Insert statement that insert record in Products, though that happens only if some condition fails, etc.
(deadlock graph deleted)
Appreciate any help and response on this...thank you so much.
1. Your assumption is correct. In your case, both the index and the table need to be updated
1a. Process: processb84d48 has an update Key lock on Alpha_1.dbo.Products.PK_Products (Resource: KEY: 9:72057601536098304 (3a006a7be3de)), and is trying to acquire an update lock on Alpha_1.dbo.Products (Resource: RID: 9:1:928935:14). It is running the command: UPDATE Products SET EntryDate = @EntryDate, LastUpdate = @LastUpdate, UpdatedBy = @UpdatedBy, SponsorAccount = @SponsorAccount, OpenedDate = @OpenedDate, ClosedDate = @ClosedDate, NSCCActiveDate = @NSCCActiveDate, NAVCodeID = @NAVCodeID, LOIAmount = @LOIAmount, LOIExpirationDate = @LOIExpirationDate, DistributionTypeID = @DistributionTypeID, PositionDate = CASE WHEN @OrigProductId = -1 THEN @PriceDate ELSE PositionDate END, PositionSourceID = CASE WHEN @OrigProductId = -1 THEN @PositionSourceID ELSE PositionSourceID END, Quantity = CASE WHEN @OrigProductId = -1 THEN @Quantity ELSE Quantity END, TeamID = @TeamID, RegTextLine1 = @RegTextLine1, RegTextLine2 = @RegTextLine2, RegTextLine3 = @RegTextLine3, RegTextLine4 = @RegTextLine4, RegTextLine5 = @RegTextLine5, RegTextLine6 = @RegTextLine6, RegTextLine7 = @RegTextLine7, AddressLineCode = @AddressLineCode WHERE ProductId=@RecordID, located in Alpha_1.dbo.UpdateProcedure1.
1b. Process: processe314c8 has an eXclusive row lock on Alpha_1.dbo.Products (Resource: RID: 9:1:928935:14) and is trying to acquire an update lock on Alpha_1.dbo.Products.PK_Products (Resource: KEY: 9:72057601536098304 (3a006a7be3de)). It is running the command: UPDATE Products SET UpdateVersion = @UpdateVersion, RecStatus = @RecStatus WHERE ProductId = @RecordID, located in Alpha_1.dbo.UpdateProcedure2, which was called from Alpha_1.dbo.UpdateProcedure1.
2. Lock definitions can be found at http://msdn.microsoft.com/en-us/library/ms175519.aspx.
3. This is the harder question to answer. Are these particular queries doing any scans? (It doesn't seem like they would be.) Does the PK_Products NCI PK_Products have any include columns? Do you have a clustered index on this table? If so, what are it's columns? Are any of those columns being updated by either of the update statements?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 11, 2011 at 10:03 pm
thanks WayneS.
here is some more info...
Q.This is the harder question to answer. Are these particular queries doing any scans? (It doesn't seem like they would be.)
A> Nothing unusual as such. These procs are invoked from batch on continuous basis. There are two threads in batch. (that's is the reason I said, worst case secnario I will assume both are updating same record).
Q. Does the PK_Products NCI PK_Products have any include columns? Do you have a clustered index on this table? If so, what are it's columns? Are any of those columns being updated by either of the update statements?
A>The NCI dont have any include columns. Table doesn't have any clustered index.
Part of the problem I have is -
4) regarding the Order of locks (on RID and Key) that these processes are acquiring is different and I dont understand why. And if I look at the query these are almost same (updating just one record), except one query is updating bunch of columns in single record and other one is updating just 2 columns in a record.
5) secondly, when I look at MSDN for meaning of KEY, it says - A row lock within an index used to protect key ranges in serializable transactions. I dont understand why SQL is doing this for above statements. I am not updating any range here?
(BTW, second procedure UpdateProcedure2 is not standalone, it gets called from UpdateProcedure1.)
6) In an ideal world, I would hope that any 2 processes which are updateing same record in a table, would not lead to deadlock, rather one will wait till the other gets over with its update. Afterall these are simple transactions & statements?
May 13, 2011 at 4:51 am
http://stackoverflow.com/questions/2099659/sql-server-2005-deadlock-with-nonclustered-index
above link has some more details which helped me understand this issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply