Home

	Expand Search

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

Join cursor processing and data modifications

Join cursor processing and data modifications

This section describes changes to cursor behavior, beginning with version 11.9.2, that may affect applications that use cursors. It also describes cursor positioning and modification rules for cursors on data-only-locked tables.

Updates and deletes that can affect the cursor position

Two types of deletes and updates can affect the row at the cursor position:

• Positioned deletes and updates, using delete...where current of or update...where current of to change the row at the cursor position

• Searched deletes and updates, that is, any delete or update query that changes a value in the row at the cursor position, but without including a where current of clause

Cursor positioning after a delete or update command without joins

The behavior of a delete or update command to the base table for a cursor on a single-table query depends on the type of modification, the locking scheme of the base table, and whether the clustered index of the base table is affected:

• A positioned delete or a searched delete that deletes the row at the cursor location positions the cursor to the next qualifying row on the table. This is true for both allpages-locked and data-only-locked tables. A subsequent positioned update or delete via this cursor is disallowed until the next fetch is done to position the cursor on the next row that qualifies.

• A searched or positioned update that does not change the position of the row leaves the current position of the cursor unchanged. The next fetch returns the next qualifying row.

• A searched or positioned update on an allpages-locked table can change the location of the row; for example, if it updates key columns of a clustered index. The cursor does not track the row; it remains positioned just before the next row at the original location. Positioned updates are not allowed until a subsequent fetch returns the next row. The updated row may be visible to the cursor a second time, if the row moves to a later position in the search order.

  Data-only-locked tables have fixed row IDs, so expanding updates or updates that affect the clustered key do not move the location of the row. The cursor remains positioned on the row, and the next fetch returns the next qualifying row.

The cursor positioning behavior described above is unchanged from previous releases.

Effects of updates and deletes on join cursors

When a searched or positioned delete is issued on the row at the cursor position of a cursor that includes a join, there can be one of two results:

• Searched deletes close the cursor implicitly. The next fetch returns error 582:

  Cursor 'cursor_name' was closed implicitly because the current cursor position was deleted due to an update or a delete. The cursor scan position could not be recovered. This happens for cursors which reference more than one table. 

• Positioned deletes fail, with error 592:

  The DELETE WHERE CURRENT OF to the cursor 'cursor_name' failed because the cursor is on a join. 

  The cursor remains open, positioned at the same row.

When a searched or positioned update is issued on the join columns of a cursor:

• Searched updates to clustered index keys on allpages-locked tables succeed, but implicitly close the cursor, so the next fetch returns error 582. Searched updates to clustered index keys do not close cursors on data-only-locked tables.

• Positioned updates for all locking schemes, and searched updates that do not change a clustered index key on an allpages-locked table succeed and do not close the cursor.

  The cursor position depends on the type of table and whether the column has a clustered index. See "Cursor positioning after a delete or update command without joins" for more information.

  Join column buffering may affect the result sets returned when join columns are updated. See "Join cursor processing and data modifications" for more information.

Table 17-2 shows how delete and update commands affect join cursors.

In this table, "Left open" means that the cursor is not closed by the update. The cursor is still positioned on the row, so positioned updates can still be made, and the next fetch also succeeds.

Effects of join column buffering on join cursors

For cursors on queries that include joins, the join columns, search arguments and select list values for the outer table in the join order are buffered with the first fetch. If more than one row is returned from the inner table in the join order, subsequent fetches use the buffered value for the outer row. This means that the behavior of applications that update join columns and search arguments through cursors can vary, depending on the join order chosen for the query. Join column buffering affects both allpages-locked tables and data-only-locked tables.

In version 11.5.x, join cursors on allpages-locked tables did not buffer join values.

The results of cursor queries that perform joins may produce varying results, depending on the join order chosen for the query, if updates to the join column take place during the session. The following two examples illustrate how join order can affect cursor results sets.

select * from dept

dept_id deptloc
------- -----------------
      1 Elm Street
      2 Acacia Drive
      3 Maple Lane
      4 Oak Avenue

select * from employee

dept_id empid
------- ---------
      1 172321176
      1 213468915
      2 341221782
      2 409567008
      2 427172319
      3 472272349
      3 486291786

Example of join column buffering

These statements declare a cursor, open it, and fetch the first row:

declare j_curs cursor for
select d.dept_id, e.empid, d.deptloc
from dept d , employee e
where d.dept_id = e.dept_id
and d.dept_id = 2

