Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
2

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know this, too.

Thanks to anyone who can help!

share|improve this question

1 Answer

up vote 10 down vote accepted

In the deadlock graph XML you will see something like:

<deadlock-list>
  <deadlock victim="...">
    <process-list>
      <process id="..." ... waitresource="X:..."
...

The X is the interesting bit, possible values you are interested in are:

  • RID for row id (row level locking)
  • PAG for page level lock
  • OBJECT (which may be further qualified TAB indicating a table lock)

There are a few others kinds listed in the documentation also.

share|improve this answer
Thanks, that's useful. Although in my graph I have 2 processes, which appear to be using different pages: waitresource="RID: 21:1:2588:0" waitresource="RID: 21:1:2699:1" So if they are using different pages, then they can't be conflicting over the same row, correct? Because I'm using a WITH (ROWLOCK) hint on this query. – tuseau Apr 6 '11 at 14:15
2  
If process 1 is holding 2699 and wants 2588, and process 2 is holding 2588 and wants 2699, that would deadlock. Remember a deadlock implies a circular dependency. – Gaius Apr 6 '11 at 14:21
Yeah, that clears it up. Thanks again. – tuseau Apr 6 '11 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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