Deadlock with Inserts into Unique/Primary Key

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.

I would try to commit more often, inside your loop -start by commiting every 10 rows inserted and then test making insert batch larger until you are comfortable.

Quote:

---

Originally Posted by PAVB

I would try to commit more often, inside your loop -start by commiting every 10 rows inserted and then test making insert batch larger until you are comfortable.

---

I think it is very unlikely that committing more or less often is going to change anything.

You should try using hash partitioning with a lot of partitions. this will make more of a random distribution of values across a larger number of partitions, so it is unlikely that one part of the primary key is going to raise a locking issue.

You should also make sure that the table allows parallel transactions, and verify that this isn't a trigger causing the locking issue.

Quote:

---

Originally Posted by gandolf989

I think it is very unlikely that committing more or less often is going to change anything.

---

Have you tested it? I did.

Quote:

---

Originally Posted by PAVB

Have you tested it? I did.

---

I haven't tested it in Moakes database no. That you tested something in one of your databases and come to a conclusion is an anecdotal which may or may not have any comparison to what is happening here.

i did a quick search on http://asktom.oracle.com and found the following links that seem relevant. I realize that Moakes said he does not have a bitmaped index, but there are probably a few things that if only we knew we would have different suggestions on how to resolve the issue.

I still think that my solution is a practical alternative to more frequent commits, or living with deadlocks. Disagree if you like. ;)

Commits don't help performance.
http://asktom.oracle.com/pls/asktom/...:4951966319022

Bitmaps can cause deadlocks
http://asktom.oracle.com/pls/asktom/...:6042872196732

Sorry Gandolph, but I must agree with PAVB on this one.

Not that I am arguing with the great Tom Kyte, however. What he is saying is that committing, in and of itself, has absolutely no impact on performance. This, of course, is perfectly correct. However, in this case, we are not strictly talking about stand-alone performance, but rather multiple threads that are tripping over each other's locks.

Similarly, while Bitmap indexes are notorious for deadlocks (because they lock multiple rows), we can at least say that the example provided certainly had no bitmap indexes. So, let's assume for now that there are, as the poster says, no bitmap indexes.

What we have are multiple processes running concurrently that are trying to insert possibly the same records into the same table. And, most importantly, in a random order.

There are a few ways to alleviate this issue:

1 - Commit every record.
Nothing less will guarantee the elimination of the dealocks. As your example showed, all you need are a few inserts to cause a deadlock. Strictly speaking, all you need are two. Therefore, the only way to eliminate deadlocks by using commits is to commit every record. This has other implications, of course. First of all, doing a commit on every record will make the process somewhat slower. However, even without the deadlocks, you can easily be waiting on locks in many of your concurrent processes. If two processes have thousands of records in their batch and both try to insert record # 4 at the beginning of the batch, whichever one loses will be waiting until the winner finishes inserting the rest of the thousands of records in its batch before it can continue. So, performance without commits may well be much worse than the performance with commits.

2 - Order the INSERTS
The biggest problem here, as shown in your example, is that one process is trying to lock A, then B, while another process is trying to lock B, then A. If you order your inserts before they are performed (various ways to programatically accomplish this), then you could eliminate any possible deadlocks, although you may still experience long waits as described in solution 1.

3 - Insert into a staging table first.
This is an all-around better solution. This table would not have to have a unique index on the key column. - just insert away. Every once in a while, move the records into the real table. This can be done in multiple ways, such as:
a - Have a single process that runs every 5 minutes. It updates all the records that it currrently sees (committed) with some flag. It then does an INSERT INTO SELECT * FROM ST WHERE NOT EXISTS ( SELECT 1 FROM RT where RT.KEY = ST.KEY) followed by deleting all the records from the staging table that it previously flagged
b - Same as above, but have an error table that all the duplicates get moved to - saves the EXISTS()
c - Same as above, but with a MERGE, again saving on the EXISTS
There are varous ways this can be accomplished, but the idea is to remove the waits, the locks, and the deadlock possibility. You can also do direct-path inserts this way, which are must faster.

Anyway, there are some thoughts for you - let me know what you find.