SQL adaptation guide For Sybase ASE 15.5+ / BDL programming |
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.
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.