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