Hello Experts,

Can someone please help me in resolving the below deadlock situation:

I ran DBCC TRACEON(1222,-1) and suppressed the results as below: But i am unable to conclude what could be the problemhere: Please help:

     deadlock victim=process5e27048   process id=process5e27048 taskpriority=0 logused=480 waitresource=PAGE: 11:1:100919 waittime=2682 ownerId=822500103 transactionname=user_transaction lasttranstarted=2015-05-28T09:47:56.413 XDES=0x2e9b3d970 lockMode=U schedulerid=4 kpid=15300 status=suspended spid=962
       frame procname=abcsyss.dbo.xAAccessLog_Update_KBTech line=1 stmtstart=848 stmtend=1618
  sqlhandle=0x03000b00f8bdf00cbba2d100e1a300000000000000000000
  UPDATE
  [access] set [accessdate] = [accessdate] 
  WHERE [userid]%%=%%@1 AND [companyid]%%=%%@2 AND [databasename]%%=%%@3
  AND [internetaddress]%%=%%@4 AND [sessioncntr]%%=%%@5 AND [tstamp]<=@6     
  update
  access set accessdate=accessdate  where
  userid= 'mathy.rod' and companyid= '31' and databasename=
  'ABCAPP' and internetaddress= 'werRDP-Tcp#10' and
  sessioncntr=14  and tstamp <=
  0x0000000000cba6c3 process id=process5e3b288 taskpriority=0 logused=716 waitresource=PAGE: 11:1:100914 waittime=2491 ownerId=822496200 transactionname=user_transaction lasttranstarted=2015-05-28T09:47:56.043 XDES=0x15a0cce90 lockMode=U schedulerid=6 kpid=11996 status=suspended spid=851    
    frame procname=abcsyss.dbo.xAAccessLog_Delete_KBTech line=1 stmtstart=464 stmtend=1984
  sqlhandle=0x03000b008675080bec98d100e1a300000000000000000000
  DELETE
  [access]  WHERE [userid]%%=%%@1 AND
  [companyid]%%=%%@2 AND [databasename]%%=%%@3 AND [internetaddress]%%=%%@4 AND
  [sessioncntr]%%=%%@5     
  delete
  access where userid= 'WILLI.GOW' and companyid= '81' and databasename=
  'ABCAPP' and internetaddress= 'TPRDP-Tcp#2' and
  sessioncntr=10     
 
 
  Text
 
 
       waiter id=process5e3b288 mode=U requestType=wait  
 
 
      waiter-list
 
 
       owner
  id=process5e27048 mode=X
 
 
      owner-list
 
 
     pagelock fileid=1
  pageid=100914 dbid=11 objectname=abcsyss.dbo.xAAccessLog
  id=lock334291b80 mode=X associatedObjectId=72057594089570304
 
 
       waiter
  id=process5e27048 mode=U requestType=wait
 
 
      waiter-list
 
 
       owner
  id=process5e3b288 mode=X
 
 
      owner-list
 
 
     pagelock fileid=1
  pageid=100919 dbid=11 objectname=abcsyss.dbo.xAAccessLog
  id=lock37d944a00 mode=X associatedObjectId=72057594089570304

Post the Table DDL including indexes and queries.  This is likely caused by missing indexes, causing your UPDATE to scan too much and use page locks.

David

And the isolation levels in use on the two connections.

Josh

Sounds like this could be due to a trigger on the table or due to referential integrity (RI) being enforced, which I've seen many times.

You can see what FKs and triggers are defined on those the access table with the following:

select * from sys.foreign_keys where parent_object_id = object_id('access')

select * from sys.triggers where parent_id = object_id('access')