How to prevent duplicate insertion without locking the entire table?

[jasondi]

Hello,
I've a scenario with multiple threads that performs select/insert/update on the same table.
Basically, the flow is:

SELECT * FROM tablex where pk=?

if empty

INSERT into tablex values(...)

else

UPDATE tablex set ... where pk=?

where ph is the primary key ot tablex
To improve performance, I'd like that threads with different Pks will run without blocking, but threads with the same PK will be "serialized".
Using SELECT FOR UPDATE WITH RS is fine when the row already exists, but produce duplicate insert when the row doesn't exist yet, and two thread are trying to insert it.
Using TRANSACTION_SERIALIZABLE or SELECT FOR UPDATE WITH RR cause a table lock, and so threads with different Pks are blocked.
Is there a basic pattern to solve this problem?

Environment: Db2 V9.1 on Windows. Using a normal Java application

[docdiesel]

Hi,

did you try the MERGE statement (http://publib.boulder.ibm.com/infoce...c/r0010873.htm ) ? Something like the following could be working:

MERGE INTO mytable mta
USING
  (SELECT pk FROM mytable where pk=xxx) mtb
ON (mta.pk=mtb.pk)
WHEN MATCHED THEN
  UPDATE
  SET field = value
WHEN NOT MATCHED THEN
  INSERT
    (fields)
  VALUES
    (values)

Regards,

Bernd