Use Cursors in Transactions

By default, SAP ASE does not change a cursor’s state (open or closed) when a transaction ends through a commit or rollback. However, SQL standards associate an open cursor with its active transaction; committing or rolling back that transaction automatically closes any open cursors associated with it.

To enforce this SQL-standards-compliant behavior, SAP ASE provides the close on endtran option of the set command. In addition, if you set chained mode to on, SAP ASE starts a transaction when you open a cursor and closes that cursor when the outermost transaction is committed or rolled back.

For example, by default, this sequence of statements produces an error:

open test_crsr
commit tran
open test_crsr

If you set either the close on endtran or chained options to on, the cursor’s state changes from open to closed after the outermost transaction is committed. This allows the cursor to be reopened.

Note: Since client application buffer rows are returned through cursors, and allow users to scroll within those buffers, those client applications should not scroll backward after a transaction aborts. The rows in a client cache may become invalid because of a transaction rollback (unknown to the client) that is enforced by the close on endtran option or the chained mode.

Any exclusive locks acquired by a cursor in a transaction are held until the end of that transaction. This also applies to shared locks when you use the holdlock keyword, the at isolation serializable clause, or the set isolation level 3 option.

The following rules define the behavior of updates through a cursor with regard to transactions:

An update occuring within an explicit transaction is considered part of the transaction. If the transaction commits, any updates included with the transaction also commit. If the transaction aborts, any updates included with the transaction are rolled back. Updates through the same cursor that occurred outside the aborted transaction are not affected.
If updates through a cursor occur within an explicit (and client-specified) transaction, SAP ASE does not commit them when the cursor is closed. It commits or rolls back pending updates only when the transaction associated with that cursor ends.
A transaction commit or abort has no effect on SQL cursor statements that do not manipulate result rows, such as declare cursor, open cursor, close cursor, set cursor rows, and deallocate cursor. For example, if the client opens a cursor within a transaction, and the transaction aborts, the cursor remains open after the abort (unless close on endtran is set or chained mode is used).

However, if you do not set the close on endtran option, the cursor remains open past the end of the transaction, and its current page lock remains in effect. It may also continue to acquire locks as it fetches additional rows.