Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 409

A transaction can open several cursors—either sequentially or simultaneously. Additionally,
two or more cursors may be open on the same table; either directly on the table or through an
SQL view on that table. Because cursors require considerable memory, having many cursors
open at the same time for, say, a thousand concurrent transactions, will consume considerable
memory. One way to reduce cursor burden is to define reduced-capability cursors and use them
when a full-capability cursor is not needed.
Figure 9-13 lists four SQL cursor types used in Microsoft SQL Server 2012 environment
(cursor types for other systems are similar). The simplest cursor is the forward only cursor .
With it, the application can only move forward through the records. Changes made by other
cursors in this transaction and by other transactions will be visible only if they occur to rows
ahead of the cursor.
The next three types of cursors are called scrollable cursors because the application can
scroll forward and backward through the records. A static cursor takes a snapshot of a rela-
tion and processes that snapshot. Changes made using this cursor are visible; changes from
other sources are not visible.
A keyset cursor combines some of the features of static cursors with some of the features
of dynamic cursors. When the cursor is opened, a primary key value is saved for each row.
When the application positions the cursor on a row, the DBMS uses the key value to read the
current value of the row. Inserts of new rows by other cursors (in this transaction or in other
transactions) are not visible. If the application issues an update on a row that has been deleted
by a different cursor, the DBMS creates a new row with the old key value and places the updated

Figure 9-13
Summary of SQL Cursor
types

CursorType Description Comments

Forward only Application can only move

forward through the recordset.

Changes made by other cursors
in this transaction or in other
transactions will be visible only
if they occur on rows ahead of
the cursor.

Static Application sees the data as

they were at the time the
cursor was opened.

Changes made by this cursor
are visible. Changes from
other sources are not visible.
Backward and forward
scrolling allowed.

Keyset When the cursor is opened, a

primary key value is saved
for each row in the recordset.
When the application
accesses a row, the key is
used to fetch the current
values for the row.

Updates from any source are
visible. Inserts from sources
outside this cursor are not visible
(there is no key for them in the
keyset). Inserts from this cursor
appear at the bottom of the
recordset. Deletions from any
source are visible. Changes in
row order are not visible. If the
isolation level is dirty read, then
committed updates and deletions
are visible; otherwise only
committed updates and deletions
are visible.

Dynamic Changes of any type and from

any source are visible.

All inserts, updates, deletions,
and changes in recordset order
are visible. If the isolation level
is dirty read, then uncommitted
changes are visible. Otherwise,
only committed changes are
visible.