Database Reference
In-Depth Information
The following examples detail the cost overhead of the optimistic concurrency model:
•
Row versioning:
Since the optimistic concurrency model allows the cursor to be updatable, an
additional cost is incurred to ensure that the current underlying row is first compared (using
either version-based or value-based concurrency control) with the original cursor row fetched
before applying a modification through the cursor. This prevents the modification through the
cursor from accidentally overwriting the modification made by another user after the cursor
row is fetched.
•
Concurrency control without a
ROWVERSION
column:
As explained previously, a
ROWVERSION
column in the underlying table allows the cursor to perform an efficient version-based
concurrency control. In case the underlying table doesn't contain a
ROWVERSION
column, the
cursor resorts to value-based concurrency control, which requires matching the current value
of the row to the value when the row was read into the cursor. This increases the cost of the
concurrency control. Both forms of concurrency control will cause additional overhead in
the
TEMPDB
.
Scroll Locks
The major benefit of the scroll locks concurrency model is as follows:
•
Simple concurrency control:
By locking the underlying row corresponding to the last fetched
row from the cursor, the cursor assures that the underlying row can't be modified by another
user. This eliminates the versioning overhead of optimistic locking. Also, since the row cannot
be modified by another user, the application is relieved from checking for a row-mismatch error.
The scroll locks concurrency model incurs the following cost overhead:
•
Highest locking overhead:
The scroll locks concurrency model introduces a pessimistic locking
characteristic. A (U) lock is held on the last cursor row fetched, until another cursor row is
fetched or the cursor is closed.
•
Lowest concurrency:
Since a (U) lock is held on the underlying row, all other users requesting
a (U) or an (X) lock on the underlying row will be blocked. This can significantly hurt
concurrency. Therefore, please avoid using this cursor concurrency model unless absolutely
necessary.
Cost Comparison on Cursor Type
Each of the basic four cursor types mentioned in the “Cursor Fundamentals” section earlier in the chapter incurs a
different cost overhead on the server. Choosing an incorrect cursor type can hurt database performance. Besides the
four basic cursor types, a fast-forward-only cursor (a variation of the forward-only cursor) is provided to enhance
performance. The cost overhead of these cursor types is explained in the sections that follow.
