SELECT … FOR UPDATE
Informix®
Legacy BDL programs typically use a cursor with SELECT FOR UPDATE
to implement
pessimistic locking and avoid several users editing the same rows:
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.
SET LOCK MODE
instruction to define the lock wait
timeout:SET LOCK MODE TO {
WAIT |
NOT WAIT |
WAIT seconds }
The
default mode is NOT WAIT
.ORACLE
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.
Locks are not released when a cursor is closed.
ORACLE's locking granularity is at the row level.
NOWAIT
keyword can be used in SELECT ... FOR UPDATE
statement, the return immediately if the row is already locked by another
user:SELECT ... FOR UPDATE [
OF col-list ]
NOWAIT
Solution
Cursors declared with SELECT ... FOR UPDATE
using the
WITH HOLD
clause cannot be supported with ORACLE.
The database interface is based on an emulation of an Informix engine using transaction logging.
Opening a SELECT ... FOR UPDATE
cursor declared outside a transaction will
raise an SQL error -255 (not in transaction).
When using pessimistic locking with DECLARE ... CURSOR FOR SELECT ...
FOR UPDATE
, review the program logic to have OPEN
and
CLOSE
instructions inside transactions (BEGIN WORK
+
COMMIT WORK
/ ROLLBACK WORK
).
See also Cursors with Hold and UPDATE/DELETE WHERE CURRENT OF for more details.