I have at a minimum 2 processes that are both doing bulk inserts into a table with a unique/primary key. Periodically throughout the day I will get dead lock situations. I do not have any bitmap indexes and very little ITL waits. I'm pretty confident the issue is caused because of contention of a primary unique key index. I do not have any other tables referencing this table. (no foreign keys). I only use the primary key as a mechanism to eliminate duplicates during the load process.

The question is how do I code for avoiding this deadlock situation?. It is a requirement that we have multiple processes running so that we can keep up with loading data. Basically I do a for loop and insert row by row (could be a couple thousand records at a time) Multiple processes are doing same inserts committing at the end of all inserts. I have no control over the primary key values. They are generated by the source and send alot of duplicate records. We use the primary key as a constraint to eliminate duplicates.

I can easily re-create the issue by the following

create table test_lock (id number(10));
alter table test_lock add primary key (id);

-- In session 1
insert into test_lock values (1);
insert into test_lock values (2);
insert into test_lock values(3);
insert into test_lock values (4);

--In session 2
insert into test_lock values (4);
insert into test lock values (3);

--At this point one of the sessions gets a deadlock.

I was thinking about doing a "lock table test_lock in exclusive mode;" prior
to executing each session but not sure if this is the best solution. I want the best performance and want to continue to actually run concurrently as much as possible. I cannot do major changes to the code base so want to try and work with what I have as much as possible.

I appreciate any thoughts.