473,700 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3376
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
3876
by: * ProteanThread * | last post by:
but depends upon the clique: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=954drf%24oca%241%40agate.berkeley.edu&rnum=2&prev=/groups%3Fq%3D%2522cross%2Bposting%2Bversus%2Bmulti%2Bposting%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den ...
0
3777
by: frankenberry | last post by:
I have multi-page tiff files. I need to extract individual frames from the multi-page tiffs and save them as single-page tiffs. 95% of the time I receive multi-page tiffs containing 1 or more black and white CCITT4 compressed files (frames) inside the tiff. Every now and then I receive a mixture of black and white CCITT4 and JPEG compressed files, and sometimes just multi-page tiffs with JPEG only. The code runs great when dealing with the...
9
1842
by: iaesun | last post by:
i'm trying to write a batch that will perform a complex task using set-based selects instead of a row-based cursor. let me know if you can help me figure out how. description of what i'm trying to do: there is TABLE1, TABLE2, and TABLE3 i want to select each row from TABLE1, do some analysis on the data of that row, and then perform an insert of some data into TABLE2, and some
44
4723
by: Carlos Andr?s | last post by:
Hi everybody. I've got a problem. I'd like to avoid opening a new window when you have pressed the shift key and you click in the left button of the mouse. I've tried the next solution, in the body of the page I put the next code: <BODY onkeydown='notOpenNewWindow();'>
20
10853
by: Dean Stevens | last post by:
I have two processes: one holds a semaphore and the other waits for the semaphore. When the process which holds the semaphore is dead, the deadlock occurs. My question is there is anyway (in semaphore or operating systme) to avoid such situation? Thanks!
9
37192
by: Paul Steele | last post by:
I am writing a C# app that needs to periodically poll for cdroms and usb storage device insertions. I've looked at the WMI functions but haven't found anything all that useful. The closest is Win32_DiskDrive, but it doesn't seem to return any information on cdrom devices. I suspect there might be a Win32 API call, but I haven't found any info yet using Google. Is there a way to achieve this in C#?
2
2935
by: Diego | last post by:
Hi everybody! I'm using DB2 PE v8.2.3 for linux. I've defined a database with the following schema: ANNOTATION(ID,AUTHOR,TEXT) ANNOTATION_BOOK(ANNOTATION_ID,OBJECT_ID) BOOK(ID,AUTHOR,TITLE). Between the book and annotation entities there is a many-to-many
6
6521
by: planetthoughtful | last post by:
Hi All, I have a C# ASP.NET page that submits back to itself to insert details from a form into a database table. When / if the user refreshes the page (and gets the standard warning that POST data will be resubmitted), the previously submitted record is sumbitted again, and a duplicate record is inserted into the table. In PHP I would have avoided this by submitting the form to a processing page, which would then automatically...
2
2814
by: GS | last post by:
How can one avoid capturing leading empty or blank lines? the data I deal with look like this "will be paid on the dates you specified. xyz supplier amount: $100.52 when: September 07, 2007 reference #: 0415 from: operating account
8
1536
by: lenygold via DBMonster.com | last post by:
What about using delete/insert instead of update? Lennart wrote: -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1
0
8718
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8642
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9206
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7802
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5899
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4652
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3083
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2383
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.