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 

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

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.