Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

DeadLock Expand / Collapse
Author
Message
Posted Wednesday, October 06, 2010 6:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1000073
Posted Wednesday, October 06, 2010 6:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1000078
Posted Thursday, October 07, 2010 4:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:13 AM
Points: 13, Visits: 292
Thanks Craig and Wayne. Once I am on that network I will forward the details of the deadlock graph
Post #1000261
Posted Saturday, October 09, 2010 3:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:13 AM
Points: 13, Visits: 292
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:14 spid4s Deadlock encountered .... Printing deadlock information
20/9/2010 3:21:14 spid4s Wait-for graph
20/9/2010 3:21:14 spid4s NULL
20/9/2010 3:21:14 spid4s Node:1
20/9/2010 3:21:14 spid4s PAGE: 14:1:195362 CleanCnt:3 Mode:SIU Flags: 0x2
20/9/2010 3:21:14 spid4s Grant List 0:
20/9/2010 3:21:14 spid4s Grant List 1:
20/9/2010 3:21:14 spid4s Owner:0x00000000A6981980 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:64 ECID:0 XactLockInfo: 0x00000000802E2590
20/9/2010 3:21:14 spid4s SPID: 64 ECID: 0 Statement Type: SELECT Line #: 9
20/9/2010 3:21:14 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 1369771937]
20/9/2010 3:21:14 spid4s Requested By:
20/9/2010 3:21:14 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000012D038370 Mode: IX SPID:51 BatchID:0 ECID:0 TaskProxy0x0000000091DA0598) Value:0xf4534900 Cost0/7208)
20/9/2010 3:21:14 spid4s NULL
20/9/2010 3:21:14 spid4s Node:2
20/9/2010 3:21:14 spid4s PAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2
20/9/2010 3:21:14 spid4s Grant List 0:
20/9/2010 3:21:14 spid4s Owner:0x00000000D43DF540 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0 XactLockInfo: 0x000000012D0383A8
20/9/2010 3:21:14 spid4s SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 28
20/9/2010 3:21:14 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
20/9/2010 3:21:14 spid4s Requested By:
20/9/2010 3:21:14 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy0x00000000AB486598) Value:0xd8ae7c40 Cost0/0)
20/9/2010 3:21:14 spid4s NULL
20/9/2010 3:21:14 spid4s Victim Resource Owner:
20/9/2010 3:21:14 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000802E2558 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy0x00000000AB486598) Value:0xd8ae7c40 Cost0/0)
20/9/2010 3:21:14 spid18s deadlock-list
20/9/2010 3:21:14 spid18s deadlock victim=processc25c18
20/9/2010 3:21:14 spid18s process-list
20/9/2010 3:21:14 spid18s 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:14 spid18s executionStack
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
20/9/2010 3:21:14 spid18s UPDATE tsi_t_objects
20/9/2010 3:21:14 spid18s SET NRight = NRight + 2
20/9/2010 3:21:14 spid18s WHERE NRight >= @parentRight
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
20/9/2010 3:21:14 spid18s EXEC tsi_sp_object_insert @id
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
20/9/2010 3:21:14 spid18s INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
20/9/2010 3:21:14 spid18s SortOrder, [Guid], ExternalId)
20/9/2010 3:21:14 spid18s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
20/9/2010 3:21:14 spid18s EXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
20/9/2010 3:21:14 spid18s @modifiedById, @sortOrder, @externalId
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
20/9/2010 3:21:14 spid18s EXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
20/9/2010 3:21:14 spid18s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
20/9/2010 3:21:14 spid18s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
20/9/2010 3:21:14 spid18s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
20/9/2010 3:21:14 spid18s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
20/9/2010 3:21:14 spid18s inputbuf
20/9/2010 3:21:14 spid18s Proc [Database Id = 14 Object Id = 482100758]
20/9/2010 3:21:14 spid18s 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:14 spid18s executionStack
20/9/2010 3:21:14 spid18s frame procname=TsiCbs.dbo.bkd_sp_get_unit_by_id line=9 stmtstart=222 stmtend=1438 sqlhandle=0x03000e00a10fa5511c7e1f01679d00000100000000000000
20/9/2010 3:21:14 spid18s SELECT
20/9/2010 3:21:14 spid18s u.Id,
20/9/2010 3:21:14 spid18s u.[Name],
20/9/2010 3:21:14 spid18s u.Code,
20/9/2010 3:21:14 spid18s u.Active,
20/9/2010 3:21:14 spid18s u.Notes,
20/9/2010 3:21:14 spid18s u.PodId,
20/9/2010 3:21:14 spid18s u.Template_Fk,
20/9/2010 3:21:14 spid18s u.Model_Fk,
20/9/2010 3:21:14 spid18s u.LastTran,
20/9/2010 3:21:14 spid18s u.LastH,
20/9/2010 3:21:14 spid18s u.LastT,
20/9/2010 3:21:14 spid18s u.LastT,
20/9/2010 3:21:14 spid18s u.Street,
20/9/2010 3:21:14 spid18s u.City,
20/9/2010 3:21:14 spid18s u.StateOrProvince,
20/9/2010 3:21:14 spid18s u.ZipOrPostalCode,
20/9/2010 3:21:14 spid18s u.Country,
20/9/2010 3:21:14 spid18s sc.Id AS SiteCodeId,
20/9/2010 3:21:14 spid18s sc.[Name] AS SiteCodeName,
20/9/2010 3:21:14 spid18s sc.[Description] AS SiteCodeDescription,
20/9/2010 3:21:14 spid18s sc.Entity_Fk
20/9/2010 3:21:14 spid18s FROM
20/9/2010 3:21:14 spid18s bkd_t_unit u INNER JOIN
20/9/2010 3:21:14 spid18s bkd_t_sitecodes sc ON sc.Id = u.SiteCode_Fk INNER JOIN
20/9/2010 3:21:14 spid18s tsi_f_get_object_permissions(@userId) op ON op.OwnedId = sc.Entity_Fk
20/9/2010 3:21:14 spid18s WHERE
20/9/2010 3:21:14 spid18s u.Id =@id AND
20/9/2010 3:21:14 spid18s op.PermissionId = 7;
20/9/2010 3:21:14 spid18s inputbuf
20/9/2010 3:21:14 spid18s Proc [Database Id = 14 Object Id = 1369771937]
20/9/2010 3:21:14 spid18s resource-list
20/9/2010 3:21:14 spid18s pagelock fileid=1 pageid=195362 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock99c65d00 mode=SIU associatedObjectId=72057594523287552
20/9/2010 3:21:14 spid18s owner-list
20/9/2010 3:21:14 spid18s owner id=processc25c18 mode=S
20/9/2010 3:21:14 spid18s waiter-list
20/9/2010 3:21:14 spid18s waiter id=processbbfc18 mode=IX requestType=convert
20/9/2010 3:21:14 spid18s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=locka6b64880 mode=IX associatedObjectId=72057594523287552
20/9/2010 3:21:14 spid18s owner-list
20/9/2010 3:21:14 spid18s owner id=processbbfc18 mode=IX
20/9/2010 3:21:14 spid18s waiter-list
20/9/2010 3:21:14 spid18s waiter id=processc25c18 mode=S requestType=wait






