How to avoid duplcate key insertions in a multi-user DB2 environment?

[Isaac Blank]

Hi.

We've run into a concurrency issue I do not have a clear solution for. In a
DB2 UDB 7.2 database, we have several tables with a chain of foreign key
relarionships:

Table1 primary key x
table2 foreign key x references table1(x)
table3 foreign key x references table2(x)
.........................................

Here's the simplified logic one of our application uses (given a value "foo"
for the column x)

1. Check if a row exists in table1 with x='foo'
2. if no row is found, construct a bunch of sql statements to insert rows
into table1, table2, table3
3. execute these sql statements

The application has no control over key values, they come from a
different part of the system through MQ. Having two messages with the same
key is normal, it just means one message is for insertion, another to update
what's already there.
There is a certain lag in time between steps 1 and 3, so by the time we
get to step 3, another thread of the same application might have inserted
rows into these same table with the same key. In this case we'll get a DB2
error indicating the primary key violation. We can catch the error and
retry, but this is slowing the processing immensely. Also, the thread that
comes second, will have to sit in lock-wait status for a while, which also
affects performance.

Is there a better way of handling this?

TIA,

Isaac

[Blair Adamache]

You might look at the MERGE statement here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

It's somewhat similar to UPSERT (DB2 has no UPSERT, so MERGE is the way
to go), i.e.

if the row does not exist, insert it
if the row exists, update it (UPdate + inSERT=UPSERT).

Isaac Blank wrote:

Hi.

We've run into a concurrency issue I do not have a clear solution for. In a
DB2 UDB 7.2 database, we have several tables with a chain of foreign key
relarionships:

Table1 primary key x
table2 foreign key x references table1(x)
table3 foreign key x references table2(x)
........................................

Here's the simplified logic one of our application uses (given a value "foo"
for the column x)

1. Check if a row exists in table1 with x='foo'
2. if no row is found, construct a bunch of sql statements to insert rows
into table1, table2, table3
3. execute these sql statements

The application has no control over key values, they come from a
different part of the system through MQ. Having two messages with the same
key is normal, it just means one message is for insertion, another to update
what's already there.
There is a certain lag in time between steps 1 and 3, so by the time we
get to step 3, another thread of the same application might have inserted
rows into these same table with the same key. In this case we'll get a DB2
error indicating the primary key violation. We can catch the error and
retry, but this is slowing the processing immensely. Also, the thread that
comes second, will have to sit in lock-wait status for a while, which also
affects performance.

Is there a better way of handling this?

TIA,

Isaac