DeadLock

  • Hi All,

    I am trying to fine tune an application. One of the issues I find with the application is deadlocks.

    There have been six different instances of deadlocks in the last one week.

    The Deadlock trace graphs shows the same object ID on the node2 which points to the below Stored proc.

    My question is that the store procedure contains an outer Begin-end which can be removed (confirmed with the developers of the application). will it help in minimizing blocking if I remove the outer Begin-End block.

    BEGIN -------- This one if removed, will it help

    SET NOCOUNT ON;

    -----------------------------------------------------------------------------------

    CREATE PROCEDURE [sampleProc]

    @IDD AS INT

    AS

    DECLARE @var1 AS INT

    DECLARE @var2 AS INT

    DECLARE @var3 AS INT

    DECLARE @var4 AS INT

    BEGIN -------- This one if removed, will it help

    SET NOCOUNT ON;

    CREATE TABLE #temp1 (IDD INT)

    SELECT @var1 = PFK FROM TObjects WHERE IDD = @IDD

    SELECT @var4 = COUNT(IDD) FROM TObjects

    IF (@var4 = 1)

    BEGIN

    UPDATE TObjects

    SET NLeft = 1, NRight = 2

    END

    ELSE

    BEGIN

    SELECT @var2 = NLeft, @var3 = NRight

    FROM TObjects WHERE IDD = @var1

    UPDATE TObjects

    SET NRight = NRight + 2

    WHERE NRight >= @var3

    UPDATE TObjects

    SET NLeft = NLeft + 2

    WHERE NLeft > @var3

    UPDATE TObjects

    SET NLeft = @var3, NRight = @var3 + 1

    WHERE IDD = @IDD

    END

    DROP TABLE #temp1

    END

    -----------------------------------------------------------------------------------

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

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • thanks for the early reply

    read committed is the server level isolation level.

  • This query isn't the problem child, but it could be a contributor if default is READ COMMITTED.

    EDIT: Whoops. Can you also find out if your READ_COMMITTED_SNAPSHOT is set to on/off please?

    Run this, see if you get any hits:

    select

    o.name

    from

    sysobjects o

    join syscomments c

    on o.id = c.id

    where

    c.text like '%BEGIN TRAN%'

    and o.type IN ( 'p', 't')


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Nope Snapshot is not turned on and turning it is not an available option (I have already recommended this).

    Yes I ran the query given by you and it gives me as many as 37 records.

    Please let me know if you need any further info.

  • Amit Pandey DeBugSQL (10/6/2010)


    Nope Snapshot is not turned on and turning it is not an available option (I have already recommended this).

    Yes I ran the query given by you and it gives me as many as 37 records.

    Please let me know if you need any further info.

    One of these queries is your likely culprit, then, in the actual deadlock cause. Deadlocks in general (it seems you're relatively aware, but to make sure) are caused by two transactions fighting over the same resources. If you don't have multi-statement transactions, it's nearly impossible to get a deadlock. Timeouts waiting for lock, sure... but not deadlocks.

    Look over your deadlock statistics again and check to see if one of those 37 are listed.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Amit Pandey DeBugSQL (10/6/2010)


    The Deadlock trace graphs shows the same object ID on the node2 which points to the below Stored proc.

    I have a neat script that shreds a deadlock graph apart to show what all is happening. Can you post your deadlock graphs?

    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

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

    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

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

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

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

    Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.

    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

  • WayneS (10/6/2010)


    Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.

    Well, in that case, Amit, try a MAXDOP 1 and see if that helps your deadlocking problems.

    Thanks Wayne, learned something new today. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (10/6/2010)


    WayneS (10/6/2010)


    Research links, no. But I've seen deadlocks caused by parallelism before myself... so self-research - yes.

    Well, in that case, Amit, try a MAXDOP 1 and see if that helps your deadlocking problems.

    Thanks Wayne, learned something new today. 🙂

    Before you do that... can we see the deadlock graphs to see if this is a factor.

    (What I saw was an update that was updating data and indexes in separate threads... and they deadlocked on each other.)

    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

  • Thanks Craig and Wayne. Once I am on that network I will forward the details of the deadlock graph 🙂

  • Sorry for the delay in sending the information friends. I have to be on the network on this client which I am on weekends only.

    Here is the deadlock graph information.

    1st Graph:

    20/9/2010 3:21:14spid4sDeadlock encountered .... Printing deadlock information

    20/9/2010 3:21:14spid4sWait-for graph

    20/9/2010 3:21:14spid4sNULL

    20/9/2010 3:21:14spid4sNode:1

    20/9/2010 3:21:14spid4sPAGE: 14:1:195362 CleanCnt:3 Mode:SIU Flags: 0x2

    20/9/2010 3:21:14spid4s Grant List 0:

    20/9/2010 3:21:14spid4s Grant List 1:

    20/9/2010 3:21:14spid4s Owner:0x00000000A6981980 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:64 ECID:0 XactLockInfo: 0x00000000802E2590

    20/9/2010 3:21:14spid4s SPID: 64 ECID: 0 Statement Type: SELECT Line #: 9

    20/9/2010 3:21:14spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 1369771937]

    20/9/2010 3:21:14spid4s Requested By:

    20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000012D038370 Mode: IX SPID:51 BatchID:0 ECID:0 TaskProxy:(0x0000000091DA0598) Value:0xf4534900 Cost:(0/7208)

    20/9/2010 3:21:14spid4sNULL

    20/9/2010 3:21:14spid4sNode:2

    20/9/2010 3:21:14spid4sPAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2

    20/9/2010 3:21:14spid4s Grant List 0:

    20/9/2010 3:21:14spid4s Owner:0x00000000D43DF540 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0 XactLockInfo: 0x000000012D0383A8

    20/9/2010 3:21:14spid4s SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 28

    20/9/2010 3:21:14spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]

    20/9/2010 3:21:14spid4s Requested By:

    20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000AB486598) Value:0xd8ae7c40 Cost:(0/0)

    20/9/2010 3:21:14spid4sNULL

    20/9/2010 3:21:14spid4sVictim Resource Owner:

    20/9/2010 3:21:14spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000AB486598) Value:0xd8ae7c40 Cost:(0/0)

    20/9/2010 3:21:14spid18sdeadlock-list

    20/9/2010 3:21:14spid18s deadlock victim=processc25c18

    20/9/2010 3:21:14spid18s process-list

    20/9/2010 3:21:14spid18s process id=processbbfc18 taskpriority=0 logused=7208 waitresource=PAGE: 14:1:195362 waittime=1765 ownerId=449682462 transactionname=ius lasttranstarted=2010-09-20T03:21:12.557 XDES=0x12d038370 lockMode=IX schedulerid=1 kpid=39992 status=suspended spid=51 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-20T03:21:12.557 lastbatchcompleted=2010-09-20T03:21:12.557 clientapp=CBS hostname=DAWEBP100 hostpid=13508 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=449682462 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    20/9/2010 3:21:14spid18s executionStack

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000

    20/9/2010 3:21:14spid18sUPDATE tsi_t_objects

    20/9/2010 3:21:14spid18s SET NRight = NRight + 2

    20/9/2010 3:21:14spid18s WHERE NRight >= @parentRight

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000

    20/9/2010 3:21:14spid18sEXEC tsi_sp_object_insert @id

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000

    20/9/2010 3:21:14spid18sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,

    20/9/2010 3:21:14spid18s SortOrder, [Guid], ExternalId)

    20/9/2010 3:21:14spid18s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000

    20/9/2010 3:21:14spid18sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,

    20/9/2010 3:21:14spid18s @modifiedById, @sortOrder, @externalId

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000

    20/9/2010 3:21:14spid18sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,

    20/9/2010 3:21:14spid18s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,

    20/9/2010 3:21:14spid18s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,

    20/9/2010 3:21:14spid18s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,

    20/9/2010 3:21:14spid18s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId

    20/9/2010 3:21:14spid18s inputbuf

    20/9/2010 3:21:14spid18sProc [Database Id = 14 Object Id = 482100758]

    20/9/2010 3:21:14spid18s process id=processc25c18 taskpriority=0 logused=0 waitresource=PAGE: 14:1:195748 waittime=1812 ownerId=449682251 transactionname=SELECT lasttranstarted=2010-09-20T03:21:12.193 XDES=0x802e2558 lockMode=S schedulerid=2 kpid=41360 status=suspended spid=64 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-20T03:21:12.193 lastbatchcompleted=2010-09-20T03:21:12.193 clientapp=CBS hostname=DAWEBP100 hostpid=13508 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=449682251 currentdb=14 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056

    20/9/2010 3:21:14spid18s executionStack

    20/9/2010 3:21:14spid18s frame procname=TsiCbs.dbo.bkd_sp_get_unit_by_id line=9 stmtstart=222 stmtend=1438 sqlhandle=0x03000e00a10fa5511c7e1f01679d00000100000000000000

    20/9/2010 3:21:14spid18sSELECT

    20/9/2010 3:21:14spid18s u.Id,

    20/9/2010 3:21:14spid18s u.[Name],

    20/9/2010 3:21:14spid18s u.Code,

    20/9/2010 3:21:14spid18s u.Active,

    20/9/2010 3:21:14spid18s u.Notes,

    20/9/2010 3:21:14spid18s u.PodId,

    20/9/2010 3:21:14spid18s u.Template_Fk,

    20/9/2010 3:21:14spid18s u.Model_Fk,

    20/9/2010 3:21:14spid18s u.LastTran,

    20/9/2010 3:21:14spid18s u.LastH,

    20/9/2010 3:21:14spid18s u.LastT,

    20/9/2010 3:21:14spid18s u.LastT,

    20/9/2010 3:21:14spid18s u.Street,

    20/9/2010 3:21:14spid18s u.City,

    20/9/2010 3:21:14spid18s u.StateOrProvince,

    20/9/2010 3:21:14spid18s u.ZipOrPostalCode,

    20/9/2010 3:21:14spid18s u.Country,

    20/9/2010 3:21:14spid18s sc.Id AS SiteCodeId,

    20/9/2010 3:21:14spid18s sc.[Name] AS SiteCodeName,

    20/9/2010 3:21:14spid18s sc.[Description] AS SiteCodeDescription,

    20/9/2010 3:21:14spid18s sc.Entity_Fk

    20/9/2010 3:21:14spid18s FROM

    20/9/2010 3:21:14spid18s bkd_t_unit u INNER JOIN

    20/9/2010 3:21:14spid18s bkd_t_sitecodes sc ON sc.Id = u.SiteCode_Fk INNER JOIN

    20/9/2010 3:21:14spid18s tsi_f_get_object_permissions(@userId) op ON op.OwnedId = sc.Entity_Fk

    20/9/2010 3:21:14spid18s WHERE

    20/9/2010 3:21:14spid18s u.Id =@id AND

    20/9/2010 3:21:14spid18s op.PermissionId = 7;

    20/9/2010 3:21:14spid18s inputbuf

    20/9/2010 3:21:14spid18sProc [Database Id = 14 Object Id = 1369771937]

    20/9/2010 3:21:14spid18s resource-list

    20/9/2010 3:21:14spid18s pagelock fileid=1 pageid=195362 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock99c65d00 mode=SIU associatedObjectId=72057594523287552

    20/9/2010 3:21:14spid18s owner-list

    20/9/2010 3:21:14spid18s owner id=processc25c18 mode=S

    20/9/2010 3:21:14spid18s waiter-list

    20/9/2010 3:21:14spid18s waiter id=processbbfc18 mode=IX requestType=convert

    20/9/2010 3:21:14spid18s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=locka6b64880 mode=IX associatedObjectId=72057594523287552

    20/9/2010 3:21:14spid18s owner-list

    20/9/2010 3:21:14spid18s owner id=processbbfc18 mode=IX

    20/9/2010 3:21:14spid18s waiter-list

    20/9/2010 3:21:14spid18s waiter id=processc25c18 mode=S requestType=wait

    2nd One

    15/9/2010 11:16:53spid4sDeadlock encountered .... Printing deadlock information

    15/9/2010 11:16:53spid4sWait-for graph

    15/9/2010 11:16:53spid4sNULL

    15/9/2010 11:16:53spid4sNode:1

    15/9/2010 11:16:53spid4sKEY: 14:72057594517192704 (f500d97e6a8d) CleanCnt:3 Mode:U Flags: 0x0

    15/9/2010 11:16:53spid4s Grant List 0:

    15/9/2010 11:16:53spid4s Owner:0x000000011A163700 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:67 ECID:0 XactLockInfo: 0x00000000D08EE200

    15/9/2010 11:16:53spid4s SPID: 67 ECID: 0 Statement Type: SELECT Line #: 9

    15/9/2010 11:16:53spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 578101100]

    15/9/2010 11:16:53spid4s Requested By:

    15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000011B721850 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy:(0x000000013256C598) Value:0x91f43380 Cost:(0/266404)

    15/9/2010 11:16:53spid4sNULL

    15/9/2010 11:16:53spid4sNode:2

    15/9/2010 11:16:53spid4sKEY: 14:72057594517192704 (aa00a25ad511) CleanCnt:2 Mode:X Flags: 0x0

    15/9/2010 11:16:53spid4s Grant List 0:

    15/9/2010 11:16:53spid4s Owner:0x00000000EF27A5C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x000000011B721888

    15/9/2010 11:16:53spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 28

    15/9/2010 11:16:53spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]

    15/9/2010 11:16:53spid4s Requested By:

    15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy:(0x00000000FF174598) Value:0x96e27680 Cost:(0/0)

    15/9/2010 11:16:53spid4sNULL

    15/9/2010 11:16:53spid4sVictim Resource Owner:

    15/9/2010 11:16:53spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy:(0x00000000FF174598) Value:0x96e27680 Cost:(0/0)

    15/9/2010 11:16:53spid19sdeadlock-list

    15/9/2010 11:16:53spid19s deadlock victim=processc25ac8

    15/9/2010 11:16:53spid19s process-list

    15/9/2010 11:16:53spid19s process id=processbbf438 taskpriority=0 logused=266404 waitresource=KEY: 14:72057594517192704 (f500d97e6a8d) waittime=1562 ownerId=434962837 transactionname=ius lasttranstarted=2010-09-15T11:16:51.390 XDES=0x11b721850 lockMode=X schedulerid=1 kpid=40560 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-15T11:16:51.390 lastbatchcompleted=2010-09-15T11:16:51.390 clientapp=CBS hostname=DAWEBP100 hostpid=228 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=434962837 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    15/9/2010 11:16:53spid19s executionStack

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000

    15/9/2010 11:16:53spid19sUPDATE tsi_t_objects

    15/9/2010 11:16:53spid19s SET NRight = NRight + 2

    15/9/2010 11:16:53spid19s WHERE NRight >= @parentRight

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000

    15/9/2010 11:16:53spid19sEXEC tsi_sp_object_insert @id

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000

    15/9/2010 11:16:53spid19sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,

    15/9/2010 11:16:53spid19s SortOrder, [Guid], ExternalId)

    15/9/2010 11:16:53spid19s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000

    15/9/2010 11:16:53spid19sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,

    15/9/2010 11:16:53spid19s @modifiedById, @sortOrder, @externalId

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000

    15/9/2010 11:16:53spid19sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,

    15/9/2010 11:16:53spid19s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,

    15/9/2010 11:16:53spid19s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,

    15/9/2010 11:16:53spid19s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,

    15/9/2010 11:16:53spid19s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId

    15/9/2010 11:16:53spid19s inputbuf

    15/9/2010 11:16:53spid19sProc [Database Id = 14 Object Id = 482100758]

    15/9/2010 11:16:53spid19s process id=processc25ac8 taskpriority=0 logused=0 waitresource=KEY: 14:72057594517192704 (aa00a25ad511) waittime=1625 ownerId=434962892 transactionname=SELECT lasttranstarted=2010-09-15T11:16:51.723 XDES=0xd08ee1c8 lockMode=S schedulerid=2 kpid=40368 status=suspended spid=67 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-15T11:16:51.723 lastbatchcompleted=2010-09-15T11:16:51.723 clientapp=Routing hostname=DAWEBP100 hostpid=7292 loginname=DMZ\xProdWebServices isolationlevel=read committed (2) xactid=434962892 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    15/9/2010 11:16:53spid19s executionStack

    15/9/2010 11:16:53spid19s frame procname=TsiCbs.dbo.x_sp_get_user_by_id line=9 stmtstart=316 stmtend=602 sqlhandle=0x03000e006c1f75228597f300fc9c00000100000000000000

    15/9/2010 11:16:53spid19sSELECT u.* FROM x_v_users u

    15/9/2010 11:16:53spid19s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = u.Id

    15/9/2010 11:16:53spid19s WHERE Id = @id AND PermissionId = 7

    15/9/2010 11:16:53spid19s inputbuf

    15/9/2010 11:16:53spid19sProc [Database Id = 14 Object Id = 578101100]

    15/9/2010 11:16:53spid19s resource-list

    15/9/2010 11:16:53spid19s keylock hobtid=72057594517192704 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects indexname=PK_tsi_tLoginHierarchy id=lockd7dc9780 mode=X associatedObjectId=72057594517192704

    15/9/2010 11:16:53spid19s owner-list

    15/9/2010 11:16:53spid19s owner id=processbbf438 mode=X

    15/9/2010 11:16:53spid19s waiter-list

    15/9/2010 11:16:53spid19s waiter id=processc25ac8 mode=S requestType=wait

    15/9/2010 11:16:53spid19s keylock hobtid=72057594517192704 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects indexname=PK_tsi_tLoginHierarchy id=lock1297d3080 mode=U associatedObjectId=72057594517192704

    15/9/2010 11:16:53spid19s owner-list

    15/9/2010 11:16:53spid19s owner id=processc25ac8 mode=S

    15/9/2010 11:16:53spid19s waiter-list

    15/9/2010 11:16:53spid19s waiter id=processbbf438 mode=X requestType=convert

    3rd one

    22/9/2010 3:34:27spid4sDeadlock encountered .... Printing deadlock information

    22/9/2010 3:34:27spid4sWait-for graph

    22/9/2010 3:34:27spid4sNULL

    22/9/2010 3:34:27spid4sNode:1

    22/9/2010 3:34:27spid4sPAGE: 14:1:195360 CleanCnt:3 Mode:SIU Flags: 0x2

    22/9/2010 3:34:27spid4s Grant List 1:

    22/9/2010 3:34:27spid4s Owner:0x00000000D1F12800 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:66 ECID:0 XactLockInfo: 0x00000000A67437B0

    22/9/2010 3:34:27spid4s SPID: 66 ECID: 0 Statement Type: SELECT Line #: 8

    22/9/2010 3:34:27spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 802101898]

    22/9/2010 3:34:27spid4s Requested By:

    22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A88BB890 Mode: IX SPID:69 BatchID:0 ECID:0 TaskProxy:(0x00000000B16D2598) Value:0xa38d4340 Cost:(0/520)

    22/9/2010 3:34:27spid4sNULL

    22/9/2010 3:34:27spid4sNode:2

    22/9/2010 3:34:27spid4sPAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2

    22/9/2010 3:34:27spid4s Grant List 1:

    22/9/2010 3:34:27spid4s Owner:0x0000000099644940 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x00000000A88BB8C8

    22/9/2010 3:34:27spid4s SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 28

    22/9/2010 3:34:27spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]

    22/9/2010 3:34:27spid4s Requested By:

    22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy:(0x000000009C788598) Value:0x98f30800 Cost:(0/0)

    22/9/2010 3:34:27spid4sNULL

    22/9/2010 3:34:27spid4sVictim Resource Owner:

    22/9/2010 3:34:27spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy:(0x000000009C788598) Value:0x98f30800 Cost:(0/0)

    22/9/2010 3:34:27spid13sdeadlock-list

    22/9/2010 3:34:27spid13s deadlock victim=processc256d8

    22/9/2010 3:34:27spid13s process-list

    22/9/2010 3:34:27spid13s process id=processc256d8 taskpriority=0 logused=0 waitresource=PAGE: 14:1:195748 waittime=2015 ownerId=456939113 transactionname=SELECT lasttranstarted=2010-09-22T03:34:24.867 XDES=0xa6743778 lockMode=S schedulerid=2 kpid=41552 status=suspended spid=66 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-09-22T03:34:24.867 lastbatchcompleted=2010-09-22T03:34:24.867 clientapp=CBS hostname=DAWEBP100 hostpid=6700 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=456939113 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    22/9/2010 3:34:27spid13s executionStack

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_list_securing_groups line=8 stmtstart=366 stmtend=706 sqlhandle=0x03000e008a1acf2fafada500169c00000100000000000000

    22/9/2010 3:34:27spid13sSELECT * FROM tsi_v_secured s

    22/9/2010 3:34:27spid13s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = s.SecurityGroupId

    22/9/2010 3:34:27spid13s WHERE SecuredId = @securedId AND PermissionId =

    22/9/2010 3:34:27spid13s inputbuf

    22/9/2010 3:34:27spid13sProc [Database Id = 14 Object Id = 802101898]

    22/9/2010 3:34:27spid13s process id=processc25978 taskpriority=0 logused=520 waitresource=PAGE: 14:1:195360 waittime=2015 ownerId=456939087 transactionname=ius lasttranstarted=2010-09-22T03:34:24.707 XDES=0xa88bb890 lockMode=IX schedulerid=2 kpid=41020 status=suspended spid=69 sbid=0 ecid=0 priority=0 transcount=4 lastbatchstarted=2010-09-22T03:34:24.690 lastbatchcompleted=2010-09-22T03:34:24.690 clientapp=CBS hostname=DAWEBP100 hostpid=6700 loginname=DMZ\xProdCBS isolationlevel=read committed (2) xactid=456939087 currentdb=14 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    22/9/2010 3:34:27spid13s executionStack

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000

    22/9/2010 3:34:27spid13sUPDATE tsi_t_objects

    22/9/2010 3:34:27spid13s SET NRight = NRight + 2

    22/9/2010 3:34:27spid13s WHERE NRight >= @parentRight

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000

    22/9/2010 3:34:27spid13sEXEC tsi_sp_object_insert @id

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000

    22/9/2010 3:34:27spid13sINSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,

    22/9/2010 3:34:27spid13s SortOrder, [Guid], ExternalId)

    22/9/2010 3:34:27spid13s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000

    22/9/2010 3:34:27spid13sEXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,

    22/9/2010 3:34:27spid13s @modifiedById, @sortOrder, @externalId

    22/9/2010 3:34:27spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000

    22/9/2010 3:34:27spid13sEXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,

    22/9/2010 3:34:27spid13s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,

    22/9/2010 3:34:27spid13s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,

    22/9/2010 3:34:27spid13s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,

    22/9/2010 3:34:27spid13s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId

    22/9/2010 3:34:27spid13s inputbuf

    22/9/2010 3:34:27spid13sProc [Database Id = 14 Object Id = 482100758]

    22/9/2010 3:34:27spid13s resource-list

    22/9/2010 3:34:27spid13s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock9b48fa00 mode=IX associatedObjectId=72057594523287552

    22/9/2010 3:34:27spid13s owner-list

    22/9/2010 3:34:27spid13s owner id=processc25978 mode=IX

    22/9/2010 3:34:27spid13s waiter-list

    22/9/2010 3:34:27spid13s waiter id=processc256d8 mode=S requestType=wait

    22/9/2010 3:34:27spid13s pagelock fileid=1 pageid=195360 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lockf453bb00 mode=SIU associatedObjectId=72057594523287552

    22/9/2010 3:34:27spid13s owner-list

    22/9/2010 3:34:27spid13s owner id=processc256d8 mode=S

    22/9/2010 3:34:27spid13s waiter-list

    22/9/2010 3:34:27spid13s waiter id=processc25978 mode=IX requestType=convert

  • The deadlock graph will be in an XML format... do you have that available?

    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

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply