Database Reference
In-Depth Information
Server-side cursors have the following cost overhead or disadvantages:
•
Lower scalability:
They make the server less scalable since server resources are consumed to
manage the cursor.
•
More network round-trips:
They increase network round-trips if the cursor row processing
is done in the client application. The number of network round-trips can be optimized by
processing the cursor rows in the stored procedure or by using the cache size feature of the
data access layer.
•
Less portable:
Server-side cursors implemented using T-SQL cursors are not readily portable
to other databases because the syntax of the database code managing the cursor is different
across databases.
Cost Comparison on Cursor Concurrency
As expected, cursors with a higher concurrency model create the least amount of blocking in the database and
support higher scalability, as explained in the following sections.
Read-Only
The read-only concurrency model provides the following cost benefits:
•
Lowest locking overhead:
The read-only concurrency model introduces the least locking and
synchronization overhead on the database. Since (S) locks are not held on the underlying row
after a cursor row is fetched, other users are not blocked from accessing the row. Furthermore,
the (S) lock acquired on the underlying row while fetching the cursor row can be avoided by
using the
NO_LOCK
locking hint in the
SELECT
statement of the cursor, but only if you don't care
about what kind of data you get back because of dirty reads.
•
Highest concurrency:
Since additional locks are not held on the underlying rows, the read-only
cursor doesn't block other users from accessing the underlying tables. The shared lock is still
acquired.
The main drawback of the read-only cursor is as follows:
•
Nonupdatable:
The content of underlying tables cannot be modified through the cursor.
Optimistic
The optimistic concurrency model provides the following benefits:
•
Low locking overhead:
Similar to the read-only model, the optimistic concurrency model
doesn't hold an (S) lock on the cursor row after the row is fetched. To further improve
concurrency, the
NOLOCK
locking hint can also be used, as in the case of the read-only
concurrency model. But, please know that
NOLOCK
can absolutely lead to incorrect data or
missing or extra rows, so its use requires careful planning. Modification through the cursor to
an underlying row requires exclusive rights on the row as required by an action query.
•
High concurrency:
Since only a shared lock is used on the underlying rows, the cursor
doesn't block other users from accessing the underlying tables. But the modification through
the cursor to an underlying row will block other users from accessing the row during the
modification.
