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.

Sybase ASE ignores the FOR UPDATE clause when not used in a native Sybase SQL DECLARE command. In order to lock rows when doing a SELECT, with Sybase you must add the holdlock hint or the at isolation repeatable read clause. Sybase supports SELECT locking outside transactions (i.e. WITH HOLD cursors).

Locks are acquired when opening the cursor.
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.

Sybase ASE'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 WAIT. SET LOCK MODE is as an Informix specific SQL statement which is translated by the driver.

Solution

SELECT FOR UPDATE statements are supported: The Sybase ASE driver adds the "at isolation repeatable read" keywords to the end of any SELECT FOR UPDATE statement.

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

Sybase ASE locks the rows when you open the cursor. You will have to test SQLCA.SQLCODE after doing an OPEN.

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.