DeadLock

  • 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>

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Brilliant stuff, analyzing it now.

    Cheers.

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

    http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

    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