SELECT FOR UPDATE

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

• The row must be fetched in order to set the lock.
• If the cursor is local to a transaction, the lock is released when the transaction ends. If the cursor is declared "WITH HOLD", the lock is released when the cursor is closed.

ORACLE allows individual and exclusive row locking with:

SELECT ... FOR UPDATE [OF col-list]

• A lock is acquired for each selected row when the cursor is opened, before the first fetch.
• Cursors using SELECT ... FOR UPDATE are automatically closed when the transaction ends; Note that locks are not released when a cursor is closed.

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.