Cursor Concurrency (Database Engine)

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 another user or process 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 another user or process 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, that capability is provided by the timestamp data type, which is 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.

Note
For cursors opened over remote data sources, updates are not supported through the cursor if the remote source does not contain a timestamp column.

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, the transaction update lock is held until the transaction is either committed or rolled back; the cursor lock is dropped when the next row is fetched. 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 whenever the user wants full pessimistic concurrency control. 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.