Holding Locks

When using standard cursors with Microsoft® SQL Server™ 2000, the duration of locks acquired during cursor operations is controlled by the application. In other words, an application that uses SET CONCURRENCY LOCKCC must also issue a BEGIN TRANSACTION statement for the locking to have any effect. To hold the lock on the currently fetched row when LOCKCC is used, the application must issue a BEGIN TRANSACTION statement before each FETCH statement and a COMMIT TRANSACTION statement after all operations on that row are complete.

For repeatable-read consistency, specify HOLDLOCK in the SELECT statement when opening the standard cursor, and issue a BEGIN TRANSACTION statement before the first FETCH statement. Locks are obtained as the data is fetched and are retained until the application issues a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.