SQL adaptation guide For Oracle Database 9.2, 10.x, 11.x, 12x / BDL programming |
A lot of BDL programs use pessimistic locking in order to prevent several users editing the same rows at the same time.
DECLARE cc CURSOR FOR SELECT ... FROM tab WHERE ... FOR UPDATE OPEN cc FETCH cc <-- lock is acquired ... CLOSE cc <-- lock is released
ORACLE allows individual and exclusive row locking with:
SELECT ... FOR UPDATE [OF col-list]
ORACLE's locking granularity is at the row level.
To control the behavior of the program when locking rows, Informix® provides a specific instruction to set the wait mode:
SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }
The default mode is NOT WAIT. This is an Informix specific-SQL statement.
In order to simulate the same behavior in ORACLE, your can use the NOWAIT keyword in the SELECT ... FOR UPDATE statement, as follows:
SELECT ... FOR UPDATE [OF col-list] NOWAIT
With this option, ORACLE immediately returns an SQL error if the row is locked by another user.
The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).
Cursors declared with SELECT ... FOR UPDATE using the "WITH HOLD" clause cannot be supported with ORACLE. See Cursors with Hold and UPDATE/DELETE WHERE CURRENT OF for more details.
If your BDL application uses pessimistic locking with SELECT ... FOR UPDATE, you must review the program logic for OPEN cursor and CLOSE cursor statements inside transactions (BEGIN WORK + COMMIT WORK / ROLLBACK WORK).