3

I am trying hard to figure out what may be going wrong for below shown deadlock graph:

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.

Improve this question
5
  • 2
    All of the above locks are page locks. Does the table have a composite clustered key and are the locks on the clustered key or any secondary index ? Try looking at which part of the table is being blocked DBCC TRACEON(3604) DBCC PAGE(11,1,100787,3) WITH TABLERESULTS and then see if you can get the server to use rowlocks
    – Spörri
    May 27, 2015 at 17:19
  • To clarify, the issue is that you are getting page locks please update question with schema and indexes
    – Spörri
    May 27, 2015 at 17:41
  • @Spörri, Thanks let me gather the results and will update!
    – KASQLDBA
    May 28, 2015 at 10:31
  • @Spörri, with help of trace flag 1222 i tried pulling up the table name and found that there is only one 'non clustered index out there. Not sure if this info helps. Also what info you would need on DBCC PAGE, because i see quite a huge info getting pulled up
    – KASQLDBA
    May 28, 2015 at 14:54
  • That's what we need to know. We now know that the deadlock is happening on the non clustered index on the table. So now it's possible to guess why you are getting page locks there. The update/delete is probably locking on the same page because of the index key. Can you post the table and index definitions. Also check as Andomar suggests that insert/update/delete are always done in the same order in all transactions
    – Spörri
    May 28, 2015 at 15:14

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • the server is new to me. It just came recenly for performance issues and this was one of the finding. I believe i need to contact people who developed the code as you mentioned above. Also, can you please clarify little more on above or any article you can refer where i can explore more on what you mentioned ? Thanks
    – KASQLDBA
    May 28, 2015 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.