Clustered Key deadlock
- 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.
- 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
- 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
- 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.
select * from SiteQuota
where %%LOCKRES%% in ('(8602646f03cd)', '(87020108bf75)')
Note: Once I have completed my analysis I will be presenting it to Microsoft.
As of 1344 hours Easter Time, no deadlocks to report.