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.

Informix provides the 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).

SAP HANA's locking granularity is at the row level.

Note:

When using a cursor declared with a SELECT FOR UPDATE, all rows of the result set will get an exclusive lock when the cursor is opened.

SAP HANA has and equivalent for Informix

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.

Important:

Since SAP HANA sets locks when the cursor is opened, it is not possible to get the Informix behavior, where locks are set at FETCH time.