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.

IBM® DB2®

IBM DB2 locks are released when closing the cursor or when the transaction ends.

IBM DB2's locking granularity is at the row level.

IBM DB2 has no equivalent for SET LOCK MODE TO NOT WAIT: The system lock timeout parameter can be changed, but this is a database parameter, global to all SQL processes.

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).

When used, you must review pessimistic locking implementation, because it is based on the NOT WAIT mode, which is not supported by IBM DB2.