Click here to Skip to main content
15,895,777 members
Please Sign up or sign in to vote.
3.40/5 (2 votes)
See more:
Hi All,
We are facing the deadlock issue for one of our application and below is the sql log obtained. I am unable to interprete the exact issue.

Can some one help me how to understand this one.

SQL
Node:1  
KEY: 8:72057594538426368 (e1e76d2c41ad) CleanCnt:2 Mode:RangeX-X Flags: 0x1
 Grant List 1:
   Owner:0x00000001511ACE00 Mode: RangeX-X Flg:0x40 Ref:0 Life:02000000 SPID:95 ECID:0 XactLockInfo: 0x000000019F52F990
   SPID: 95 ECID: 0 Statement Type: CONDITIONAL Line #: 143
   Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 1003150619]
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000001FAE1A3B0 Mode: RangeS-S SPID:97 BatchID:0 ECID:0 TaskProxy:(0x00000001F42BA540) Value:0x9f71a8c0 Cost:(0/1228)
NULL
Node:2  
KEY: 8:72057594538426368 (79a08f80fbf1) CleanCnt:2 Mode:X Flags: 0x1
 Grant List 1:
   Owner:0x00000001CDD00940 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:97 ECID:0 XactLockInfo: 0x00000001FAE1A3F0
   SPID: 97 ECID: 0 Statement Type: CONDITIONAL Line #: 143
   Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 1003150619]
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x000000019F52F950 Mode: RangeS-S SPID:95 BatchID:0 ECID:0 TaskProxy:(0x00000001A4432540) Value:0x5d929e80 Cost:(0/2312)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x00000001FAE1A3B0 Mode: RangeS-S SPID:97 BatchID:0 ECID:0 TaskProxy:(0x00000001F42BA540) Value:0x9f71a8c0 Cost:(0/1228)
deadlock-list
 deadlock victim=process807dfdc8
  process-list
   process id=process807dfdc8 taskpriority=0 logused=1228 waitresource=KEY: 8:72057594538426368 (e1e76d2c41ad) waittime=2828 ownerId=775643922 transactionname=user_transaction lasttranstarted=2015-02-02T09:24:00.767 XDES=0x1fae1a3b0 lockMode=RangeS-S schedulerid=2 kpid=7948 status=suspended spid=97 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-02-02T09:24:00.767 lastbatchcompleted=2015-02-02T09:24:00.763 clientapp=.Net SqlClient Data Provider hostname=servername1 hostpid=11600 loginname=ABC isolationlevel=serializable (4) xactid=775643922 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=ABC.dbo.usp_UpdateFun line=143 stmtstart=11630 stmtend=12584 sqlhandle=0x030008001bddca3b985a410027a400000100000000000000
IF EXISTS                 
 (                
  SELECT a.*                
  FROM                
   tblBookings a                
   INNER JOIN OPENXML(@doc, 'EventRequest/Bookings', 2) WITH (resourceId int) b ON a.resourceId = b.resourceId                
  WHERE                
   (a.start > @start OR a.finish > @start) AND                 
   (a.start < @finish OR a.finish < @finish)                
 )     
    inputbuf
Proc [Database Id = 8 Object Id = 1003150619]    
   process id=process807dfb88 taskpriority=0 logused=2312 waitresource=KEY: 8:72057594538426368 (79a08f80fbf1) waittime=4107 ownerId=775643278 transactionname=user_transaction lasttranstarted=2015-02-02T09:23:58.540 XDES=0x19f52f950 lockMode=RangeS-S schedulerid=2 kpid=11876 status=suspended spid=95 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-02-02T09:23:58.540 lastbatchcompleted=2015-02-02T09:23:58.540 clientapp=.Net SqlClient Data Provider hostname=servername1 hostpid=11600 loginname=ABC isolationlevel=serializable (4) xactid=775643278 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=ABC.dbo.usp_UpdateFun line=143 stmtstart=11630 stmtend=12584 sqlhandle=0x030008001bddca3b985a410027a400000100000000000000
IF EXISTS                 
 (                
  SELECT a.*                
  FROM                
   tblBookings a                
   INNER JOIN OPENXML(@doc, 'EventRequest/Bookings', 2) WITH (resourceId int) b ON a.resourceId = b.resourceId                
  WHERE                
   (a.start > @start OR a.finish > @start ) AND                 
   (a.start < @finish OR a.finish < @finish )                
 )     
    inputbuf
Proc [Database Id = 8 Object Id = 1003150619]    
  resource-list
   keylock hobtid=72057594538426368 dbid=8 objectname=ABC.dbo.tblBookings indexname=IX5_tblBookings id=lock18c90fe00 mode=RangeX-X associatedObjectId=72057594538426368
    owner-list
     owner id=process807dfb88 mode=RangeX-X
    waiter-list
     waiter id=process807dfdc8 mode=RangeS-S requestType=wait
   keylock hobtid=72057594538426368 dbid=8 objectname=ABC.dbo.tblBookings indexname=IX5_tblBookings id=lock1d2d12d80 mode=X associatedObjectId=72057594538426368
    owner-list
     owner id=process807dfdc8 mode=X
    waiter-list
     waiter id=process807dfb88 mode=RangeS-S requestType=wait


Thanks in advance
Posted
Updated 2-Feb-15 21:55pm
v2
Comments
Herman<T>.Instance 3-Feb-15 3:56am    
what is the complete SQL command? i.e. what are you doing with ) inputbuf....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900