Constraint Violation and Locks

Came across this nasty surprise while doing some casual testing. It is a surprise because this is something that   seems unnecessary.

Here's a quick look at the problem. First, make sure auto-commit is off.

a) create a simple table T1 with a unique column:

CREATE TABLE T1 ( C1 NUMBER NOT NULL PRIMARY KEY)

b) insert a single row into T1

INSERT INTO T1 VALUES (1)

COMMIT


c) Now, let's try to do the same insert:

INSERT INTO T1 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 "DB2INST1.T1" from having duplicate values for the index key.
SQLSTATE=23505

**********************

So far so good. We have triggered a constraint violation.

d) Now, start up another CLP session and issue this:

UPDATE T1 SET C1 = 2 WHERE C1 = 1 

You will notice that this session 'stalls'. Why? Because it is blocked. The question is: blocked by what?

==> It is blocked by the previous INSERT session that caused the constraint violation.

Comment: In my opinion, it is very strange that DB2 keeps the locks for an INSERT that has failed. This behaviour is hindering concurrency. It should  just simply release those locks associated with the failed constraint and be done with it.