SQL adaptation guide For Genero db 3.6x, 3.8x / BDL programming |
Many BDL programs implement pessimistic locking in order to prevent several users from 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
Genero db allows individual and exclusive row locking with:
SELECT ... FOR UPDATE [OF column-list]
Genero db locking granularity is at the row level.
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 is an Informix-specific SQL statement.
Genero db supports SELECT .. FOR UPDATE as in Informix, but the rows are locked when the cursor is opened, not when the first row is fetched.
Ensure that the use of 'FOR UPDATE' is always inside a transaction.
Ensure that you COMMIT the transaction as soon as possible to prevent rows being locked longer than necessary.