Clustered Key deadlock

SQLJarhead 2013-02-22 16:13:39

link textTrying to understand why two threads would deadlock on the other's resource when each thread utilizes a clustered index seek to obtain access to their row.

  1. Survivor (SPID 459) explicitly started a transaction, then requested and received an exclusive lock on Owning clustered key SiteQuota_SiteID, KEY: 35:72057611051008000 (87020108bf75) which belonged to the SiteQuota table.
  2. Victim (SPID 340) implicitly started a transaction, then requested and received an exclusive lock on Owning clustered key SiteQuota_SiteID, KEY: 35:72057611051008000 (8602646f03cd) which belonged to the SiteQuota table
  3. Victim, as part of the same transaction, requested an exclusive lock on requesting cluster key, KEY: 35:72057611051008000 (87020108bf75), owned by Survivor. This was not granted because Survivor had a non-compatible lock
  4. Survivor, as part of the same transaction, requested an shared lock on requesting cluster key, KEY: 35:72057611051008000 (8602646f03cd), owned by Victim. This was not granted because Victim had a non-compatiable lock.

Survivor (SPID 459), called procedure proc_DeleteURL which begins an explicit transaction, while still inside a transaction, it called another child procedure proc_DeleteUrlCore, and finally calls yet another child procedure proc_AppendSiteQuota. While still in an open transaction, Proc_AppendSiteQuota performs an insert into the SiteQuota table, and insert requires and exclusive lock at the row level and will be held for the duration of the transaction, which can be see via the deadlock output. The insert completes allowing the previous procedure to continue and eventually giving scope back to the parent procedure proc_DeleteURL. Proc_DeleteURL now reaches the proc_UpdateDiskUsed procedure issuing the SELECT statement captured in the deadlock output.

Victim (SPID 340), called proc_UpdateListItemWorkflowInstanceData which in turns calls proc_UpdateDiskUsed issuing the DELETE statement captured in the deadlock output.

My confusion is why each SPID is requesting a KEY (row) lock on the other resource when they are issuing a seek? The only missing piece to this puzzle, which I have a server-side trace running, is the actual value for the WHERE predicate @SiteID.

I have attached a zip file containing detailed objects to help understand this deadlock.

Once again, thank you for your time.

/storage/attachments/273-proc_UpdateDiskUsed_1.queryanalysis
/storage/attachments/274-deadlock_2013_092617890.zip
JasonRHall 2013-02-22 16:38:13
I see that you've included quite a bit of information. I just wanted to note for readers that the deadlock XML file included in the zip file can be opened in Plan Explorer PRO if you rename the file extension to .xdl.
GregGonzalez 2013-02-22 18:20:29
I haven't had a chance to analyze the deadlock in detail, but to address the "missing piece" question you should be able to get the associated SiteId key values using the query below, assuming the rows still exist:

select * from SiteQuota
where %%LOCKRES%% in ('(8602646f03cd)', '(87020108bf75)')

SQLJarhead 2013-02-22 18:22:45
Unfortunately, the SiteQuota table is empty. Must be used as a staging table while the session is active. However, I was able to retrieve the deleted record off the page (GHOST record), which is not useful since I cannot correlate with the deadlock output.
SQLkiwi 2013-02-24 06:59:54
Are you sure your deadlock analysis is correct? Session 340 calls UpdateListItemWorkflowInstanceData, which does go on to call UpdateDiskUsed, but this occurs after the transaction is committed.
SQLJarhead 2013-02-25 13:17:35
Hello SQLKiwi, Yes it is correct and you are also right. UpdateDiskUsed is actually called after the commit. I was only alluding to the fact that SQL Server implicitly started a transaction. I am currently waiting for the deadlock to occur again in hopes to capture the parameter values. More to come…
SQLkiwi 2013-02-25 17:14:02
To be clear, my question is how 340 is still holding an exclusive lock (in the scenario you described) if the transaction has committed. (We can talk about non-unique clustered uniqueidentifier indexes later).
SQLJarhead 2013-02-25 17:22:41
That is my confusion as well. Spid 340 had issued a delete for a specific site id, why is it requesting a lock on spid 459's resource?
Bogdan SAHLEAN 2013-02-25 17:06:56
Why SiteQuota_SiteId clustered index (table [dbo].[SiteQuota]) is not unique ?
SQLJarhead 2013-02-25 17:29:27
That is a great question and it should have been defined using the unique keyword. However, this is a Microsoft Share Point database so I cannot answer that question. Moreover, we have a case opened with Microsoft and they will not provide a fix other than asking us to update statistics and run index defrag. Moreover, I had informed them that it will not provide any benefit considering the table is always empty and used for storing session information.

Note: Once I have completed my analysis I will be presenting it to Microsoft.

SQLkiwi 2013-02-25 17:59:20
Aha! So this is a SharePoint database – now it all makes sense (there are so many S/P horror stories…:) Anyway, I guess you're pretty limited as to the changes you can make here (i.e. probably none) so I'll hold off making suggestions until more detail is available.
SQLJarhead 2013-02-25 18:46:58
LOL! Yes sir it is and I had made a few index modifications in the past which eliminated other deadlocks, oops, you did not hear that. As far as making code changes, I cannot implement only recommend.

As of 1344 hours Easter Time, no deadlocks to report.

SQLJarhead 2013-03-08 13:22:42
As of March 8 @ 0822 hours EST the deadlock has not surfaced.
SQLkiwi 2013-03-08 23:06:38
Thanks for the feedback. Given the lack of a recurrence of the problem, I'm going to close this question. I hope the discussion in the comments was helpful to you though!
February 22, 2013
No Comments