Cursor Locking
In SQL Server, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.
The transaction locks acquired by any SELECT statement, including the SELECT statement in a cursor definition, are controlled by:
The transaction isolation level setting for the connection.
Any locking hints specified in the FROM clause.
These locks are held until the end of the current transaction for both cursors and independent SELECT statements. When SQL Server is running in autocommit mode, each individual SQL statement is a transaction and the locks are freed when the statement finishes. If SQL Server is running in explicit or implicit transaction mode, then the locks are held until the transaction is either committed or rolled back.
For example, the locking done for these two Transact-SQL examples is essentially the same:
/* Example 1 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
SELECT * FROM AdventureWorks2008R2.Sales.Store;
GO
/* Example 2 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
DECLARE abc CURSOR STATIC FOR
SELECT * FROM AdventureWorks2008R2.Sales.Store;
GO
OPEN abc
GO
Setting the transaction isolation level to repeatable read means that both the independent SELECT statement in Example 1 and the SELECT statement contained in the DECLARE CURSOR of Example 2 generate share locks on each row they read, and the share locks are held until the transaction is committed or rolled back.
Acquiring Locks
Although cursors obey the same rules as independent SELECT statements, regarding the type of transaction locks acquired, the locks are acquired at different times. The locks generated by an independent SELECT or a cursor are always acquired when a row is retrieved. For an independent SELECT, all the rows are retrieved when the statement is executed. Cursors, however, retrieve the rows at different times depending on the type of cursor:
Static cursors retrieve the entire result set at the time the cursor is opened. This locks each row of the result set at open time.
Keyset-driven cursors retrieve the keys of each row of the result set at the time the cursor is opened. This locks each row of the result set at open time.
Dynamic cursors (including regular forward-only cursors) do not retrieve rows until they are fetched. Locks are not acquired on the rows until they have been fetched.
Fast forward-only cursors vary in when they acquire their locks depending on the execution plan chosen by the query optimizer. If a dynamic plan is chosen, no locks are taken until the rows are fetched. If worktables are generated, then the rows are read into the worktable and locked at open time.
Cursors also support their own concurrency specifications, some of which generate additional locks on the rows in each fetch. These scroll locks are held until the next fetch operation or until the cursor is closed, whichever comes first. If the connection option to keep cursors open on a commit is set on, these locks will be kept across a commit or rollback operation.