Cursors and Isolation Levels

SAP ASE provides three isolation levels for cursors.

Isolation level 2 is not supported for cursors.

Besides using holdlock for isolation level 3, you can use set transaction isolation level to specify any of the four isolation levels for your session. When you use set transaction isolation level, any cursor you open uses the specified isolation level, unless the transaction isolation level is set at 2. In this case, the cursor uses isolation level 3. You can also use the select statement’s at isolation clause to specify isolation level 0, 1, or 3 for a specific cursor. For example:

declare commit_crsr cursor
for select *
from titles
at isolation read committed

This statement makes the cursor operate at isolation level 1, regardless of the isolation level of the transaction or session. If you declare a cursor at isolation level 0 (read uncommitted), SAP ASE also defines the cursor as read-only. You cannot specify the for update clause along with at isolation read uncommitted in a declare cursor statement.

SAP ASE determines a cursor’s isolation level when you open the cursor (not when you declare it), based on the following:

SAP ASE compiles the cursor’s query when you declare it. This compilation process is different for isolation level 0 as compared to isolation levels 1 or 3. If you declare a language or client cursor in a transaction with isolation level 1 or 3, opening it in a transaction at isolation level 0 causes an error.

For example:

set transaction isolation level 1
declare publishers_crsr cursor
    for select *
    from publishers
open publishers_crsr     /* no error */
fetch publishers_crsr
close publishers_crsr
set transaction isolation level 0
open publishers_crsr     /* error */