In versions earlier than 15.7, Adaptive Server closes a cursor declared with a for update clause and opened within a transaction context, if
you explicitly commit the transaction before closing the cursor
set close on endtran is set
See “Using select for update in cursors and DML”.
In version 15.7 and later, and when select for update is set, Adaptive Server supports fetch operations on open cursors after the transaction has been committed.
When you open a cursor, Adaptive Server uses different locking mechanisms based on the transaction mode:
Chained mode – Adaptive Server implicitly starts a transaction and uses exclusive locks for fetched rows. If you commit a transaction after a fetch, a subsequent fetch command starts a new transaction. Adaptive Server continues to use exclusive locks for fetched rows in the new transaction.
Unchained mode – Adaptive Server uses exclusive locks only if you execute an explicit begin tran statement before opening the cursor. Otherwise, Adaptive Server acquires update row locks on fetched rows and displays a warning that exclusive locks are not acquired for subsequently fetched rows.
When you execute a commit between two fetch commands, or between closing and reopening a cursor, Adaptive Server releases all exclusive locks. For subsequent fetch commands, Adaptive Server acquires locks based on the transaction mode:
Chained mode – Adaptive Server acquires exclusive row locks on fetched rows. Adaptive Server may also acquire update row locks in certain non-optimized conditions.
Unchained mode – Adaptive Server acquires update row locks on fetched rows. If the fetch commands are preceded by begin tran, Adaptive Server acquires exclusive row locks.
If update row locks are acquired, Adaptive Server releases them only when:
The cursor is closed – at isolation levels 2 and 3.
The cursor moves to the next row – at isolation level 1.