2nd One

15/9/2010 11:16:53 spid4s Deadlock encountered .... Printing deadlock information
15/9/2010 11:16:53 spid4s Wait-for graph
15/9/2010 11:16:53 spid4s NULL
15/9/2010 11:16:53 spid4s Node:1
15/9/2010 11:16:53 spid4s KEY: 14:72057594517192704 (f500d97e6a8d) CleanCnt:3 Mode:U Flags: 0x0
15/9/2010 11:16:53 spid4s Grant List 0:
15/9/2010 11:16:53 spid4s Owner:0x000000011A163700 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:67 ECID:0 XactLockInfo: 0x00000000D08EE200
15/9/2010 11:16:53 spid4s SPID: 67 ECID: 0 Statement Type: SELECT Line #: 9
15/9/2010 11:16:53 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 578101100]
15/9/2010 11:16:53 spid4s Requested By:
15/9/2010 11:16:53 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000011B721850 Mode: X SPID:60 BatchID:0 ECID:0 TaskProxy0x000000013256C598) Value:0x91f43380 Cost0/266404)
15/9/2010 11:16:53 spid4s NULL
15/9/2010 11:16:53 spid4s Node:2
15/9/2010 11:16:53 spid4s KEY: 14:72057594517192704 (aa00a25ad511) CleanCnt:2 Mode:X Flags: 0x0
15/9/2010 11:16:53 spid4s Grant List 0:
15/9/2010 11:16:53 spid4s Owner:0x00000000EF27A5C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x000000011B721888
15/9/2010 11:16:53 spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 28
15/9/2010 11:16:53 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
15/9/2010 11:16:53 spid4s Requested By:
15/9/2010 11:16:53 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy0x00000000FF174598) Value:0x96e27680 Cost0/0)
15/9/2010 11:16:53 spid4s NULL
15/9/2010 11:16:53 spid4s Victim Resource Owner:
15/9/2010 11:16:53 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000D08EE1C8 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy0x00000000FF174598) Value:0x96e27680 Cost0/0)
15/9/2010 11:16:53 spid19s deadlock-list
15/9/2010 11:16:53 spid19s deadlock victim=processc25ac8
15/9/2010 11:16:53 spid19s process-list
15/9/2010 11:16:53 spid19s 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:53 spid19s executionStack
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
15/9/2010 11:16:53 spid19s UPDATE tsi_t_objects
15/9/2010 11:16:53 spid19s SET NRight = NRight + 2
15/9/2010 11:16:53 spid19s WHERE NRight >= @parentRight
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
15/9/2010 11:16:53 spid19s EXEC tsi_sp_object_insert @id
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
15/9/2010 11:16:53 spid19s INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
15/9/2010 11:16:53 spid19s SortOrder, [Guid], ExternalId)
15/9/2010 11:16:53 spid19s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
15/9/2010 11:16:53 spid19s EXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
15/9/2010 11:16:53 spid19s @modifiedById, @sortOrder, @externalId
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
15/9/2010 11:16:53 spid19s EXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
15/9/2010 11:16:53 spid19s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
15/9/2010 11:16:53 spid19s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
15/9/2010 11:16:53 spid19s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
15/9/2010 11:16:53 spid19s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
15/9/2010 11:16:53 spid19s inputbuf
15/9/2010 11:16:53 spid19s Proc [Database Id = 14 Object Id = 482100758]
15/9/2010 11:16:53 spid19s 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:53 spid19s executionStack
15/9/2010 11:16:53 spid19s frame procname=TsiCbs.dbo.x_sp_get_user_by_id line=9 stmtstart=316 stmtend=602 sqlhandle=0x03000e006c1f75228597f300fc9c00000100000000000000
15/9/2010 11:16:53 spid19s SELECT u.* FROM x_v_users u
15/9/2010 11:16:53 spid19s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = u.Id
15/9/2010 11:16:53 spid19s WHERE Id = @id AND PermissionId = 7
15/9/2010 11:16:53 spid19s inputbuf
15/9/2010 11:16:53 spid19s Proc [Database Id = 14 Object Id = 578101100]
15/9/2010 11:16:53 spid19s resource-list
15/9/2010 11:16:53 spid19s 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:53 spid19s owner-list
15/9/2010 11:16:53 spid19s owner id=processbbf438 mode=X
15/9/2010 11:16:53 spid19s waiter-list
15/9/2010 11:16:53 spid19s waiter id=processc25ac8 mode=S requestType=wait
15/9/2010 11:16:53 spid19s 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:53 spid19s owner-list
15/9/2010 11:16:53 spid19s owner id=processc25ac8 mode=S
15/9/2010 11:16:53 spid19s waiter-list
15/9/2010 11:16:53 spid19s waiter id=processbbf438 mode=X requestType=convert


