SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.

DECLARE cc CURSOR FOR
SELECT ... FROM tab WHERE ... FOR UPDATE
OPEN cc
FETCH cc <-- lock is acquired
...
CLOSE cc <-- lock is released

A transaction must be started before opening cursors declared for update.
The row must be fetched in order to set the lock.
The lock is released when the transaction ends (if the cursor is not declared "WITH HOLD") or when the cursor is closed.

Microsoft™ SQL SERVER allows individual and exclusive row locking by using the (UPDLOCK) hint after the table names in the FROM clause:

SELECT ... FROM tab1 WITH (UPDLOCK) WHERE ...

The FOR UPDATE clause is not mandatory; the (UPDLOCK) hint is important.

Individual locks are acquired when fetching the rows.
When the cursor (WITH HOLD) is opened outside a transaction, locks are released when the cursor is closed.
When the cursor is opened inside a transaction, locks are released when the transaction ends.

SQL SERVER's locking granularity is at the row level, page level or table level (the level is automatically selected by the engine for optimization).

To control the behavior of the program when locking rows, Informix^® provides a specific instruction to set the wait mode:

SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This as an Informix specific SQL statement.

Solution

The SQL SERVER database driver for MS SQL SERVER uses the SCROLL LOCKS concurrency options for cursors (SQL_ATTR_CONCURRENCY = SQL_CONCUR_LOCK).

This option implements pessimistic concurrency control, in which the application attempts to lock the underlying database rows at the time they are read into the cursor result set.

When using server cursors, an update lock is placed on the row when it is read into the cursor. If the cursor is opened within a transaction, the transaction update lock is held until the transaction is either committed or rolled back; the cursor lock is dropped when the next row is fetched.

If the cursor has been opened outside a transaction, the lock is dropped when the next row is fetched.

Therefore, a cursor should be opened in a transaction whenever the user wants full pessimistic concurrency control.

An update lock prevents any other task from acquiring an update or exclusive lock, which prevents any other task from updating the row.

An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock.

SELECT FOR UPDATE statements are well supported in BDL as long as they are used inside a transaction. Avoid cursors declared WITH HOLD.

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

The SELECT FOR UPDATE statement cannot contain an ORDER BY clause if you want to perform positioned updates/deletes with WHERE CURRENT OF.

Cursors declared with SELECT ... FOR UPDATE using the "WITH HOLD" clause cannot be supported with SQL SERVER.

You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by SQL SERVER.