Transaction Support for Updatable Cursors

When select for update is set, SAP ASE supports fetch operations on open cursors after the transaction has been committed.

When you open a cursor, SAP ASE uses different locking mechanisms based on the transaction mode:

Chained mode – SAP ASE 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. SAP ASE continues to use exclusive locks for fetched rows in the new transaction.
Unchained mode – SAP ASE uses exclusive locks only if you execute an explicit begin tran statement before opening the cursor. Otherwise, SAP ASE 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, SAP ASE releases all exclusive locks. For subsequent fetch commands, SAP ASE acquires locks based on the transaction mode:

Chained mode – SAP ASE acquires exclusive row locks on fetched rows. SAP ASE may also acquire update row locks in certain non-optimized conditions.
Unchained mode – SAP ASE acquires update row locks on fetched rows. If the fetch commands are preceded by begin tran, SAP ASE acquires exclusive row locks.

If update row locks are acquired, SAP ASE 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.