Scrollable Cursors and Transaction Isolation

One of the distinguishing characteristics of a certain type of scrollable cursor—static, keyset-driven, or dynamic—is its ability to detect changes made by other operations in the same transaction and by other transactions. Because the transaction isolation level also determines what changes are visible to the cursor, it seems fair to ask what the relationship is between these two.

The transaction isolation level dictates what changes in other transactions might be visible to the cursor while the cursor type dictates which of those changes are actually visible. For example, suppose the transaction containing the cursor is running at the Read Committed isolation level: Committed changes made by other transactions are visible to the cursor’s transaction. However, the cursor sees these only if it is a keyset-driven or dynamic cursor; if it is a static cursor, it cannot see any changes at all.

Note that the transaction isolation level does not affect a cursor’s ability to see its own changes—those made with positioned update or delete statements or through SQLSetPos—or those made by other operations in the same transaction. Whether the cursor can see its own changes depends on the cursor type and how it is implemented. Whether the cursor can see changes made by other operations in the same transaction depends on the cursor type. For more information, see “Scrollable Cursor Types” in Chapter 11, “Retrieving Results (Advanced).”

The following table lists the factors governing the visibility of changes.

Changes made by:

Visibility depends on:

Cursor	Cursor type, cursor implementation
Other statements in same transaction	Cursor type
Statements in other transactions	Cursor type, transaction isolation level

Perhaps this is shown best in the following diagram:

Depending on the application, certain combinations of cursor type and transaction isolation level do not make sense. For example, suppose an online telephone book uses a dynamic cursor to read and display telephone numbers and that a separate application is used to maintain the database of telephone numbers. To be effective, the cursor used to read telephone numbers needs to detect all committed changes to the database. If the transaction containing this cursor is run at the Repeatable Read or Serializable isolation level, the cursor will detect few or no changes and is essentially a slow, expensive static cursor. Instead, the transaction containing the cursor should be run at the Read Committed isolation level.

The following table summarizes the ability of each cursor type to detect changes made by itself, by other operations in its own transaction, and by other transactions. The visibility of the latter changes depends on the cursor type and the isolation level of the transaction containing the cursor.

Self

Own Txn

Othr Txn (RU [a])

Othr Txn (RC [a])

Othr Txn (RR [a])

Othr Txn (S [a])

Static
Insert	Maybe [b]	No	No	No	No	No
Update	Maybe [b]	No	No	No	No	No
Delete	Maybe [b]	No	No	No	No	No
Keyset -driven
Insert	Maybe [b]	No	No	No	No	No
Update	Yes	Yes	Yes	Yes	No	No
Delete	Maybe [b]	Yes	Yes	Yes	No	No
Dynamic
Insert	Yes	Yes	Yes	Yes	Yes	No
Update	Yes	Yes	Yes	Yes	No	No
Delete	Yes	Yes	Yes	Yes	No	No

[a] The letters in parentheses indicate the isolation level of the transaction containing the cursor; the isolation level of the other transaction (in which the change was made) is irrelevant. RU: Read Uncommitted RC: Read Committed RR: Repeatable Read S: Serializable [b] Depends on how the cursor is implemented. Whether the cursor can detect such changes is reported through the SQL_STATIC_SENSITIVITY option in SQLGetInfo.