10

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!

tuseau
  • 1,885
  • 5
  • 18
  • 18

1 Answers1

14

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.

Gaius
  • 11,200
  • 3
  • 31
  • 64
  • 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 06 '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 06 '11 at 14:21