Cursors and Lock Conflicts

Updates are issued through a single database connection including updates that reference a cursor (for example, in an UPDATE WHERE CURRENT OF cursor_name statement). Because of this, locking conflicts do not occur between updates that are issued under the same CONNECT TO, SET CONNECT, or default connection.

Each cursor's retrieval operations are performed through a separate database connection.

The following situations block cursors:

• Cursors that are declared without FOR BROWSE place a read lock on the current page as they move through the results set. No changes can be made to that page by anyone, not even the cursor owner, until the cursor moves on.

  When the cursor attempts to read a page that contains an uncommitted change (made by anyone, including the cursor owner), the cursor waits until the change is committed.

• Cursors that are declared with FOR BROWSE must wait for uncommitted changes (made by anyone, including the cursor owner) only during the OPEN CURSOR operation. Once the cursor is opened, subsequent changes do not cause the cursor to wait. When a cursor is reopened, it can be blocked by uncommitted changes.

Note

SQL Server internally performs locking at the page level rather than at the row level. This means that a second operation can be locked out by the first cursor operation even though the operations are accessing different rows.