Kbase P26296: ORACLE behavior on duplicated keys on pending transactions
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
ORACLE behavior on duplicated keys on pending transactions
GOAL:
Creating 2 new records at the same time within Oracle
FACT(s) (Environment):
Oracle DataServer
FIX:
ORACLE behaves differently from PROGRESS in the following scenario.
Scenario:
Session 1:
CREATE customer.
ASSIGN cust_num = 1.
RELEASE customer. /* This will create the record inside of ORACLE */
PAUSE. /* Transaction still uncommitted. This will hold it */
Session 2:
CREATE customer.
ASSIGN cust_num = 1.
RELEASE customer.
With the PROGRESS database, you will get the following error message
on session 2 immediately:
Customer already exists with cust_num 1. (132)
With the ORACLE database, session 2 will be held until you commit or
rollback session 1. The ORACLE RDBMS is holding PROGRESS up, and this
would happen also with another ORACLE application. If session 1
commits, you will get the usual duplicate unique key in database table
(1443) on session 2, while if session 1 rollsback, session 2 will
succeed creating the record.
If your application relies on trying to create keys that might being
created at the same time from two sessions, beware of this behavior.
This issue also happens if one session is creating a record while
another session is trying to change a key to the same unique value.
The last session to try to get the unique key, will be held until the
first session commits or rollbacks.