Database Reference
In-Depth Information
The optimistic concurrency model uses row versioning to determine whether a row has been modified since
it was read into the cursor, instead of locking the row while it is read into the cursor. Version-based optimistic
concurrency requires a
ROWVERSION
column (this was formerly a
TIMESTAMP
data type) in the underlying user table
on which the cursor is created. The
ROWVERSION
data type is a binary number that indicates the relative sequence of
modifications on a row. Each time a row with a
ROWVERSION
column is modified, SQL Server stores the current value of
the global
ROWVERSION
value,
@@DBTS
, in the
ROWVERSION
column; it then increments the
@@DBTS
value.
Before applying a modification through the optimistic cursor, SQL Server determines whether the current
ROWVERSION
column value for the row matches the
ROWVERSION
column value for the row when it was read into the
cursor. The underlying row is modified only if the
ROWVERSION
values match, indicating that the row hasn't been
modified by another user in the meantime. Otherwise, an error is raised. In case of an error, first refresh the cursor
with the updated data.
If the underlying table doesn't contain a
ROWVERSION
column, then the cursor defaults to value-based optimistic
concurrency, which requires matching the current value of the row with the value when the row was read into the
cursor. The version-based concurrency control is more efficient than the value-based concurrency control since it
requires less processing to determine the modification of the underlying row. Therefore, for the best performance of a
cursor with the optimistic concurrency model, ensure that the underlying table has a
ROWVERSION
column.
The following T-SQL statement creates an optimistic T-SQL cursor:
DECLARE MyCursor CURSOR OPTIMISTIC
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
A cursor with scroll locks concurrency holds a (U) lock on the underlying row until another cursor row is fetched
or the cursor is closed. This prevents other users from modifying the underlying row when the cursor fetches it. The
scroll locks concurrency model makes the cursor updatable.
The following T-SQL statement creates a T-SQL cursor with the scroll locks concurrency model:
DECLARE MyCursor CURSOR SCROLL_LOCKS
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1;
Since locks are held on a row being referenced (until another cursor row is fetched or the cursor is closed),
it blocks all the other users trying to modify the row during that period. This hurts database concurrency.
Cursor Types
Cursors can be classified into the following four types:
•
Forward-only cursors
•
Static cursors
•
Keyset-driven cursors
•
Dynamic cursors
Let's take a closer look at these four types in the sections that follow.
