I am trying hard to figure out what may be going wrong for below shown deadlock graph:
I am receiving alerts for deadlocks similar to above for almost every 10 minutes, it's just the query changes, action of deadlock remains the same:
**Wait Resource Page:11:11:...2820**
Lock Type: Page
Own/Wait: Own
Mode: X
SPID: 1568
ECID: 0
Sql:
update access
set accessdate=accessdate where userid= 'ABC.DEF' and companyid= '12'
and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
and sessioncntr=12 and tstamp <= 0x0000000000sw123
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1120
ECID: 0
Sql:
delete access where userid= 'ADAm.Smith' and companyid= '23'
and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
and sessioncntr=23
**Wait Resource Page:11:11:...2830**
Lock Type: Page
Own/Wait: Own
Mode: X
SPID: 1120
ECID: 0
Sql:
delete access where userid= 'ADAm.Smith' and companyid= '23'
and databasename= 'XYZ' and internetaddress= 'KASDER-Tcp#8'
and sessioncntr=23
Lock Type: Page
Own/Wait: Wait
Mode: U
SPID: 1568
ECID: 0
Sql:
update access
set accessdate=accessdate where userid= 'ABC.DEF' and companyid= '12'
and databasename= 'XYZ' and internetaddress= 'KASQL-Tcp#10'
and sessioncntr=12 and tstamp <= 0x0000000000sw123
I've looked at fragmentation & any missing indexes , but that's all good for the database xyz as mentioned in queries above!
Note: DBID mentioned in deadlock graph says dbid 11, which is different than on e in queries that is XYZ, so dbid 11 is actually another database 'DBCapse'
Please provide any inputs that can help me in resolving and understanding this deadlock situation.