ORA-00001 (unique key violation) or a lock, lately followed by ORA-00001.

[Arjen Visser]

Issue
The user on source tried to insert (update) a duplicate value on the source database. This resulted either in direct ORA-00001 (unique key violation) or a lock, lately followed by ORA-00001.

Description
The issue stems from the fact that Oracle first updates/inserts the row in the table, then it goes on to check it against the constraint whether the row is valid. For a unique/primary key violation, such check can result in immediate ORA-00001 error or a lock (enqueue) on the index. The second case happens when the same primary key value is present in the index, but not yet committed. The outcome of this other session determines fate of the lock - it's rollback results in success of the first session, it's commit results in ORA-00001 again. And as usual, an error results in the statement to be rolled back.

The trouble with replication is that the row is changed in the table before the locks are sorted out; if Oracle throws the error immediately, Replicate detects that and skips the change completely. However, the lock can be held arbitrary long (hours, even days), so apply can apply the change while the lock is still in place on the source, thus apply cannot know what will the outcome be. Apply does not halt while waiting for the lock, it tries to go on with changes from other sessions.
This issue is being addressed by Replicate development and the detection should become more successful in future versions, but it will probably still be present for extreme/border cases.

The solution is to either ignore the error or better, to change the constraint to be deferred. The deferred constraint checks the unique/primary key values only on commit and by that time should the statement rollbacks be sorted out and the values should satisfy the constraint.
Note however, that using deferred constraints has it's ramifications (e.g. on other applications that may rely on getting the ORA-00001 error) and it thus should be implemented only after thoughtful analysis.

[Arjen Visser]

Here is another example of how this works.

On our source database:

cat avi_cr_tab_customer.sql
drop table customer;
create table customer (id number primary key, name varchar2(10));
insert into customer values(1,'toto');
insert into customer values(2,'toto');
insert into customer values(3,'titi');
commit;
insert into customer values(4,'titi');
insert into customer values(4,'titi');
commit;

sqlplus avi/avi

SQL> @avi_cr_tab_customer.sql

Table dropped.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.


1 row created.

insert into customer values(4,'titi')
*
ERROR at line 1:
ORA-00001: unique constraint (AVI.SYS_C0018856) violated

Commit complete.

We now have a unique constraint violation because the primary key ID = 4 has already been inserted.

This is what is now in our source database:

SQL> select * from customer;
ID NAME
---------- ----------
1 toto
2 toto
3 titi
4 titi


If we select from this table on target we see that there are only 3 rows:
On target:

SQL> select * from customer;
ID NAME
---------- ----------
1 toto
2 toto
3 titi


There are only 3 rows, because the last row where ID=4 was inserted twice has been picked up as a conflict and therefore not committed yet on the target.

If we look at the Dbvisit Replicate Console we can see the conflict. We will ignore this conflict as IGNORE
dbvrep> resolve conflict 2560010006753 as ignore
Conflict resolution set.

Now we see that we have all the rows on the target:
SQL> select * from customer;
ID NAME
---------- ----------
1 toto
2 toto
3 titi
4 titi

Explanation:
When the second insert is executed on source, Oracle actually writes it to the redo and then rolls it back. Because of the real time replication, the apply process is not always able to detect this and not run the second insert and that is why the insert is executed and causing a conflict.

In these cases it is safe to ignore the statement as it gets rolled back immediately anyway.

Dbvisit Replicate will improve the detection of these cases so that conflicts will not appear in future.