Read an Excerpt
Chapter 11: Retrieving Results (Advanced)
...The ability to move backward in a result data set raises an important question: Should the cursor detect changes made to rows previously fetched? In other words, should it detect updated, deleted, and newly inserted rows? This question arises because the definition of a result data set (that is, the set of rows matching certain criteria) does not state when rows are checked to see if they match that criteria, nor does it state whether rows must contain the same data each time they are fetched. The former omission makes it possible for scrollable cursors to detect whether rows have been inserted or deleted, while the latter makes it possible for them to detect updated data.
To cover the needs of different applications, ODBC defines the following four different types of scrollable cursors:
- Static
- Dynamic
- Keyset-driven
- Mixed
Each of these cursors vary both in their expense and in their ability to detect changes made to the result data set: Static cursors detect few or no changes but are relatively cheap to implement. Dynamic cursors detect all changes but are expensive to implement. Keyset-driven and mixed cursors lie somewhere in between, detecting most changes but at less expense than dynamic cursors.
Static Cursors
A static cursor is a cursor in which the result data set appears to be static. That's because static cursors do not usually detect changes made to the result data set after the cursor is opened. For example, suppose a static cursor fetches a row of data from a result data set. Now suppose another application then updates that row. If the static cursorrefetches the row, it does not see the changes made by the other application. Static cursors never detect inserts, updates, and deletes made by other applications; however, static cursors may detect their own inserts, updates, and deletes, although they are not required to do so. This type of cursor is most useful for read-only applications that do not need the most up-to-date data available or for applications in which multiple users never need to modify data concurrently.
Static cursors are commonly implemented by locking the rows in the result data set or by making a copy, or snapshot, of the result data set. While locking rows is relatively easy to do, the drawback of this approach is that it significantly reduces transaction concurrency. Making a copy or a snapshot of a result data set allows greater concurrency and provides the cursor with a way to keep track of its own inserts, updates, and deletes by modifying the copy. However, a copy is more expensive to make and can differ from the underlying data as that data is changed by other applications.
Dynamic Cursors
Dynamic cursors can detect changes made to the result data set after the cursor is opened. For example, suppose a dynamic cursor fetches two rows of data from a result data set. Now suppose another application then updates one of those rows and deletes the other one. If the dynamic cursor attempts to refetch the rows, it returns the new values for the updated row and does not return the deleted row.
Dynamic cursors detect their own inserts, updates, and deletes as well as inserts, updates, and deletes made by other applications. (This is subject to the isolation level of the transaction, as defined by the SQL_ATTR_TXN_ISOLATION connection attribute.)
Keyset-Driven Cursors
A keyset-driven cursor is a cursor that lies somewhere between a static and a dynamic cursor in its ability to detect changes. Like static cursors, keyset-driven cursors do not always detect changes to the set of rows in the result data set or to the order in which rows are returned. Like dynamic cursors, keyset-driven cursors are able to detect changes to row values in the result data set (dependent upon the transaction isolation level being used). The advantage of using this type of cursor is that it enables access to the most up-to-date values and it allows an application to fetch rows based on absolute position within a result data set.
When a keyset-driven cursor is opened, it saves keys (unique row identifiers) for the entire result data set. A key can be a row ID (if available), a unique index, a unique key, or the entire row. As the cursor scrolls through the result data set, it uses the keys in this keyset to retrieve the current data values for each row. Because of this, keyset-driven cursors always detect their own updates and deletes as well as updates and deletes made by other applications. For example, suppose a keyset-driven cursor fetches a row of data from a result data set. Now suppose another application then updates that row. If the keyset-driven cursor refetches the row, it sees the changes made by the other application, because it refetched the row using its key. Figure 11-4 shows the components of a keyset-driven cursor.
When a keyset-driven cursor attempts to refetch a row that has been deleted, this row appears as a hole in the result set: The key for the row exists in the keyset but the row no longer exists in the result data set. If the key for a row is updated, the update is treated as if the original row was deleted and a new row was inserted. Thus, these rows also appear as holes in the result data set. While a keyset-driven cursor can always detect rows deleted by others, it can optionally remove the keys for rows it deletes itself from the keyset, thereby hiding its own deletes.
Rows inserted by other applications are never visible to keyset-driven cursors. That's because no keys for these rows exist in the keyset. However, a keyset-driven cursor can optionally add the keys for rows it inserts itself to the keyset. Keyset-driven cursors that do this can detect their own inserts.
Keyset-driven cursors are commonly implemented by creating a temporary table containing the keys and the row versioning information for each row in the result data set. To scroll through the original result data set, the keyset-driven cursor opens a static cursor over the temporary table. To retrieve a row in the original result data set, the keyset-driven cursor first retrieves the appropriate key from the temporary table, then it retrieves the current values for the row. If block cursors are used, the cursor must retrieve multiple keys and rows.
Mixed Cursors
A mixed cursor is a combination of a keyset-driven cursor and a dynamic cursor. Mixed cursors are used when the result data set is too large to reasonably save keys for. Mixed cursors are implemented by creating a keyset that is smaller than the entire result set but larger than the rowset. As long as the application scrolls within the keyset, the behavior is the same as a keyset-driven cursor. When the application scrolls outside the keyset, the behavior becomes dynamic-the cursor fetches the requested rows and creates a new keyset. After the new keyset is created, the behavior reverts to keyset-driven within that keyset.
For example, suppose a result set has 1,000 rows and uses a mixed cursor with a keyset size of 100 and a rowset size of 10. When the first rowset is fetched, the cursor creates a keyset consisting of the keys for the first 100 rows. It then returns the first 10 rows, as requested.
Now suppose another application deletes rows 11 and 101. If the cursor attempts to retrieve row 11, it encounters a hole because it has a key for this row but no row exists; this is keyset-driven behavior. If the cursor attempts to retrieve row 101, the cursor does not detect that the row is missing because it does not have a key for the row. Instead, it retrieves what was previously row 102. This is dynamic cursor behavior.
A mixed cursor is equivalent to a keyset-driven cursor when the keyset size is equal to the result data set size. A mixed cursor is equivalent to a dynamic cursor when the keyset size is equal to 1. Figure 11-5 shows the components of a mixed cursor....