Professional Web Applications Themes

DELETE of 1 row gets multiple Exclusive Row locks? - IBM DB2

I'm trying to debug a deadlock problem I'm having using DB2 7.2 and I'm a bit stumped. As part of my testing, I've turned statement batching completely off in my application. This means that for any insert/modify/delete or select (except for the ones I set as Uncommitted Read) I only ever operate on a single row in one transaction. Still, I'm getting deadlocks. The schema I'm using has one main table and a number of indexes. When doing a DELETE, the WHERE only contains indexed fields, which should limit the number of row locks required. I'm at the point now ...

Sponsored Links
  1. #1

    Default DELETE of 1 row gets multiple Exclusive Row locks?

    I'm trying to debug a deadlock problem I'm having using DB2 7.2 and
    I'm a bit stumped.

    As part of my testing, I've turned statement batching completely off
    in my application. This means that for any insert/modify/delete or
    select (except for the ones I set as Uncommitted Read) I only ever
    operate on a single row in one transaction. Still, I'm getting
    deadlocks.

    The schema I'm using has one main table and a number of indexes. When
    doing a DELETE, the WHERE only contains indexed fields, which should
    limit the number of row locks required.

    I'm at the point now where I'm seeing something I can't explain. From
    the combination of a lock snapshot and event monitoring I can see that
    a transaction containing a DELETE statement that deletes only a single
    row is getting a number of exclusive locks. With our indexes, I would
    expect to see only a single exclusive row lock. The DELETE statement
    looks like this:

    DELETE FROM MYTABLE WHERE Id in (1234)

    Does anybody have an explanation for what I'm seeing? Here is the
    snapshot I was getting:

    Application Lock Snapshot

    Snapshot timestamp = 09-22-2003
    12:08:01.702215

    Application handle = 367
    Application ID =
    AC1E00DB.3A06.030922181021
    Sequence number = 0001
    Application name = MyApp.exe
    Authorization ID = MYID
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1252
    Locks held = 14
    Total wait time (ms) = 0

    List Of Locks
    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = IS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = IS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = IS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = IS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = IS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal L Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = S
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 29962
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 22022
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 720136
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 721409
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 719372
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 720133
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 580
    Node number lock is held at = 0
    Object Type = Table
    Tablespace Name = USERSPACE1
    Table Schema = MYDB
    Table Name = MYTABLE
    Mode = IX
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 0
    Node number lock is held at = 0
    Object Type = Internal P Lock
    Tablespace Name =
    Table Schema =
    Table Name =
    Mode = S
    Status = Granted
    Lock Escalation = NO
    Sponsored Links
    Scott Cameron Guest

  2. #2

    Default Re: DELETE of 1 row gets multiple Exclusive Row locks?


    "Scott Cameron" <scameron24hotmail.com> wrote in message
    news:9a3e4ce1.0309221133.d69093posting.google.com ...
    > I'm trying to debug a deadlock problem I'm having using DB2 7.2 and
    > I'm a bit stumped.
    >
    > As part of my testing, I've turned statement batching completely off
    > in my application. This means that for any insert/modify/delete or
    > select (except for the ones I set as Uncommitted Read) I only ever
    > operate on a single row in one transaction. Still, I'm getting
    > deadlocks.
    >
    > The schema I'm using has one main table and a number of indexes. When
    > doing a DELETE, the WHERE only contains indexed fields, which should
    > limit the number of row locks required.
    >
    > I'm at the point now where I'm seeing something I can't explain. From
    > the combination of a lock snapshot and event monitoring I can see that
    > a transaction containing a DELETE statement that deletes only a single
    > row is getting a number of exclusive locks. With our indexes, I would
    > expect to see only a single exclusive row lock. The DELETE statement
    > looks like this:
    >
    > DELETE FROM MYTABLE WHERE Id in (1234)
    >
    > Does anybody have an explanation for what I'm seeing?
    <snip snapshot>

    Are you sure that only 1 row matches your DELETE statement?
    Are you committing after every DELETE? If not, the X locks will aculate.
    Also, depending on your isolation level, you may be getting hit by next-key
    locking, which is used in some isolation levels to ensure the stability of
    the result set.

    --
    Matt Emmerton


    Matthew Emmerton Guest

  3. #3

    Default Re: DELETE of 1 row gets multiple Exclusive Row locks?

    Try DELETE FROM MYTABLE WHERE Id = 1234. I'm not sure the optimizer picks
    up the semantic equivalence of IN (1234) with = 1234, and may be locking
    more as a result.

    Also I've noticed that some people say they're getting deadlocks when
    they're actually getting timeouts. Please confirm that you have a deadlock
    and not a timeout; there is a huge difference. To resolve a timeout, either
    increase the wait time, or commit more frequently. For a deadlock, you're
    going to have to review the complete set of SQL executed since the last
    COMMIT in all concurrent transactions to determine what's happening, use a
    statement trace to do this.

    "Scott Cameron" <scameron24hotmail.com> wrote in message
    news:9a3e4ce1.0309221133.d69093posting.google.com ...
    > I'm trying to debug a deadlock problem I'm having using DB2 7.2 and
    > I'm a bit stumped.
    >
    > As part of my testing, I've turned statement batching completely off
    > in my application. This means that for any insert/modify/delete or
    > select (except for the ones I set as Uncommitted Read) I only ever
    > operate on a single row in one transaction. Still, I'm getting
    > deadlocks.
    >
    > The schema I'm using has one main table and a number of indexes. When
    > doing a DELETE, the WHERE only contains indexed fields, which should
    > limit the number of row locks required.
    >
    > I'm at the point now where I'm seeing something I can't explain. From
    > the combination of a lock snapshot and event monitoring I can see that
    > a transaction containing a DELETE statement that deletes only a single
    > row is getting a number of exclusive locks. With our indexes, I would
    > expect to see only a single exclusive row lock. The DELETE statement
    > looks like this:
    >
    > DELETE FROM MYTABLE WHERE Id in (1234)
    >
    > Does anybody have an explanation for what I'm seeing? Here is the
    > snapshot I was getting:
    >
    > Application Lock Snapshot
    >
    > Snapshot timestamp = 09-22-2003
    > 12:08:01.702215
    >
    > Application handle = 367
    > Application ID =
    > AC1E00DB.3A06.030922181021
    > Sequence number = 0001
    > Application name = MyApp.exe
    > Authorization ID = MYID
    > Application status = UOW Waiting
    > Status change time = Not Collected
    > Application code page = 1252
    > Locks held = 14
    > Total wait time (ms) = 0
    >
    > List Of Locks
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = IS
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = IS
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = IS
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = IS
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = IS
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal L Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = S
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 29962
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 22022
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 720136
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 721409
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 719372
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 720133
    > Node number lock is held at = 0
    > Object Type = Row
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = X
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 580
    > Node number lock is held at = 0
    > Object Type = Table
    > Tablespace Name = USERSPACE1
    > Table Schema = MYDB
    > Table Name = MYTABLE
    > Mode = IX
    > Status = Granted
    > Lock Escalation = NO
    >
    > Lock Object Name = 0
    > Node number lock is held at = 0
    > Object Type = Internal P Lock
    > Tablespace Name =
    > Table Schema =
    > Table Name =
    > Mode = S
    > Status = Granted
    > Lock Escalation = NO

    Mark Yudkin Guest

  4. #4

    Default Re: DELETE of 1 row gets multiple Exclusive Row locks?

    "Matthew Emmerton" <memmerto> wrote in message news:<3fIbb.972$PT3.200062news20.bellglobal.com>. ..
    > "Scott Cameron" <scameron24hotmail.com> wrote in message
    > news:9a3e4ce1.0309221133.d69093posting.google.com ...
    > > I'm trying to debug a deadlock problem I'm having using DB2 7.2 and
    > > I'm a bit stumped.
    > >
    > > As part of my testing, I've turned statement batching completely off
    > > in my application. This means that for any insert/modify/delete or
    > > select (except for the ones I set as Uncommitted Read) I only ever
    > > operate on a single row in one transaction. Still, I'm getting
    > > deadlocks.
    > >
    > > The schema I'm using has one main table and a number of indexes. When
    > > doing a DELETE, the WHERE only contains indexed fields, which should
    > > limit the number of row locks required.
    > >
    > > I'm at the point now where I'm seeing something I can't explain. From
    > > the combination of a lock snapshot and event monitoring I can see that
    > > a transaction containing a DELETE statement that deletes only a single
    > > row is getting a number of exclusive locks. With our indexes, I would
    > > expect to see only a single exclusive row lock. The DELETE statement
    > > looks like this:
    > >
    > > DELETE FROM MYTABLE WHERE Id in (1234)
    > >
    > > Does anybody have an explanation for what I'm seeing?
    >
    > <snip snapshot>
    >
    > Are you sure that only 1 row matches your DELETE statement?
    > Are you committing after every DELETE? If not, the X locks will aculate.
    > Also, depending on your isolation level, you may be getting hit by next-key
    > locking, which is used in some isolation levels to ensure the stability of
    > the result set.
    It turns out that the problem does seem to involve next-key locking.
    Setting DB2_RR_TO_RS=YES made the problem go away. I'm still a bit
    unclear as to why this worked so well -- I am always using the default
    CS for my isolation level (except for UR in limited cases). And I
    don't understand why next-key locking would lock so many rows instead
    of just the next one row. I'm sure this DELETE statement only matches
    a single row because "Id" is always unique.

    But like I said, the deadlocks are gone so, understand it or not, I
    can't complain. :)

    Thanks for the response.

    scott
    Scott Cameron Guest

  5. #5

    Default Re: DELETE of 1 row gets multiple Exclusive Row locks?

    .... and as you did not describe your whole transaction to us,
    there could be other insert or update statements before your commit.

    PM


    PM Guest

Similar Threads

  1. exclusive and non-exclusive state models
    By Bruce_Hensley@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: January 24th, 06:33 PM
  2. Delete multiple rows
    By Darren Carter in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 12th, 10:33 PM
  3. Server 2003 delete used exclusive files
    By N F in forum Windows Server
    Replies: 0
    Last Post: June 10th, 12:11 AM
  4. multiple delete with checkboxes ?
    By Fredrik/Sweden in forum ASP Database
    Replies: 3
    Last Post: October 2nd, 03:53 PM
  5. can you delete multiple frames at once?
    By gooskimo webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: July 24th, 03:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139