469,323 Members | 1,460 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

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 3273
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.