471,887 Members | 1,330 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

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

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

Nov 12 '05 #1
1 3314
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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by iaesun | last post: by
20 posts views Thread by Dean Stevens | last post: by
9 posts views Thread by Paul Steele | last post: by
2 posts views Thread by Diego | last post: by
6 posts views Thread by planetthoughtful | last post: by
8 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.