Cursor Concurrency (Database Engine)
Microsoft SQL Server 2005 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.
Note
In SQL Server 2000 and SQL Server 7.0, if the underlying table has a timestamp column, OPTIMISTIC WITH ROW VERSIONING is used even if OPTIMISTIC WITH VALUES is specified. If OPTIMISTIC WITH ROW VERSIONING is specified and the table does not have timestamps, OPTIMISTIC WITH VALUES is used.
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.
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 type of scroll locks acquired depends on the cursor concurrency option and the locking hints in the cursor SELECT statement.
Note
In SQL Server 2005, scroll locks are supported only for keyset-driven and dynamic cursors.
| Locking hints | Read only | Optimistic with values | Optimistic with row versioning | Locking |
|---|---|---|---|---|
No Hints |
- |
- |
- |
Update |
NOLOCK* |
- |
- |
- |
- |
HOLDLOCK |
- |
- |
- |
Update |
UPDLOCK |
- |
- |
- |
Update |
TABLOCKX |
- |
- |
- |
Update |
All Others |
- |
- |
- |
Update |
*Specifying the NOLOCK hint makes the table on which it is specified read-only through the cursor.
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.
See Also
Other Resources
DECLARE CURSOR (Transact-SQL)
Cursor Concurrency (ODBC)