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 issued under the same CONNECT TO, SET CONNECT, or default connection.

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

These situations block cursors:

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

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

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

Note  Microsoft® SQL Server™ version 6.5 performs locking internally at the page level rather than at the row level. Therefore, a second operation can be locked out by the first cursor operation even though the operations are accessing different rows.

For information about SQL Server cursor locking mechanisms, see Cursor Transaction Isolation Levels.