求哪位大侠帮忙分析下死锁日志
通过 DBCC?TRACEON?(3605,1204,1222,-1)??
抓到了死锁日志，日志附在下面，我不太明白是，先UPDATE再INSERT，怎么会是有了X锁，再请求RangeS-S锁呢？
具体日志如下：
59:50.spid5sDeadlock encountered .... Printing deadlock information
59:50.spid5sWait-for graph
59:50.spid5sNULL
59:50.spid5sNode:1
59:50.spid5sKEY: 6:72057594065715200 (8500ea663c04) CleanCnt:2 Mode:RangeS-U Flags: 0x1
59:50.spid5s Grant List 2:
59:50.spid5s Owner:0x00000000077E4AC0 Mode: RangeS-U Flg:0x40 Ref:0 Life:00000001 SPID:66 ECID:0 XactLockInfo: 0x00000007D3EEE3F0
59:50.spid5s SPID: 66 ECID: 0 Statement Type: UPDATE Line #: 92
59:50.spid5s Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 1463676262]
59:50.spid5sRequested by:
59:50.spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000080092570 Mode: RangeS-U SPID:57 BatchID:0 ECID:0 TaskProxy:(0x000000022CE2C538) Value:0x785b500 Cost:(0/480)
59:50.spid5sNULL
59:50.spid5sNode:2
59:50.spid5sKEY: 6:72057594048806912 (6200429172ae) CleanCnt:2 Mode:X Flags: 0x1
59:50.spid5s Grant List 2:
59:50.spid5s Owner:0x0000000006D05CC0 Mode: X Flg:0x40 Ref:0 Life:02000001 SPID:57 ECID:0 XactLockInfo: 0x00000000800925B0
59:50.spid5s SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 57
59:50.spid5s Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 1463676262]
59:50.spid5sRequested by:
59:50.spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000007D3EEE3B0 Mode: RangeS-S SPID:66 BatchID:0 ECID:0 TaskProxy:(0x00000006C426A538) Value:0x6ba2700 Cost:(0/1236)
59:50.spid5sNULL
59:50.spid5sVictim Resource Owner:
59:50.spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000080092570 Mode: RangeS-U SPID:57 BatchID:0 ECID:0 TaskProxy:(0x000000022CE2C538) Value:0x785b500 Cost:(0/480)
59:50.spid24sdeadlock-list
59:50.spid24s deadlock victim=process6463b88
59:50.spid24s process-list
59:50.spid24s process id=process6463b88 taskpriority=0 logused=480 waitresource=KEY: 6:72057594065715200 (8500ea663c04)
waittime=516 ownerId=773364767 transactionname=UpdateAdAndSetCost lasttranstarted=2013-06-04T08:59:49.450
XDES=0x80092570 lockMode=RangeS-U schedulerid=22 kpid=2872 status=suspended spid=57 sbid=0 ecid=0 priority=0
trancount=3 lastbatchstarted=2013-06-04T08:59:49.453 lastbatchcompleted=2013-06-04T08:59:49.450
clientapp=.Net SqlClient Data Provider hostname=FZTEC-240151 hostpid=61828 loginname=jijin91
isolationlevel=read committed (2) xactid=773364767 currentdb=6 lockTimeout=4294967295
clientoption1=671088672 clientoption2=128056
59:50.spid24s executionStack
59:50.spid24s frame procname=jijin.dbo.PR_InsertAdCostDetail line=57 stmtstart=3438 stmtend=4438 sqlhandle=0x0300060066ed3d57bde7b100d2a100000100000000000000
59:50.spid24sUPDATE a
59:50.spid24s SET a.Cost = a.Cost + b.Cost ,
59:50.spid24s UpdateTime = @now
59:50.spid24s FROM AdvertCostDaily a ,
59:50.spid24s @tblCost b
59:50.spid24s WHERE a.AdId = b.AdId
59:50.spid24s AND a.CostDate = @chToday;
59:50.spid24s --插入扣费汇总表,先清除更新数据
59:50.spid24s --DELETE b FROM @tblCost b
59:50.spid24s -- INNER JOIN AdvertCostDaily a with (nolock) ON a.AdID = b.AdId
59:50.spid24s -- AND a.CostDate = @chToday;
59:50.spid24s --插入到每天汇总表
59:50.spid24s inputbuf
59:50.spid24sProc [Database Id = 6 Object Id = 1463676262]
59:50.spid24s process id=process7382e08 taskpriority=0 logused=1236 waitresource=KEY: 6:72057594048806912 (6200429172ae)
waittime=516 ownerId=773364742 transactionname=UpdateAdAndSetCost lasttranstarted=2013-06-04T08:59:49.440
XDES=0x7d3eee3b0 lockMode=RangeS-S schedulerid=23 kpid=3400 status=suspended spid=66 sbid=0 ecid=0 priority=0
trancount=3 lastbatchstarted=2013-06-04T08:59:49.443 lastbatchcompleted=2013-06-04T08:59:49.440
clientapp=.Net SqlClient Data Provider hostname=FZTEC-240125 hostpid=151812 loginname=jijin91
isolationlevel=read committed (2) xactid=773364742 currentdb=6 lockTimeout=4294967295
clientoption1=671088672 clientoption2=128056
59:50.spid24s executionStack
59:50.spid24s frame procname=jijin.dbo.PR_InsertAdCostDetail line=92 stmtstart=5774 stmtend=6218 sqlhandle=0x0300060066ed3d57bde7b100d2a100000100000000000000
59:50.spid24sUPDATE a
59:50.spid24s SET a.Cost = a.Cost + b.Cost ,
59:50.spid24s UpdateTime = @now
59:50.spid24s FROM AdvertCost a ,
59:50.spid24s @tblCost b
59:50.spid24s WHERE a.AdId = b.AdId
59:50.spid24s --插入总汇总表,先清除更新数据
59:50.spid24s inputbuf
59:50.spid24sProc [Database Id = 6 Object Id = 1463676262]
59:50.spid24s resource-list
59:50.spid24s keylock hobtid=72057594065715200 dbid=6 objectname=jijin.sys.query_notification_685245496 indexname=cidx id=lock74d3d00 mode=RangeS-U associatedObjectId=72057594065715200
59:50.spid24s owner-list
59:50.spid24s owner id=process7382e08 mode=RangeS-U
59:50.spid24s waiter-list
59:50.spid24s waiter id=process6463b88 mode=RangeS-U requestType=wait
59:50.spid24s keylock hobtid=72057594048806912 dbid=6 objectname=jijin.dbo.AdvertCostDaily indexname=PK_AdCostDaily id=lock7d41d00 mode=X associatedObjectId=72057594048806912
59:50.spid24s owner-list
59:50.spid24s owner id=process6463b88 mode=X
59:50.spid24s waiter-list
59:50.spid24s waiter id=process7382e08 mode=RangeS-S requestType=wait
，你的 isolationlevel=read committed ，但是出现了key-range 锁，这种情况比较少，一般在indexed view或者外键等级联操作中会出现。
owner id=process7382e08 mode=RangeS-U
waiter id=process6463b88 mode=RangeS-U requestType=wait
owner id=process6463b88 mode=X
waiter id=process7382e08 mode=RangeS-S requestType=wait
首先，从上面看出，不是“已经拥有对象的RangeS-U锁，希望请求的也是RangeS-U锁，”也不是“已经拥有了X锁，它希望请求RangeS-S锁，”， 而是拥有X的process6463b88 请求RangeS-U，跟拥有RangeS-U的process7382e08 ，请求RangeS-S。
另外， 上面那个争抢的资源是： jijin.sys.query_notification_685245496 indexname=cidx ，请问query_notification_685245496是什么？一个表？那还有个表上的索引cidx，跟@tblCostDetail或者你上面的那些语句有什么联系？为什么会用到sys.query_notification_685245496 的cidx？
