March 6, 2012 at 10:48 am
Hi friends,
Kindly help me out on this.....
been getting a lot of deadlocks on prod. server lately.....occurrence is like 20-25 every 2 hrs. Can any one help me isolate the issue here. Attaching the error log o/p for reference.....
2012-03-06 13:00:37.340 spid6s Wait-for graph
2012-03-06 13:00:37.350 spid6s NULL
2012-03-06 13:00:37.350 spid6s Node:1
2012-03-06 13:00:37.350 spid6s PAGE: 7:1:18119190 CleanCnt:2 Mode:IX Flags: 0x3
2012-03-06 13:00:37.350 spid6s Grant List 2:
2012-03-06 13:00:37.350 spid6s Owner:0x5ECAA800 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:343 ECID:0 XactLockInfo: 0x5C11EC38
2012-03-06 13:00:37.350 spid6s SPID: 343 ECID: 0 Statement Type: INSERT Line #: 205
2012-03-06 13:00:37.350 spid6s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 969926677]
2012-03-06 13:00:37.350 spid6s Requested by:
2012-03-06 13:00:37.350 spid6s ResType:LockOwner Stype:'OR'Xdes:0x2E3DD6C0 Mode: S SPID:342 BatchID:0 ECID:18 TaskProxy: (0x1EEF0A48) Value:0x3f8cf160 Cost: (0/0)
2012-03-06 13:00:37.350 spid6s NULL
2012-03-06 13:00:37.350 spid6s Node:2
2012-03-06 13:00:37.350 spid6s Port: 0x5CD9EAC0 Xid Slot: 0, Wait Slot: -1, Task: 0x52B5D1C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.350 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:1 TaskProxy: (0x1EEF0718) Value:0x52b5d1c8 Cost: (0/10000)
2012-03-06 13:00:37.350 spid6s SPID: 342 ECID: 1 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.350 spid6s Input Buf: No Event:
2012-03-06 13:00:37.350 spid6s NULL
2012-03-06 13:00:37.350 spid6s Node:3
2012-03-06 13:00:37.350 spid6s Port: 0x5CD9EB40 Xid Slot: 1, Wait Slot: -1, Task: 0x044F71C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.350 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:2 TaskProxy: (0x1EEF0748) Value:0x44f71c8 Cost: (0/10000)
2012-03-06 13:00:37.350 spid6s SPID: 342 ECID: 2 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.350 spid6s Input Buf: No Event:
2012-03-06 13:00:37.350 spid6s NULL
2012-03-06 13:00:37.350 spid6s Node:4
2012-03-06 13:00:37.350 spid6s Port: 0x5CD9EBC0 Xid Slot: 2, Wait Slot: -1, Task: 0x03FFFC78, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.350 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:3 TaskProxy: (0x1EEF0778) Value:0x3fffc78 Cost: (0/10000)
2012-03-06 13:00:37.350 spid6s SPID: 342 ECID: 3 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.350 spid6s Input Buf: No Event:
2012-03-06 13:00:37.370 spid6s NULL
2012-03-06 13:00:37.370 spid6s Node:5
2012-03-06 13:00:37.370 spid6s Port: 0x5CD9EC40 Xid Slot: 3, Wait Slot: -1, Task: 0x038D8C70, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.370 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:6 TaskProxy: (0x1EEF0808) Value:0x38d8c70 Cost: (0/10000)
2012-03-06 13:00:37.370 spid6s SPID: 342 ECID: 6 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.370 spid6s Input Buf: No Event:
2012-03-06 13:00:37.370 spid6s NULL
2012-03-06 13:00:37.370 spid6s Node:6
2012-03-06 13:00:37.370 spid6s Port: 0x5CD9ECC0 Xid Slot: 4, Wait Slot: -1, Task: 0x107EF720, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.370 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:4 TaskProxy: (0x1EEF07A8) Value:0x107ef720 Cost: (0/10000)
2012-03-06 13:00:37.370 spid6s SPID: 342 ECID: 4 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.370 spid6s Input Buf: No Event:
2012-03-06 13:00:37.370 spid6s NULL
2012-03-06 13:00:37.370 spid6s Node:7
2012-03-06 13:00:37.370 spid6s Port: 0x5CD9ED40 Xid Slot: 5, Wait Slot: -1, Task: 0x041A9720, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.370 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:5 TaskProxy: (0x1EEF07D8) Value:0x41a9720 Cost: (0/10000)
2012-03-06 13:00:37.370 spid6s SPID: 342 ECID: 5 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.370 spid6s Input Buf: No Event:
2012-03-06 13:00:37.380 spid6s NULL
2012-03-06 13:00:37.380 spid6s Node:8
2012-03-06 13:00:37.380 spid6s Port: 0x5CD9EDC0 Xid Slot: 6, Wait Slot: -1, Task: 0x043DD8E8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.380 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:7 TaskProxy: (0x1EEF0838) Value:0x43dd8e8 Cost: (0/10000)
2012-03-06 13:00:37.380 spid6s SPID: 342 ECID: 7 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.380 spid6s Input Buf: No Event:
2012-03-06 13:00:37.380 spid6s NULL
2012-03-06 13:00:37.380 spid6s Node:9
2012-03-06 13:00:37.380 spid6s Port: 0x5CD9EE80 Xid Slot: 7, Wait Slot: -1, Task: 0x03C268E0, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.380 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:14 TaskProxy: (0x1EEF0988) Value:0x3c268e0 Cost: (0/10000)
2012-03-06 13:00:37.380 spid6s SPID: 342 ECID: 14 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.380 spid6s Input Buf: No Event:
2012-03-06 13:00:37.380 spid6s NULL
2012-03-06 13:00:37.380 spid6s Node:10
2012-03-06 13:00:37.380 spid6s Port: 0x5CD9EF00 Xid Slot: 8, Wait Slot: -1, Task: 0x03B0D558, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.380 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:10 TaskProxy: (0x1EEF08C8) Value:0x3b0d558 Cost: (0/10000)
2012-03-06 13:00:37.380 spid6s SPID: 342 ECID: 10 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.380 spid6s Input Buf: No Event:
2012-03-06 13:00:37.380 spid6s NULL
2012-03-06 13:00:37.380 spid6s Node:11
2012-03-06 13:00:37.380 spid6s Port: 0x5CD9EF80 Xid Slot: 9, Wait Slot: -1, Task: 0x03AF38E8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.380 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:15 TaskProxy: (0x1EEF09B8) Value:0x3af38e8 Cost: (0/10000)
2012-03-06 13:00:37.380 spid6s SPID: 342 ECID: 15 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.380 spid6s Input Buf: No Event:
2012-03-06 13:00:37.380 spid6s NULL
2012-03-06 13:00:37.380 spid6s Node:12
2012-03-06 13:00:37.380 spid6s Port: 0x5CD9F000 Xid Slot: 10, Wait Slot: -1, Task: 0x3BB4F390, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.380 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:8 TaskProxy: (0x1EEF0868) Value:0x3bb4f390 Cost: (0/10000)
2012-03-06 13:00:37.380 spid6s SPID: 342 ECID: 8 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.380 spid6s Input Buf: No Event:
2012-03-06 13:00:37.400 spid6s NULL
2012-03-06 13:00:37.400 spid6s Node:13
2012-03-06 13:00:37.400 spid6s Port: 0x5CD9F080 Xid Slot: 11, Wait Slot: -1, Task: 0x00F6B558, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.400 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:12 TaskProxy: (0x1EEF0928) Value:0xf6b558 Cost: (0/10000)
2012-03-06 13:00:37.400 spid6s SPID: 342 ECID: 12 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.400 spid6s Input Buf: No Event:
2012-03-06 13:00:37.400 spid6s NULL
2012-03-06 13:00:37.400 spid6s Node:14
2012-03-06 13:00:37.400 spid6s Port: 0x5CD9F100 Xid Slot: 12, Wait Slot: -1, Task: 0x043C2E38, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.400 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:11 TaskProxy: (0x1EEF08F8) Value:0x43c2e38 Cost: (0/10000)
2012-03-06 13:00:37.400 spid6s SPID: 342 ECID: 11 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.400 spid6s Input Buf: No Event:
2012-03-06 13:00:37.410 spid6s NULL
2012-03-06 13:00:37.410 spid6s Node:15
2012-03-06 13:00:37.410 spid6s Port: 0x5CD9F180 Xid Slot: 13, Wait Slot: -1, Task: 0x04711558, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.410 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:9 TaskProxy: (0x1EEF0898) Value:0x4711558 Cost: (0/10000)
2012-03-06 13:00:37.410 spid6s SPID: 342 ECID: 9 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.410 spid6s Input Buf: No Event:
2012-03-06 13:00:37.410 spid6s NULL
2012-03-06 13:00:37.410 spid6s Node:16
2012-03-06 13:00:37.410 spid6s Port: 0x5CD9F240 Xid Slot: 14, Wait Slot: -1, Task: 0x56B36388, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.410 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:13 TaskProxy: (0x1EEF0958) Value:0x56b36388 Cost: (0/10000)
2012-03-06 13:00:37.410 spid6s SPID: 342 ECID: 13 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.410 spid6s Input Buf: No Event:
2012-03-06 13:00:37.410 spid6s NULL
2012-03-06 13:00:37.410 spid6s Node:17
2012-03-06 13:00:37.410 spid6s Port: 0x5CD9F2C0 Xid Slot: 15, Wait Slot: -1, Task: 0x03FF5558, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.410 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:16 TaskProxy: (0x1EEF09E8) Value:0x3ff5558 Cost: (0/10000)
2012-03-06 13:00:37.410 spid6s SPID: 342 ECID: 16 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.410 spid6s Input Buf: No Event:
2012-03-06 13:00:37.430 spid6s NULL
2012-03-06 13:00:37.430 spid6s Node:18
2012-03-06 13:00:37.430 spid6s Port: 0x5CD9E740 Xid Slot: 0, Wait Slot: -1, Task: 0x00F6B000, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-03-06 13:00:37.430 spid6s ResType:ExchangeId Stype:'AND' SPID:342 BatchID:0 ECID:0 TaskProxy: (0x15062340) Value:0xf6b000 Cost: (0/10000)
2012-03-06 13:00:37.430 spid6s SPID: 342 ECID: 0 Statement Type: SELECT Line #: 1
2012-03-06 13:00:37.430 spid6s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 216491950]
2012-03-06 13:00:37.430 spid6s NULL
2012-03-06 13:00:37.430 spid6s Node:19
2012-03-06 13:00:37.430 spid6s PAGE: 7:1:12080226 CleanCnt:2 Mode:S Flags: 0x3
2012-03-06 13:00:37.430 spid6s Grant List 0:
2012-03-06 13:00:37.430 spid6s Owner:0x4103B180 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:342 ECID:0 XactLockInfo: 0x536FC7A8
2012-03-06 13:00:37.430 spid6s Requested by:
2012-03-06 13:00:37.430 spid6s ResType:LockOwner Stype:'OR'Xdes:0x5C11EC10 Mode: IX SPID:343 BatchID:0 ECID:0 TaskProxy: (0x73066354) Value:0x24083a20 Cost: (0/13124)
2012-03-06 13:00:37.430 spid6s NULL
2012-03-06 13:00:37.430 spid6s Victim Resource Owner:
2012-03-06 13:00:37.430 spid6s ResType:LockOwner Stype:'OR'Xdes:0x2E3DD6C0 Mode: S SPID:342 BatchID:0 ECID:18 TaskProxy: (0x1EEF0A48) Value:0x3f8cf160 Cost: (0/0)
2012-03-06 13:00:37.450 spid19s deadlock-list
2012-03-06 13:00:37.450 spid19s deadlock victim=process5e1fe388
2012-03-06 13:00:37.450 spid19s process-list
2012-03-06 13:00:37.450 spid19s process id=process5e1fe388 taskpriority=0 logused=0 waitresource=PAGE: 7:1:18119190 waittime=2030 ownerId=3767988176 transactionname=SELECT lasttranstarted=2012-03-06T13:00:34.760 XDES=0x2e3dd6c0 lockMode=S schedulerid=10 kpid=20944 status=suspended spi
2012-03-06 13:00:37.450 spid19s executionStack
2012-03-06 13:00:37.450 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000fc202206f23604272d649139805bdbe37db116f6
"Some Queries inside the Sproc"
2012-03-06 13:00:37.460 spid19s inputbuf
2012-03-06 13:00:37.460 spid19s Proc [Database Id = 7 Object Id = 969926677]
2012-03-06 13:00:37.460 spid19s resource-list
2012-03-06 13:00:37.460 spid19s pagelock fileid=1 pageid=18119190 dbid=7 objectname='dbname'.dbo.'tablename' id=lock732d9ac0 mode=IX associatedObjectId=72057595776335872
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process3e5aaa8 mode=IX
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process5e1fe388 mode=S requestType=wait
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9eac0 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process52b5d1c8
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9eb40 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process44f71c8
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ebc0 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3fffc78
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ec40 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process38d8c70
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ecc0 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process107ef720
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ed40 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process41a9720
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9edc0 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process43dd8e8
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ee80 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3c268e0
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ef00 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3b0d558
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9ef80 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3af38e8
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f000 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3bb4f390
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f080 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=processf6b558
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f100 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process43c2e38
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f180 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process4711558
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f240 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process56b36388
2012-03-06 13:00:37.460 spid19s exchangeEvent id=Pipe5cd9f2c0 WaitType=e_waitPipeGetRow nodeId=8
2012-03-06 13:00:37.460 spid19s owner-list
2012-03-06 13:00:37.460 spid19s owner id=process5e1fe388
2012-03-06 13:00:37.460 spid19s waiter-list
2012-03-06 13:00:37.460 spid19s waiter id=process3ff5558
2012-03-06 13:00:37.480 spid19s exchangeEvent id=Pipe5cd9e740 WaitType=e_waitPipeGetRow nodeId=1
2012-03-06 13:00:37.480 spid19s owner-list
2012-03-06 13:00:37.480 spid19s owner id=process52b5d1c8
2012-03-06 13:00:37.480 spid19s owner id=process44f71c8
2012-03-06 13:00:37.480 spid19s owner id=process3fffc78
2012-03-06 13:00:37.480 spid19s owner id=process38d8c70
2012-03-06 13:00:37.480 spid19s owner id=process107ef720
2012-03-06 13:00:37.480 spid19s owner id=process41a9720
2012-03-06 13:00:37.480 spid19s owner id=process43dd8e8
2012-03-06 13:00:37.480 spid19s owner id=process3c268e0
2012-03-06 13:00:37.480 spid19s owner id=process3b0d558
2012-03-06 13:00:37.480 spid19s owner id=process3af38e8
2012-03-06 13:00:37.480 spid19s owner id=process3bb4f390
2012-03-06 13:00:37.480 spid19s owner id=processf6b558
2012-03-06 13:00:37.480 spid19s owner id=process43c2e38
2012-03-06 13:00:37.480 spid19s owner id=process4711558
2012-03-06 13:00:37.480 spid19s owner id=process56b36388
2012-03-06 13:00:37.480 spid19s owner id=process3ff5558
2012-03-06 13:00:37.480 spid19s waiter-list
2012-03-06 13:00:37.480 spid19s waiter id=processf6b000
2012-03-06 13:00:37.480 spid19s pagelock fileid=1 pageid=12080226 dbid=7 objectname='dbname'.dbo.'tablename' id=lock2abde340 mode=S associatedObjectId=72057595776335872
2012-03-06 13:00:37.480 spid19s owner-list
2012-03-06 13:00:37.480 spid19s owner id=processf6b000 mode=S
2012-03-06 13:00:37.480 spid19s waiter-list
2012-03-06 13:00:37.480 spid19s waiter id=process3e5aaa8 mode=IX requestType=wait
2012-03-06 13:00:59.600 spid382 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2012-03-06 13:00:59.630 spid382 FILESTREAM: effective level = 0, configured level = 0, file system access share name = '@'.
2012-03-06 13:00:59.730 spid382 Configuration option 'blocked process threshold (s)' changed from 0 to 2. Run the RECONFIGURE statement to install.
2012-03-06 13:00:59.730 spid382 FILESTREAM: effective level = 0, configured level = 0, file system access share name = '@'.
2012-03-06 13:07:15.070 spid376 Process ID 296 was killed by hostname '@', host process ID 6288.
March 6, 2012 at 3:02 pm
Is this system an OLTP system? Are you able to determine if the deadlocks are being created by writes blocking writes or are there some caused by reads?
If writes blocking reads or reads blocking writes are leading to deadlocks, then this could be a situation where row versioning would help. I'd suggest looking into it
March 6, 2012 at 7:24 pm
Chris Harshman (3/6/2012)
Is this system an OLTP system? Are you able to determine if the deadlocks are being created by writes blocking writes or are there some caused by reads?If writes blocking reads or reads blocking writes are leading to deadlocks, then this could be a situation where row versioning would help. I'd suggest looking into it
Thanks Chris,
Yes, this is an OLTP system.
From the deadlock graph that we have, I believe its 1 insert blocking all the other select stmts.
Row versioning was an option that had come to mind, but wasnt sure it would help. Was trying to take the more conventional route by either creating specific indexes or disabling parallism for the queries involved.
Do we have any major tradeoffs for implementing read_committed_snapshot?? or will it work just fine.
Thanks again...
March 6, 2012 at 8:25 pm
Row version isolation will usually prevent deadlocks between readers and writers or updaters.
I have sucessfully used it for this on a number of systems, and can't say that I have seen any drawbacks. On one system it went from over 7,000 deadlocks per day to none.
The first few times I was more cautious about implementing it, but over time I have come around to making it the default for databases unless I know of a reason not to.
March 6, 2012 at 8:58 pm
ssdba (3/6/2012)
Do we have any major tradeoffs for implementing read_committed_snapshot?? or will it work just fine.
As Michael stated, it can solve a wealth of sins. Just make sure that TempDB is in great shape, that the growth patterns have been set so it's not all fragmented like the default settings will fragment it, and that you have the room for it if you do any heavy batch processing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2012 at 12:17 am
For a reader-writer deadlock the snapshot isolations are completely effective in preventing them, and any other form of locking problem. You do need to test them first, some behaviour can change.
If you want some help optimising the queries, can you please post the details of "Some Queries inside the Sproc" as well as the definition of the procedure that has object id 969926677 in database 7.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 12:31 am
Thanks Michael & Jeff,
Already feeling a bit relieved by the response.....though I am yet to implement it & see any difference.
Feels great to know ppl have actually gone ahead & used the approach to resolve deadlocks without any significant drawbacks.
Will let you ppl know of any updates on this....
Thanks Again...
March 7, 2012 at 6:31 am
ssdba (3/7/2012)
Thanks Michael & Jeff,Already feeling a bit relieved by the response.....though I am yet to implement it & see any difference.
Feels great to know ppl have actually gone ahead & used the approach to resolve deadlocks without any significant drawbacks.
Will let you ppl know of any updates on this....
Thanks Again...
Just to be sure, my feeling is that these types of "magic bullets" aren't a panacea to resolve sloppy programming just like having scads of memory or disk space isn't an excuse to write sloppy code. Combining excellent code practices with this type of "prevention" will still do a much better job than using the "magic" alone.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2012 at 6:51 am
ssdba (3/6/2012)
...Row versioning was an option that had come to mind, but wasnt sure it would help. Was trying to take the more conventional route by either creating specific indexes or disabling parallism for the queries involved.Do we have any major tradeoffs for implementing read_committed_snapshot?? or will it work just fine...
Creating more indexes wouldn't necessarily help this problem if any of the columns in the index were part of the update happening in the other transaction. For tradeoffs, as the others have stated, watch the I/O on the tempdb. In my production systems that use this isolation level I keep tempdb files on a separate drive from the other databases and haven't experienced a problem. Row versioning helped our main OLTP system go from averaging about 250 deadlocks per month to now averaging less than 1 per month. I also find row versioning a much cleaner practice than using NOLOCKs or READ UNCOMMITTED
March 7, 2012 at 7:44 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply