Kbase 16612: ORACLE Behavior: Duplicated Unique Keys Simultaneously
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/02/2005 |
|
Status: Unverified
GOAL:
An explanation of ORACLE behavior, inconsistent with the PROGRESS database, that might affect ORACLE DataServer applications.
FACT(s) (Environment):
Oracle DataServer
FIX:
Related error: ORA-00001 Unique constraint (constraint-name) violated.
If you create a record with a unique key value K, on session A, and before session A commits, session B tries to create a record with the same unique key, 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 INSERT statement will complete successfully.
For example, consider two SQL*Plus session connected to ORACLE.
On session A: insert into customer(cust_num) values(10000);
On session B: insert into customer(cust_num) values(10000);
Notice that session B is hung at this point. If run commit on session A, then session B will error out "constraint violation ...". However, if you run rollback on session A, session B will complete the INSERT statement successfully.