SQL Anywhere cursors

Any cursor, once opened, has an associated result set. The cursor is kept open for a length of time. During that time, the result set associated with the cursor may be changed, either through the cursor itself or, subject to isolation level requirements, by other transactions. Some cursors permit changes to the underlying data to be visible, while others do not reflect these changes. A sensitivity to changes to the underlying data causes different cursor behavior, or cursor sensitivity.

SQL Anywhere provides cursors with a variety of sensitivity characteristics. This section describes what sensitivity is, and describes the sensitivity characteristics of cursors.

This section assumes that you have read Cursors.

Membership, order, and value changes

Changes to the underlying data can affect the result set of a cursor in the following ways:

Membership The set of rows in the result set, as identified by their primary key values.
Order The order of the rows in the result set.
Value The values of the rows in the result set.

For example, consider the following simple table with employee information (EmployeeID is the primary key column):

EmployeeID	Surname
1	Whitney
2	Cobb
3	Chin

A cursor on the following query returns all results from the table in primary key order:

SELECT EmployeeID, Surname
FROM Employees
ORDER BY EmployeeID;

The membership of the result set could be changed by adding a new row or deleting a row. The values could be changed by changing one of the names in the table. The order could be changed by changing the primary key value of one of the employees.

Visible and invisible changes

Subject to isolation level requirements, the membership, order, and values of the result set of a cursor can be changed after the cursor is opened. Depending on the type of cursor in use, the result set as seen by the application may or may not change to reflect these changes.

Changes to the underlying data may be visible or invisible through the cursor. A visible change is a change that is reflected in the result set of the cursor. Changes to the underlying data that are not reflected in the result set seen by the cursor are invisible.

Cursor sensitivity
Cursor sensitivity example: A deleted row
Cursor sensitivity example: An updated row
Insensitive cursors
Sensitive cursors
Asensitive cursors
Value-sensitive cursors
Cursor sensitivity and performance
Cursor sensitivity and isolation levels
Requests for SQL Anywhere cursors

Discuss this page in DocCommentXchange.