Understanding & Resolving Deadlock in SQL Server 2008 R2

Question

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.

Answer 1

Looks like multiple statements in a transaction. One row is the target of the first statement of the first transaction and the second statement of the second transaction. And vice-versa: the same row is targeted by the second statement of the first transaction and the first statement of the second transaction.

Is there any reason why you are using multi-statement transactions?

If so, can you "order" the statements within the transaction so that they always affect rows in a predefined order? In a row with an id identity key, if consecutive statements affect higher id transactions, the chance for conflict is greatly reduced.