Oracle merge statement not multithreaded

Question

Just looking for some comfirmation here. We are attempting to use the merge statement for two tables called from C# thru stored procs. the calls are made from seperate threads and/or processes. We are getting unique key violations on the two columns that also make up the on statement.

Our theory for this is that the statements are happening concurrently with the same data and the merge statement isn't multithread safe. Anyone else have or see this issue before?

Any thoughts on solutions for this issue?

Dan

Answer 1

Oracle has nothing to do with the threads and processes on the calling side.

Instead, it implements it own locking mechanisms which prevent concurrent updates from two logical sessions (which may or may not be initiated by different threads, processes and even machines).

The key violations you see are the result of (probably correct) work of these mechanisms.

If you post the query you run, I probably will be able to give a more specific answer.