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:

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.