Locking with read-only cursors

Here is a piece of cursor code you can use to display the locks that are set up at each point in the life of a cursor. The following example uses an allpages-locked table. Execute the code in Figure 14-4, and pause at the arrows to execute sp_lock and examine the locks that are in place.

Figure 14-4: Read-only cursors and locking experiment input

Table 14-3 shows the results.

Table 14-3: Locks held on data and index pages by cursors

Event

Data page

After declare

No cursor-related locks.

After open

Shared intent lock on authors.

After first fetch

Shared intent lock on authors and shared page lock on a page in authors.

After 100 fetches

Shared intent lock on authors and shared page lock on a different page in authors.

After close

No cursor-related locks.

If you issue another fetch command after the last row of the result set has been fetched, the locks on the last page are released, so there will be no cursor-related locks.

With a data-only-locked table: