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.
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 page 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 results set as long as the cursor is open, no matter what its position is in the table. The repeatable read isolation level is useful when you want to scan a results set and produce a self-consistent summary report without locking the entire results set. Other users can update rows of the results 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 results 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 results 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 might not be the same.
The FOR BROWSE and HOLDLOCK options are mutually exclusive. However, you can have cursors repeatedly read, and you can update rows by first locking the rows with the HOLDLOCK option and then opening a cursor by using the FOR BROWSE statement.
The following example illustrates 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 that checks all rows but */
/* return one row of output only. */
EXEC SQL SELECT COUNT(*) INTO :count FROM orders HOLDLOCK;
/* The results 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. */
Because the updates occur over the same connection as the HOLDLOCK operation in the example, conflict does not occur between the FOR BROWSE and the HOLDLOCK options. The locks are released when the transaction is committed or rolled back.