Understanding & Resolving Deadlock in SQL Server 2008 R2 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 &lt;= 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 &lt;= 0x0000000000sw123


I've looked at fragmentation &amp; 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.
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.