3rd one
22/9/2010 3:34:27 spid4s Deadlock encountered .... Printing deadlock information
22/9/2010 3:34:27 spid4s Wait-for graph
22/9/2010 3:34:27 spid4s NULL
22/9/2010 3:34:27 spid4s Node:1
22/9/2010 3:34:27 spid4s PAGE: 14:1:195360 CleanCnt:3 Mode:SIU Flags: 0x2
22/9/2010 3:34:27 spid4s Grant List 1:
22/9/2010 3:34:27 spid4s Owner:0x00000000D1F12800 Mode: S Flg:0x0 Ref:2 Life:00000000 SPID:66 ECID:0 XactLockInfo: 0x00000000A67437B0
22/9/2010 3:34:27 spid4s SPID: 66 ECID: 0 Statement Type: SELECT Line #: 8
22/9/2010 3:34:27 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 802101898]
22/9/2010 3:34:27 spid4s Requested By:
22/9/2010 3:34:27 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A88BB890 Mode: IX SPID:69 BatchID:0 ECID:0 TaskProxy0x00000000B16D2598) Value:0xa38d4340 Cost0/520)
22/9/2010 3:34:27 spid4s NULL
22/9/2010 3:34:27 spid4s Node:2
22/9/2010 3:34:27 spid4s PAGE: 14:1:195748 CleanCnt:2 Mode:IX Flags: 0x2
22/9/2010 3:34:27 spid4s Grant List 1:
22/9/2010 3:34:27 spid4s Owner:0x0000000099644940 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x00000000A88BB8C8
22/9/2010 3:34:27 spid4s SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 28
22/9/2010 3:34:27 spid4s Input Buf: RPC Event: Proc [Database Id = 14 Object Id = 482100758]
22/9/2010 3:34:27 spid4s Requested By:
22/9/2010 3:34:27 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy0x000000009C788598) Value:0x98f30800 Cost0/0)
22/9/2010 3:34:27 spid4s NULL
22/9/2010 3:34:27 spid4s Victim Resource Owner:
22/9/2010 3:34:27 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000A6743778 Mode: S SPID:66 BatchID:0 ECID:0 TaskProxy0x000000009C788598) Value:0x98f30800 Cost0/0)
22/9/2010 3:34:27 spid13s deadlock-list
22/9/2010 3:34:27 spid13s deadlock victim=processc256d8
22/9/2010 3:34:27 spid13s process-list
22/9/2010 3:34:27 spid13s 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:27 spid13s executionStack
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.tsi_sp_list_securing_groups line=8 stmtstart=366 stmtend=706 sqlhandle=0x03000e008a1acf2fafada500169c00000100000000000000
22/9/2010 3:34:27 spid13s SELECT * FROM tsi_v_secured s
22/9/2010 3:34:27 spid13s INNER JOIN tsi_f_get_object_permissions(@userId) op ON op.OwnedId = s.SecurityGroupId
22/9/2010 3:34:27 spid13s WHERE SecuredId = @securedId AND PermissionId =
22/9/2010 3:34:27 spid13s inputbuf
22/9/2010 3:34:27 spid13s Proc [Database Id = 14 Object Id = 802101898]
22/9/2010 3:34:27 spid13s 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:27 spid13s executionStack
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.tsi_sp_object_insert line=28 stmtstart=1170 stmtend=1358 sqlhandle=0x03000e00f578a27585a8a500169c00000100000000000000
22/9/2010 3:34:27 spid13s UPDATE tsi_t_objects
22/9/2010 3:34:27 spid13s SET NRight = NRight + 2
22/9/2010 3:34:27 spid13s WHERE NRight >= @parentRight
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.tsi_tg_object_insert line=17 stmtstart=760 stmtend=826 sqlhandle=0x03000e00e823327aa6b3a500169c00000000000000000000
22/9/2010 3:34:27 spid13s EXEC tsi_sp_object_insert @id
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.tsi_sp_insert_update_object line=21 stmtstart=862 stmtend=1350 sqlhandle=0x03000e002e9d967693a8a500169c00000100000000000000
22/9/2010 3:34:27 spid13s INSERT INTO tsi_t_objects(Parent_Fk, [Name], ObjectType_Fk, ModifiedDate, ModifiedBy_Fk,
22/9/2010 3:34:27 spid13s SortOrder, [Guid], ExternalId)
22/9/2010 3:34:27 spid13s VALUES(@parentId, @name, @objectTypeId, GETUTCDATE(), @modifiedById, @sortOrder, NEWID(), @externalId)
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_person line=39 stmtstart=1988 stmtend=2244 sqlhandle=0x03000e00f704cd06c7572001b79c00000100000000000000
22/9/2010 3:34:27 spid13s EXEC tsi_sp_insert_update_object @id OUTPUT, @parentId, @name, @objectTypeId,
22/9/2010 3:34:27 spid13s @modifiedById, @sortOrder, @externalId
22/9/2010 3:34:27 spid13s frame procname=TsiCbs.dbo.x_sp_insert_update_user line=59 stmtstart=3232 stmtend=3934 sqlhandle=0x03000e001646bc1cd7572001b79c00000100000000000000
22/9/2010 3:34:27 spid13s EXEC x_sp_insert_update_person @id OUTPUT, @parentId, @name, @objectTypeId,
22/9/2010 3:34:27 spid13s @modifiedById, @sortOrder, @firstName, @lastName, @email, @email2,
22/9/2010 3:34:27 spid13s @middleInitial, @middleName, @phoneNumber1, @phoneType1Id,
22/9/2010 3:34:27 spid13s @phoneNumber2, @phoneType2Id, @phoneNumber3, @phoneType3Id,
22/9/2010 3:34:27 spid13s @phoneNumber4, @phoneType4Id, @primaryPhoneNumber, @externalId
22/9/2010 3:34:27 spid13s inputbuf
22/9/2010 3:34:27 spid13s Proc [Database Id = 14 Object Id = 482100758]
22/9/2010 3:34:27 spid13s resource-list
22/9/2010 3:34:27 spid13s pagelock fileid=1 pageid=195748 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lock9b48fa00 mode=IX associatedObjectId=72057594523287552
22/9/2010 3:34:27 spid13s owner-list
22/9/2010 3:34:27 spid13s owner id=processc25978 mode=IX
22/9/2010 3:34:27 spid13s waiter-list
22/9/2010 3:34:27 spid13s waiter id=processc256d8 mode=S requestType=wait
22/9/2010 3:34:27 spid13s pagelock fileid=1 pageid=195360 dbid=14 objectname=TsiCbs.dbo.tsi_t_objects id=lockf453bb00 mode=SIU associatedObjectId=72057594523287552
22/9/2010 3:34:27 spid13s owner-list
22/9/2010 3:34:27 spid13s owner id=processc256d8 mode=S
22/9/2010 3:34:27 spid13s waiter-list
22/9/2010 3:34:27 spid13s waiter id=processc25978 mode=IX requestType=convert


Post #1001697
Posted Saturday, October 09, 2010 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
The deadlock graph will be in an XML format... do you have that available?

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001715
Posted Saturday, October 09, 2010 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:13 AM
Points: 13, Visits: 292
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>

Post #1001752
Posted Sunday, October 10, 2010 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001830
Posted Sunday, October 10, 2010 12:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:13 AM
Points: 13, Visits: 292
Brilliant stuff, analyzing it now.
Cheers.
Post #1001832
Posted Sunday, October 10, 2010 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001836
Posted Sunday, October 10, 2010 1:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1001837
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse