failed insert because of duplicate rows and still locks held by application

[florian]

Hi,

we have a contention problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.

Why DB2 does not release the X lock after a failed insert???

We use DB2 UDB EEE Version 7.2 Fixpak 9, but we also can reproduce the
Problem on DB2 UDB ESE 8.1 Linux Fixpak 4.

I can reproduce this behavior with the statements below.

db2 +c insert into testtab values(1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0803N One or more values in the INSERT statement, UPDATE
statement, or
foreign key update caused by a DELETE statement are not valid because
the
primary key, unique constraint or unique index identified by "1"
constrains
table "DB2ADMIN.TESTTAB" from having duplicate rows for those columns.
SQLSTATE=23505

db2 get snapshot for locks for application agentid 3

Application Lock Snapshot

Snapshot timestamp = 22/04/2004
10.53.23.324831

Application handle = 3
Application ID = *LOCAL.DB2.040422084540
Sequence number = 0001
Application name = db2bp.exe
Authorization ID = DB2ADMIN
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 5
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 4
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2ADMIN
Table Name = TESTTAB
Mode = W
Status = Granted
Lock Escalation = NO

Lock Object Name = 5
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2ADMIN
Table Name = TESTTAB
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 22
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = DB2ADMIN
Table Name = TESTTAB
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
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

Thanks, Florian

[Serge Rielau]

I doubt DB2 releases a (row) lock on statement failure.
You need to rollback or commit.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

I doubt DB2 releases a (row) lock on statement failure.
You need to rollback or commit.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"florian" <au*****@web.de> wrote in message
news:35*************************@posting.google.co m...

Hi,

we have a contention problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.

Why DB2 does not release the X lock after a failed insert???

We use DB2 UDB EEE Version 7.2 Fixpak 9, but we also can reproduce the
Problem on DB2 UDB ESE 8.1 Linux Fixpak 4.

I can reproduce this behavior with the statements below.

db2 +c insert into testtab values(1)

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s).

[Mark A]

"florian" <au*****@web.de> wrote in message
news:35*************************@posting.google.co m...

Hi,

we have a contention problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.

Why DB2 does not release the X lock after a failed insert???

We use DB2 UDB EEE Version 7.2 Fixpak 9, but we also can reproduce the
Problem on DB2 UDB ESE 8.1 Linux Fixpak 4.

I can reproduce this behavior with the statements below.

db2 +c insert into testtab values(1)

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s).

[florian]

That's the point. Of course DB2 does release the lock when i do
commit/rollback. My question is: why DB2 generates that lock even if
the insert fails?

cheers
Florian

"Mark A" <[e-mail]> wrote in message news:<Zs**************@news.uswest.net>...

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s).

[florian]

That's the point. Of course DB2 does release the lock when i do
commit/rollback. My question is: why DB2 generates that lock even if
the insert fails?

cheers
Florian

"Mark A" <[e-mail]> wrote in message news:<Zs**************@news.uswest.net>...

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s).

[Mark A]

> "Mark A" <[e-mail]> wrote in message
news:<Zs**************@news.uswest.net>...

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s)."florian" <au*****@web.de> wrote

in message news:35*************************@posting.google.co m... That's the point. Of course DB2 does release the lock when i do
commit/rollback. My question is: why DB2 generates that lock even if
the insert fails?

cheers
Florian

DB2 obtains the lock before it knows whether the insert will fail or be
successful. If DB2 did not obtain the lock beforehand, the lock would be
useless.

[Mark A]

> "Mark A" <[e-mail]> wrote in message
news:<Zs**************@news.uswest.net>...

When you use +c you are turning off auto-commit so you need to do an
explicit commit to release the lock(s)."florian" <au*****@web.de> wrote

in message news:35*************************@posting.google.co m... That's the point. Of course DB2 does release the lock when i do
commit/rollback. My question is: why DB2 generates that lock even if
the insert fails?

cheers
Florian

DB2 obtains the lock before it knows whether the insert will fail or be
successful. If DB2 did not obtain the lock beforehand, the lock would be
useless.

[florian]

>

DB2 obtains the lock before it knows whether the insert will fail or be
successful. If DB2 did not obtain the lock beforehand, the lock would be
useless.

Ok, you are right, after thinking it over it makes sense and my
question was kind of stupid.

Thank you
Florian

[florian]

>

DB2 obtains the lock before it knows whether the insert will fail or be
successful. If DB2 did not obtain the lock beforehand, the lock would be
useless.

Ok, you are right, after thinking it over it makes sense and my
question was kind of stupid.

Thank you
Florian