open j_curs

fetch j_curs

dept_id     empid       deptloc
----------- ----------- --------------------
          2   341221782 Acacia Drive

If dept is chosen as the outer column in the join order, the value 2 for dept_id is buffered. The following update changes the dept_id of the join column in dept:

update dept set dept_id = 12 
where current of  j_curs

This update changes the value stored in the table row, but does not alter the buffered value, hiding the result of this update to the base table from the cursor. The effect would be the same if the join column in dept were updated by a non-positioned update.

This update changes the dept_id of the employee row:

update employee set dept_id = 12
where current of j_curs

The next fetch on the table returns the second row in the result set:

fetch j_curs

dept_id     empid       deptloc              
----------- ----------- -------------------- 
          2   409567008 Acacia Drive

All matching rows from employee can be fetched and updated.

If the join order for this cursor selects employee as the outer table, and the join column in dept is updated after the first fetch, the second fetch finds no matching rows. This is the sequence of steps:

1. The value 2 for employee.dept_id is buffered during the first fetch.

2. A searched or positioned update changes the value of dept.dept_id to 12.

3. At the second fetch, 2, the buffered value for employee.dept_id is used, and the inner table is scanned for matching values; since dept.dept_id has been changed to 12, the second fetch does not return a row.

The two remaining rows in employee with dept_id equal to 2 cannot be fetched by the cursor.

Example of search argument buffering

Due to buffering of search arguments, the sensitivity of cursor results to updates of the search arguments on cursor select queries also depends on join order. This example uses the dept and employee tables as shown in "Effects of column buffering during cursor scans". The following cursor joins on the dept_id columns of the table, using a search argument on the deptloc column:

declare c cursor for
select d.dept_id, empid, deptloc
from dept d , employee e
where d.dept_id = e.dept_id
and deptloc = "Acacia Drive"

The first fetch on this table returns this row:

dept_id   empid       deptloc
--------- ----------- --------------
        2 41221782    Acacia Drive

This positioned update statement changes employee.dept_id value to 4 for the current row; it needs to be performed for each employee in the department located at Acacia Drive:

update employee set dept_id = 4
where current of c

This positioned update changes the dept.deptloc value from "Acacia Drive" to "Pine Way",

update dept set deptloc = "Pine Way"
where current of c

If the update to deptloc is issued after the first fetch, the result set may or may not return all of the rows that need to be changed; the results depend on the join order chosen for the query:

• If dept is chosen as the outer table, the dept.dept_id and dept.deptloc columns, the values "2" and "Acacia Drive", are buffered. After the first fetch, the update to deptloc updates the base table, but not the buffered values, and subsequent fetch commands return additional result set rows.

• If employee is chosen as the outer table, only the dept_id qualification on the employee table is buffered. The update to the deptloc column changes the base table, and when the search argument qualification "Acacia Drive" is applied to the deptloc table at the next fetch, no rows are returned, even though two rows with employee.dept_id of 2 remain in the table.

Note that issuing the update to deptloc after all rows from the employee table have been fetched would accomplish both updates, without a dependency on the join order.

Effects of select-list buffering

Columns from the select list of the outer table in the join order are buffered when a row from the outer table is fetched. If a searched or positioned update is performed on a column in the select list, and another row is fetched from the inner table, the buffered value from the outer table appears in the cursor result row.

Recommendations

In general, applications should attempt to avoid updating join columns or columns with search clauses and other predicates to change their value when cursor scans are in progress.

• Avoid updates to the join columns of cursors or to the search arguments of join cursors whenever possible, unless you are completely sure of the join order.

• Use a cursor query that creates a worktable, and then use searched updates and deletes on the base table. When a cursor select query requires a worktable, the cursor is always insensitive to changes to the underlying table. Cursors that include order by, distinct, group by, or other clauses that create a worktable cannot be updated with positioned updates.

• If you are using cursors to update both join columns, consider using searched updates instead of positioned updates. Although further fetches may return buffered values instead of values that have been changed in the data rows, use of searched updates avoids the possibility of failing to fetch matching rows due to the choice of join order. Searched updates to a clustered index key in an allpages-locked table implicitly close the cursor.

• Note that the ANSI SQL 92 entry-level specification does not allow updates to join columns using cursors, so the behavior is implementation-specific. Applications designed to be portable across different database software must take individual implementations into account.