Kwisatz78 (7/21/2010)
Oh wow I was just looking at some of your code on another post 🙂 it makes it much easier to read thanks.I do have some niggly questions like why does the LockMode U appear in the graph for the delete and vice versa? I assume its just the way the graph is rendered but it bugs me :s
The other question is what importance does the Input Buffer have, I think I was following this as a red herring. The proc mentioned in the Input Buffer actually ends up being proc_MSS_ProcessCommitted how does this relate to the deadlock? This proc is also mentioned in the <frame procname for both processes and am confused as to its relevance in the graphs.
I will also look at the execution plans for the two procs in questions, I no doubt will have more questions ..grr...
Many thanks 🙂
The input buffer is the code that was called - it looks like you've identified it. It appears to contain both the update and delete statements.
I was looking into your graph a little bit more, and noticed that my code isn't returning what object is being locked. Looking into your graph, I see that they are locking on indexes. So, I've modified the code to return the indexes being locked.
select
[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'),
[Index] = Keylock.Process.value('@indexname', 'varchar(200)'),
[IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') =
Deadlock.Process.value('@id', 'varchar(50)')
This shows that the delete has an exclusive lock on the PK, and wants an update lock on the IX. The update has an exclusive lock on the IX, and wants an exclusive lock on the PK. The queries are pretty simple, so I'd check to see if there is an index on HostID, and one on DocID/BatchID.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes