Thread: Threaded Merge Statements
06-05-08, 14:07 #1Registered User
- Join Date
- May 2008
Unanswered: Threaded Merge Statements
Hey Guys I was wondering if someone could help me with this. I have a table with a primary key (that auto increments) and two more columns as a unique index. Basically I merge data into it to insert new records and do nothing if they already exist (to preserve uniqueness).
I run these queries in a multithreaded environment, and sometimes the merge statements are executed simultaneously, and both try to commit a unique row, where one of them fails to insert a row because of the uniqueness constraint on the second two columns of the table.
If I try to set the table type to be "table" locked instead of "row" locked, the merges time out sometimes.
Here is the query I run:
MERGE INTO table AS p USING ( VALUES (:val1, :val2) ) AS v(val1, val2) ON ( p.val1= v.val1 AND p.val2= v.val2) WHEN NOT MATCHED THEN INSERT (val1,val2) VALUES (v.val1, v.val2)
06-08-08, 04:13 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
What's your DB2 version on which platform?
Which error messages do you get exactly (for both cases, i.e. lock timeout vs. duplicates)?
Do you use auto-commit or not?
Which isolation level is used?
Have you any other special settings (like DB2_SKIP_INSERTED) in the DB2 registry or elsewhere?
Basically, with the highest isolation level you shouldn't have any problems running those statements concurrently over multiple DB2 connections. But given that we do not yet know the above requested details, we cannot give you a more detailed explanation why you see errors.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development