Kbase P101817: Why error "ORA-00001 Unique constraint violated" can occur?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
Why error "ORA-00001 Unique constraint violated" can occur?
FACT(s) (Environment):
Oracle DataServer
FIX:
Find below a scenario where Oracle user will hit error "ORA-00001 Unique constraint <constraint name> violated".
1. Create a record with a unique key value K, on session A.
2. Before session A commits, create a record with the same unique key, on another session B.
Session B won't error out immediately, but rather it will be held until session A either commits or rollback the transaction.
If session A commits, then session B will get the following message:
ORA-00001 Unique constraint (constraint-name) violated
If session A rolls back, session B's INSERT statement will complete successfully.
To test the above, start two SQL*Plus sessions connected as the same user and with autocommit OFF.
Run on session A: insert into customer(cust_num) values(10000);
Run on session B: insert into customer(cust_num) values(10000);
Notice that session B is hung at this point. If "commit;" is run on session A, then session B will error out "constraint violation ...". If "rollback;" is run on session A, then session B will complete the INSERT statement successfully.