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® ASE

SAP ASE supports SELECT … FOR UPDATE clause in the context of a cursor. This can be achieved by using the CS_FOR_UPDATE option in ct-lib Client-Library ct_cursor() function.

Note: The "select for update" database configuration parameter is by default set to zero, This parameter defines if a single SELECT … FOR UPDATE (at the SQL language level) must set locks. This parameter does not need to be 1 when executing

SELECT … FOR
UPDATE

in the context of a SAP ASE ct-lib cursor (ct_cursor() + CS_FOR_UPDATE).

Rows selected with SELECT … FOR UPDATE, within or outside of a cursor context, retain an exclusive lock until the transaction is complete.

SAP ASE's locking granularity is at the row level, page level or table level (the level is automatically selected by the engine for optimization, depending on the locking scheme).

Solution

SELECT FOR UPDATE statements are supported with SAP ASE under some conditions:

SAP ASE requires a PRIMARY KEY or UNIQUE INDEX on the table using in the SELECT … FOR UPDATE statement.

When the SAP ASE database driver must execute a SELECT FOR UPDATE, the ct-lib ct_cursor() function is called with the CS_FOR_UPDATE option.

Locks are acquired when fetching rows with the cursor. Locks are released when the transaction ends or when the cursor is closed.

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