Home

	Expand Search

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

Cursors and locking

Cursor locking options

Cursor locking options

These are the effects of specifying the holdlock or shared options (of the select statement) when you define an updatable cursor:

• If you omit both options, you can read data on the currently fetched pages only. Other users cannot update your currently fetched pages, through a cursor or otherwise. Other users can declare a cursor on the same tables you use for your cursor, but they cannot get an update lock on your currently fetched pages.

• If you specify the shared option, you can read data on the currently fetched pages only. Other users cannot update your currently fetched pages, through a cursor or otherwise.

• If you specify the holdlock option, you can read data on all pages fetched (in a current transaction) or only the pages currently fetched (if not in a transaction). Other users cannot update your currently fetched pages or pages fetched in your current transaction, through a cursor or otherwise. Other users can declare a cursor on the same tables you use for your cursor, but they cannot get an update lock on your currently fetched pages or the pages fetched in your current transaction.

• If you specify both options, you can read data on all pages fetched (in a current transaction) or only the pages currently fetched (if not in a transaction). Other users cannot update your currently fetched pages, through a cursor or otherwise.