In Microsoft® SQL Server™ version 7.0, 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:
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 authors
GO
/* Example 2 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
DECLARE abc CURSOR STATIC FOR
SELECT * FROM authors
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.
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:
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.