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
.SAP HANA®
SAP HANA supports SELECT FOR UPDATE
.
SAP HANA releases locks when the transaction ends (at
COMMIT WORK
or ROLLBACK WORK
).
SELECT FOR UPDATE
, all rows of the result set will get an
exclusive lock when the cursor is opened.SET
LOCK MODE TO NOT WAIT
:
SET TRANSACTION LOCK WAIT TIMEOUT milliseconds
Solution
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).
If the program is doing a SET LOCK MODE
instruction, it
will be converted to a SET TRANSACTION LOCK WAIT TIMEOUT
command by the
database interface.
FETCH
time.