DELETE of 1 row gets multiple Exclusive Row locks?

[Scott Cameron]

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

[Matthew Emmerton]

"Scott Cameron" <scameron24@hotmail.com> wrote in message
news:9a3e4ce1.0309221133.d69093@posting.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 accumulate.
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

[Mark Yudkin]

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" <scameron24@hotmail.com> wrote in message
news:9a3e4ce1.0309221133.d69093@posting.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

[Scott Cameron]

"Matthew Emmerton" <memmerto@yahoo.com> wrote in message news:<3fIbb.972$PT3.200062@news20.bellglobal.com>. ..

> "Scott Cameron" <scameron24@hotmail.com> wrote in message
> news:9a3e4ce1.0309221133.d69093@posting.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 accumulate.
> 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