Isolation Levels

An isolation level determines the degree to which data is isolated for use by one process and guarded against interference from other processes. With browse cursors, isolation level is controlled on a per-cursor basis in Microsoft® SQL Server™ 2000.

If you do not specify the HOLDLOCK option in the DECLARE CURSOR statement, the isolation level is similar to cursor stability. SQL Server maintains only a share lock on a single row of the database as you retrieve rows with the FETCH statement by using a browse cursor. As long as the cursor is located on a given row, no other process can update that data page.

When you use the HOLDLOCK option, the isolation level is set to repeatable read. With the FETCH statement, and by using a browse cursor, SQL Server maintains a share lock on each fetched page of the database. No updates are permitted to the fetched data of the result set as long as the cursor is open, no matter what its position in the table. The repeatable read isolation level is useful when you want to scan a result set and produce a self-consistent summary report without locking the entire result set. Other users can update rows of the result set that have not been fetched, but fetched rows cannot be updated until the cursor is closed. When a cursor is declared with HOLDLOCK, the lock is freed when the cursor closes. To reread a result set without freeing the lock, reopen the cursor without closing it.

When you use a DECLARE CURSOR statement with the FOR BROWSE option (which is required for UPDATE or DELETE WHERE CURRENT OF statements), SQL Server makes a snapshot of the result set when the cursor is opened. No locks are placed on the original data; the cursor cannot detect any changes that are made to the data as it fetches rows. If the cursor is reopened, SQL Server makes a new snapshot of the data, so the results may not be the same.

You can use a SELECT with HOLDLOCK to place share locks on a set of rows that prevents other connections from updating the rows, and then open a FOR BROWSE cursor to repeatedly read the rows. If any modifications are needed, an UPDATE with a WHERE CURRENT OF clause can be used to update the row at the cursor position. Because the SELECT with HOLDLOCK was issued by the same connection, the share locks acquired by the HOLDLOCK do not block the UPDATE statements. All the locks are freed when the transaction is committed or rolled back.

The following example shows the use of the HOLDLOCK option with a browse cursor:

/* Declare a cursor for browse. */
EXEC SQL DECLARE CURSOR c1 FOR SELECT * FROM orders FOR BROWSE;

/* Begin a transaction using dynamic SQL. */
strcpy(prep, "begin transaction");
EXEC SQL EXECUTE IMMEDIATE :prep;

/* Issue a singleton SELECT statement that checks all rows but 
return one row of output only. */
EXEC SQL SELECT COUNT(*) INTO :count FROM orders HOLDLOCK;

/* The result set is now locked until the transaction is complete.
Open the cursor previously declared for browse, do some fetches 
and updates, close it, reopen it, and so on. */
EXEC SQL OPEN c1;

while (SQLCODE ==0)
{
   EXEC SQL FETCH c1 INTO :order_struct;
            .
            .
            .
   EXEC SQL UPDATE orders SET trancode = :new_code
WHERE CURRENT OF c1;
}

EXEC SQL CLOSE c1;
EXEC SQL OPEN c1;

/* Some fetch and update operations can be done here, 
and the tables will not be changed. */

EXEC SQL CLOSE c2;
strcpy(prep, "commit transaction");
EXEC SQL EXECUTE IMMEDIATE :prep;
/* Now all locks are free. */