Cursor Concurrency

Microsoft® SQL Server™ supports four concurrency options for server cursors:

• READ_ONLY
• OPTIMISTIC WITH VALUES
• OPTIMISTIC WITH ROW VERSIONING
• SCROLL LOCKS

READ_ONLY

Positioned updates through the cursor are not allowed, and no locks are held on the rows that make up the result set.

OPTIMISTIC WITH VALUES

Optimistic concurrency control is a standard part of transaction control theory. Optimistic concurrency control is used in situations when there is only a slight chance that a second user may update a row in the interval between when a cursor is opened and when the row is updated. When a cursor is opened with this option, no locks are held on the underlying rows, which helps maximize throughput. If the user attempts to modify a row, the current values in the row are compared with the values retrieved when the row was last fetched. If any of the values have changed, the server knows that someone else has already updated the row, and it returns an error. If the values are the same, the server performs the modification.

Selecting this concurrency option forces the user or programmer to accept the responsibility of dealing with the occasional error indicating another user has modified the row. A typical action taken by an application that receives this error is to refresh the cursor, get the new values, and then let the user decide whether to perform the modification on the new values. text, ntext, and image columns are not used for concurrency comparisons in SQL Server version 6.5 or earlier.

OPTIMISTIC WITH ROW VERSIONING

This optimistic concurrency control option is based on row versioning. With row versioning, the underlying table must have a version identifier of some type that the server can use to determine whether the row has been changed after it was read into the cursor. In SQL Server, this capability is provided by the timestamp data type, a binary number that indicates the relative sequence of modifications in a database. Each database has a global current timestamp value, @@DBTS. Each time a row with a timestamp column is modified in any way, SQL Server stores the current @@DBTS value in the timestamp column and then increments @@DBTS. If a table has a timestamp column, then the timestamps are taken down to the row level. The server can then compare the current timestamp value of a row with the timestamp value that was stored when the row was last fetched to determine whether the row has been updated. The server does not have to compare the values in all columns, only the timestamp column. If an application requests optimistic concurrency with row versioning on a table that does not have a timestamp column, the cursor defaults to values-based optimistic concurrency control.

SCROLL LOCKS

This option implements pessimistic concurrency control, in which the application attempts to lock the underlying database rows at the time they are read into the cursor result set. When using server cursors, an update lock is placed on the row when it is read into the cursor. If the cursor is opened within a transaction, these update locks are held until the transaction is either committed or rolled back. If the cursor has been opened outside a transaction, the lock is dropped when the next row is fetched. Therefore, a cursor should be opened in a transaction any time full pessimistic concurrency control is wanted. An update lock prevents any other task from acquiring an update or exclusive lock, which prevents any other task from updating the row. An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock.

Scroll Locks

These cursor concurrency options may generate scroll locks, depending on the locking hints specified in the SELECT statement in the cursor definition. Scroll locks are acquired on each row in a fetch and held until the next fetch or the close of the cursor, whichever occurs first. On the next fetch, the server acquires scroll locks for the rows in the new fetch and then releases the scroll locks for the rows in the previous fetch. Scroll locks are independent of transaction locks and may persist past a commit or rollback operation. If the option to close cursors on commit is off, a COMMIT does not close any open cursors and scroll locks are preserved past the commit to maintain the isolation of the fetched data.

The types of scroll locks acquired depends on the cursor concurrency option and the locking hints in the cursor SELECT statement.

Specifying Cursor Concurrency Options

The concurrency options are specified differently in each cursor environment:

• Transact-SQL cursors
  Specify the READ_ONLY, SCROLL_LOCK, and OPTIMISTIC keywords on the DECLARE CURSOR statement. The OPTIMISTIC keyword specifies optimistic with row versioning, Transact-SQL cursors do not support the optimistic with values concurrency option.
• ADO applications

  Specify adLockReadOnly, adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic in the LockType property of a Recordset object.

• ODBC applications

  Set the statement attribute SQL_ATTR_CONCURRENCY to SQL_CONCUR_READ_ONLY, SQL_CONCUR_ROWVER, SQL_CONCUR_VALUES, or SQL_CONCUR_LOCK.

• DB-Library applications

  Set the dbcursoropen parameter concuropt to CUR_READONLY, CUR_OPTCC (for optimistic using row versioning), CUR_OPTCCVAL, or CUR_LOCKCC.