October 9, 2010 at 2:01 pm
1st graph
<deadlock-list> <deadlock victim="processc25828"> <process-list> <process id="processbbf048" taskpriority="0" logused="28892" waitresource="PAGE: 14:1:340676" waittime="3984" ownerId="484691527" transactionname="ius" lasttranstarted="2010-09-30T10:03:22.080" XDES="0xa50f54c0" lockMode="IX" schedulerid="1" kpid="10288" status="suspended" spid="65" sbid="0" ecid="0" priority="0" transcount="4" lastbatchstarted="2010-09-30T10:03:22.080" lastbatchcompleted="2010-09-30T10:03:22.080" clientapp="CBS" hostname="DAWEBP100" hostpid="8976" loginname="DMZ\xProdCBS" isolationlevel="read committed (2)" xactid="484691527" currentdb="14" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="TsiCbs.dbo.tsi_sp_object_insert" line="28" stmtstart="1170" stmtend="1358" sqlhandle="0x03000e00f578a27585a8a500169c00000100000000000000"> UPDATE tsi_t_objects SET NRight = NRight + 2 WHERE NRight >= @parentRight </frame> <frame procname="TsiCbs.dbo.tsi_tg_object_insert" line="17" stmtstart="760" stmtend="826" sqlhandle="0x03000e00e823327aa6b3a500169c00000000000000000000"> EXEC tsi_sp_object_insert @id </frame> <frame procname="TsiCbs.dbo.tsi_sp_insert_update_object" line="21" stmtstart="862" stmtend="1350" sqlhandle="0x03000e002e9d967693a8a500169c00000100000000000000"> INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk, SortOrder, [Guid], ExternalId) VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId) </frame> <frame procname="TsiCbs.dbo.x_sp_insert_update_person" line="39" stmtstart="1988" stmtend="2244" sqlhandle="0x03000e00f704cd06c7572001b79c00000100000000000000"> EXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId, @modifiedById, @sortOrder, @externalId </frame> <frame procname="TsiCbs.dbo.x_sp_insert_update_user" line="59" stmtstart="3232" stmtend="3934" sqlhandle="0x03000e001646bc1cd7572001b79c00000100000000000000"> EXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId, @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2, @middleInitial, @middleName, @phoneNumber1, @phoneType1Id, @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id, @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId </frame> </executionStack> <inputbuf> Proc [Database Id = 14 Object Id = 482100758] </inputbuf> </process> <process id="processc25828" taskpriority="0" logused="0" waitresource="PAGE: 14:1:340909" waittime="3984" ownerId="484691470" transactionname="SELECT" lasttranstarted="2010-09-30T10:03:22.017" XDES="0x802e2f88" lockMode="S" schedulerid="2" kpid="38908" status="suspended" spid="60" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2010-09-30T10:03:22.017" lastbatchcompleted="2010-09-30T10:03:22.017" clientapp="CBS" hostname="DAWEBP100" hostpid="8976" loginname="DMZ\xProdCBS" isolationlevel="read committed (2)" xactid="484691470" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="TsiCbs.dbo.tsi_sp_list_securing_groups" line="8" stmtstart="366" stmtend="706" sqlhandle="0x03000e008a1acf2fafada500169c00000100000000000000"> SELECT * FROM tsi_v_secured s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = s.SecurityGroupId WHERE SecuredId = @securedId AND PermissionId = </frame> </executionStack> <inputbuf> Proc [Database Id = 14 Object Id = 802101898] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="340909" dbid="14" objectname="TsiCbs.dbo.tsi_t_objects" id="lock979d9980" mode="IX" associatedObjectId="72057594529382400"> <owner-list> <owner id="processbbf048" mode="IX"/> </owner-list> <waiter-list> <waiter id="processc25828" mode="S" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="340676" dbid="14" objectname="TsiCbs.dbo.tsi_t_objects" id="locka6139580" mode="SIU" associatedObjectId="72057594529382400"> <owner-list> <owner id="processc25828" mode="S"/> </owner-list> <waiter-list> <waiter id="processbbf048" mode="IX" requestType="convert"/> </waiter-list> </pagelock> </resource-list> </deadlock> </deadlock-list>
2nd Graph
<deadlock-list> <deadlock victim="processbbeef8"> <process-list> <process id="processbbeef8" taskpriority="0" logused="0" waitresource="KEY: 14:72057594529382400 (aa00a25ad511)" waittime="7843" ownerId="484691567" transactionname="SELECT" lasttranstarted="2010-09-30T10:03:23.220" XDES="0xc8220868" lockMode="S" schedulerid="1" kpid="30564" status="suspended" spid="76" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2010-09-30T10:03:23.220" lastbatchcompleted="2010-09-30T10:03:23.220" clientapp="CBS" hostname="DAWEBP100" hostpid="8976" loginname="DMZ\xProdCBS" isolationlevel="read committed (2)" xactid="484691567" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="TsiCbs.dbo.x_sp_get_user_by_username" line="4" stmtstart="164" sqlhandle="0x03000e00a5436923ffaba500169c00000100000000000000"> SELECT * FROM x_v_users WHERE Username = @username; </frame> </executionStack> <inputbuf> Proc [Database Id = 14 Object Id = 594101157] </inputbuf> </process> <process id="processbbf048" taskpriority="0" logused="669676" waitresource="KEY: 14:72057594529382400 (bf00f1ccfd51)" waittime="4734" ownerId="484691527" transactionname="ius" lasttranstarted="2010-09-30T10:03:22.080" XDES="0xa50f54c0" lockMode="X" schedulerid="1" kpid="10288" status="suspended" spid="65" sbid="0" ecid="0" priority="0" transcount="4" lastbatchstarted="2010-09-30T10:03:22.080" lastbatchcompleted="2010-09-30T10:03:22.080" clientapp="CBS" hostname="DAWEBP100" hostpid="8976" loginname="DMZ\xProdCBS" isolationlevel="read committed (2)" xactid="484691527" currentdb="14" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"> <executionStack> <frame procname="TsiCbs.dbo.tsi_sp_object_insert" line="28" stmtstart="1170" stmtend="1358" sqlhandle="0x03000e00f578a27585a8a500169c00000100000000000000"> UPDATE tsi_t_objects SET NRight = NRight + 2 WHERE NRight >= @parentRight </frame> <frame procname="TsiCbs.dbo.tsi_tg_object_insert" line="17" stmtstart="760" stmtend="826" sqlhandle="0x03000e00e823327aa6b3a500169c00000000000000000000"> EXEC tsi_sp_object_insert @id </frame> <frame procname="TsiCbs.dbo.tsi_sp_insert_update_object" line="21" stmtstart="862" stmtend="1350" sqlhandle="0x03000e002e9d967693a8a500169c00000100000000000000"> INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk, SortOrder, [Guid], ExternalId) VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId) </frame> <frame procname="TsiCbs.dbo.x_sp_insert_update_person" line="39" stmtstart="1988" stmtend="2244" sqlhandle="0x03000e00f704cd06c7572001b79c00000100000000000000"> EXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId, @modifiedById, @sortOrder, @externalId </frame> <frame procname="TsiCbs.dbo.x_sp_insert_update_user" line="59" stmtstart="3232" stmtend="3934" sqlhandle="0x03000e001646bc1cd7572001b79c00000100000000000000"> EXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId, @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2, @middleInitial, @middleName, @phoneNumber1, @phoneType1Id, @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id, @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId </frame> </executionStack> <inputbuf> Proc [Database Id = 14 Object Id = 482100758] </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594529382400" dbid="14" objectname="TsiCbs.dbo.tsi_t_objects" indexname="PK_tsi_tLoginHierarchy" id="lock96828e80" mode="X" associatedObjectId="72057594529382400"> <owner-list> <owner id="processbbf048" mode="X"/> </owner-list> <waiter-list> <waiter id="processbbeef8" mode="S" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594529382400" dbid="14" objectname="TsiCbs.dbo.tsi_t_objects" indexname="PK_tsi_tLoginHierarchy" id="locka5e72600" mode="U" associatedObjectId="72057594529382400"> <owner-list> <owner id="processbbeef8" mode="S"/> </owner-list> <waiter-list> <waiter id="processbbf048" mode="X" requestType="convert"/> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list>
October 10, 2010 at 12:23 pm
Amit,
Here is my freshly updated code to show what is going on with this deadlock. (For any keeping track, this is now my most recent deadlock shredding code.)
-- see http://msdn.microsoft.com/en-us/library/ms188246.aspx
-- (MS BOL Analyzing Deadlocks with SQL Server Profiler)
-- see http://msdn.microsoft.com/en-us/library/ms175519.aspx
-- (MS BOL Lock Modes)
-- Shred XML Deadlock Graphs, showing in tabular format as much information as possible.
-- Insert the XML Deadlock Graph into the @deadlock table.
-- Author: Wayne Sheffield
-- Version: 2
-- Modification History:
-- 10/10/2010 - Added individual items in the Execution Stack node.
-- - Converted from using an XML variable to a table variable with an XML variable
-- - to allow seeing multiple deadlocks simultaneously.
declare @deadlock table (DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED, DeadlockGraph XML);
INSERT INTO @deadlock VALUES ('');
-- insert the deadlock XML in the above line! Duplicate as necessary for additional graphs.
WITH CTE AS
(
SELECT DeadlockID,
DeadlockGraph
FROM @deadlock
)
, Process AS
(
-- get the data from the process node
SELECT CTE.DeadlockID,
--[DeadlockTime]=
CTE.[DeadlockGraph],
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = Deadlock.Process.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)'),
[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'),
[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)'),
[ProcessID] = Deadlock.Process.value('@id','varchar(50)'),
[SPID] = Deadlock.Process.value('@spid','int'), -- server process id
[SBID] = Deadlock.Process.value('@sbid','int'), -- server batch id
[ECID] = Deadlock.Process.value('@ecid','int'), -- thread id of given SPID (0 is always the parent)
[IsolationLevel] = Deadlock.Process.value('@isolationlevel','varchar(200)'),
[WaitResource] = Deadlock.Process.value('@waitresource','varchar(200)'),
[LogUsed] = Deadlock.Process.value('@logused','int')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
)
, ExecutionStack AS
(
-- get the data from the executionStack node
SELECT CTE.DeadlockID,
ProcessID = Execution.Stack.value('../../@id','varchar(50)'),
Code = Execution.Stack.value('.', 'varchar(1000)'),
ProcName = Execution.Stack.value('@procname', 'sysname'),
Line = Execution.Stack.value('@line','int'),
RN = row_number() OVER (PARTITION BY CTE.DeadlockID, Execution.Stack.value('../../@id','varchar(50)') ORDER BY (SELECT 1))
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process/executionStack/frame') as Execution(Stack)
)
--SELECT * FROM ExecutionStack
, PageLock AS
(
-- get the data from the pagelock node
SELECT DeadlockID,
ObjectName = PageLock.Process.value('../../@objectname', 'varchar(200)'),
ProcessID = PageLock.Process.value('@id', 'varchar(200)'),
LockType = 'Page',
LockMode = PageLock.Process.value('../../@mode','varchar(3)')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/pagelock/owner-list/owner') AS PageLock(Process)
)
,KeyLock AS
(
-- get the data from the keylock node
SELECT DeadlockID,
ObjectName = KeyLock.Process.value('../../@objectname', 'varchar(200)') + '.' +
KeyLock.Process.value('../../@indexname', 'varchar(200)'), -- get the index name also
ProcessID = KeyLock.Process.value('@id', 'varchar(200)'),
LockType = 'Key',
LockMode = KeyLock.Process.value('../../@mode','varchar(3)')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/keylock/owner-list/owner') AS KeyLock(Process)
)
, RidLock AS
(
-- get the data from the ridlock node
SELECT DeadlockID,
ObjectName = RIDLock.Process.value('../../@objectname', 'varchar(200)'),
ProcessID = RIDLock.Process.value('@id', 'varchar(200)'),
LockType = 'RID',
LockMode = RIDLock.Process.value('../../@mode','varchar(3)')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/ridlock/owner-list/owner') AS RIDLock(Process)
)
, ObjectLock AS
(
-- get the data from the objectlock node
SELECT DeadlockID,
ObjectName = ObjectLock.Process.value('../../@objectname', 'varchar(200)'),
ProcessID = ObjectLock.Process.value('@id', 'varchar(200)'),
LockType = 'Object',
LockMode = ObjectLock.Process.value('../../@mode','varchar(3)')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/objectlock/owner-list/owner') AS ObjectLock(Process)
)
-- combine all the data together, and display in
SELECT Process.[DeadlockID],
Process.[SPID],
Process.[SBID],
Process.[ECID],
Process.[DeadlockGraph], -- include the graph - can click and open in separate window
Process.[ProcessID],
Process.[Victim],
[LockedType] = coalesce(PageLock.LockType, KeyLock.LockType, RIDLock.LockType, ObjectLock.LockType),
[LockMode] = coalesce(PageLock.LockMode, KeyLock.LockMode, RIDLock.LockMode, ObjectLock.LockMode),
[LockedObject] = coalesce(PageLock.ObjectName, KeyLock.ObjectName, RIDLock.ObjectName, ObjectLock.ObjectName),
[Procedure #] = es.RN,
es.[ProcName],
es.[Line],
es.[Code],
Process.[ClientApp],
Process.[HostName],
Process.[LoginName],
Process.[TransactionTime],
Process.BatchStarted,
Process.BatchCompleted,
Process.[InputBuffer],
Process.[IsolationLevel],
Process.WaitResource,
Process.LogUsed
FROM Process
JOIN ExecutionStack es
ON es.ProcessID = Process.ProcessID
AND es.DeadlockID = Process.DeadlockID
LEFT JOIN PageLock
ON PageLock.ProcessID = Process.ProcessID
AND PageLock.DeadlockID = Process.DeadlockID
LEFT JOIN KeyLock
ON KeyLock.ProcessID = Process.ProcessID
AND KeyLock.DeadlockID = Process.DeadlockID
LEFT JOIN RIDLock
ON RIDLock.ProcessID = Process.ProcessID
AND RIDLock.DeadlockID = Process.DeadlockID
LEFT JOIN ObjectLock
ON ObjectLock.ProcessID = Process.ProcessID
AND ObjectLock.DeadlockID = Process.DeadlockID
ORDER BY Process.DeadlockID,
Process.victim DESC, -- show the victim first
Process.ProcessID,
Process.ECID, -- show in thread order by SPID
es.RN; -- execution stack order
In running this against the two deadlocks, I first notice that they are related. When the first deadlock was forcibly ended (SQL choose a victim), the "winning" process was then immediately involved in a deadlock with a third process.
The process that was "winner" in both deadlocks was inserting a record into the TsiCbs.dbo.tsi_t_objects table. The first deadlock was when inserting the row into the table, the second deadlock was when updating the PK on that table (presumably the same record). It also appears that there is an insert trigger on the table - hence why there are 4 subsequent statements in the execution stack after the update for this process.
The "victim" of the deadlocks were each running a select statement. They both appear to be from a view (I'm assuming that the "v_" in the following object names indicates a view:
1. tsi_v_secured (which is also being joined to a function: tsi_f_get_object_permissions)
2. x_v_users
Since neither of these views indicate the object that is the source of the deadlock (TsiCbs.dbo.tsi_t_objects), it looks like they are involved in a JOIN condition to this table.
So, IMO, the things that you need to look at are:
1. The views - look at the actual execution plan (NOT the ESTIMATED plan), and ensure that they are as optimized as they can be (for the queries that I see, there should be NO scan operators).
2. The function - again, ensure that it is as optimized as it can be. Note that joining to a function may result in the overall query now being reduced to scans, and can be even worse than using a cursor.
3. The trigger - again, ensure that it is optimized.
As Craig as stated, I see nothing in the procedure that you are running that is causing the issue. So, IMO, it will be at least one of the above issues.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 10, 2010 at 12:45 pm
Brilliant stuff, analyzing it now.
Cheers.
October 10, 2010 at 1:34 pm
Craig Farrell (10/6/2010)
WayneS (10/6/2010)
Craig Farrell (10/6/2010)
Answer: No and no.This code, as is, will not deadlock independently. There's no transaction calls.
What is the serverwide setting for isolation?
Craig, did you consider the effect of parallelism on the deadlocks? I can see this potentially being an issue. Again, the deadlock graphs will help show what's really going on.
Hm, I've seen parallelism cause timeouts, but I've never seen it cause a deadlock without an explicit two part transaction. You wouldn't happen to have some research links handy, would you?
Craig,
Just as a followup on this... if you look at the execution stack for this deadlock, you will see these two commands being run by the same process: UPDATE tsi_t_objects SET NRight = NRight + 2 WHERE NRight >= @parentRight
INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk, SortOrder, [Guid], ExternalId)
VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
Obviously, attempting an insert into the same table that you're updating could very well cause a deadlock. (However, since these are being run under the same thread (ECID), then parallelism isn't a factor here.) These statements could have been spawned off into separate processes.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 10, 2010 at 1:35 pm
Amit Pandey DeBugSQL (10/10/2010)
Brilliant stuff, analyzing it now.Cheers.
Thanks.
Analyzing the code, or the results of it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 11, 2010 at 7:52 am
Here are my two goto resouces for deadlock troubleshooting:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Note there are 2 additional parts to this blog series.
BTW, anything out there by bart duncan is worth studying!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply