Adaptive Server provides three isolation levels for cursors:
Level 0 – Adaptive Server uses no locks on base table pages that contain a row representing a current cursor position. Cursors acquire no read locks for their scans, so they do not block other applications from accessing the same data. However, cursors operating at this isolation level are not updatable, and they require a unique index on the base table to ensure the accuracy of their scans.
Level 1 – Adaptive Server uses a shared or update lock on base table pages that contain a row representing a current cursor position. The page remains locked until the current cursor position moves off the page (as a result of fetch statements), or the cursor is closed. If an index is used to search the base table rows, it also applies shared or update locks to the corresponding index pages. This is the default locking behavior for Adaptive Server.
Level 3 – Adaptive Server uses a shared or update lock on any base table pages that have been read in a transaction on behalf of the cursor. In addition, the locks are held until the transaction ends, as opposed to being released when the data page is no longer needed. The holdlock keyword applies this locking level to the base tables, as specified by the query on the tables or views.
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), Adaptive Server 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.
Adaptive Server determines a cursor’s isolation level when you open the cursor (not when you declare it), based on the following:
If the cursor was declared with the at isolation clause, that isolation level overrides the transaction isolation level in which it is opened.
If the cursor was not declared with at isolation, the cursor uses the isolation level in which it is opened. If you close the cursor and reopen it later, the cursor acquires the current isolation level of the transaction.
Adaptive Server 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 */
