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 8-4, and pause at the arrows to execute sp_lock and examine the locks that are in place.

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

Image shows this cursor: declare curs1 cursor for select au_id, au_lname, au_fname from authors where au_id like ’15%’ for read only go open curs1 go fetch curs1 go fetch curs1 go 100 close curs1 go deallocate cursor curs1 go

Table 8-3 shows the results.

**Table 8-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:

If the cursor query runs at isolation level 1, and read committed with lock is set to 0, you do not see any page or row locks. The values are copied from the page or row, and the lock is immediately released.

If read committed with lock is set to 1 or if the query runs at isolation level 2 or 3, you see either shared page or shared row locks at the point that Table 8-3 indicates shared page locks. If the table uses datarows locking, the sp_lock report includes the row ID of the fetched row.