Cursor and Lock

Last month one of my client asked me a question, how SQL Server locks a table and its rows while a cursor is looping over it? So let's do an experiment using the StackOverflow2013 sample database.

Run below query on a window in SSMS. (For simplicity I just show you FAST_FORWARD which was mostly recommended by SQL Server MVPs. You can also try any other types of cursor, but I can tell you that the results are the same):

IF @@TRANCOUNT <> 0 ROLLBACK;
DECLARE @Id int;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT V.Id FROM Votes V JOIN Users U ON V.UserId = U.Id
OPEN cur
WHILE 1 = 1
BEGIN
    FETCH cur INTO @Id
    IF @@FETCH_STATUS <> 0 BREAK
END
CLOSE cur
DEALLOCATE cur

 Get the SPID from the query, here I got spid 57:

While the first query is running, open a new query window, change the spid value, then execute it:

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
            Status,command,wait_type,wait_time,wait_resource,
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id = 57    -- Change it to the SPID number of the first query
ORDER BY    r.start_time
EXEC sp_lock 57;    -- Change it to the SPID number of the first query
-- Change below values to the dbid and ObjId values that you got from above result
SELECT DB_NAME(12), OBJECT_NAME(149575571, 12), OBJECT_NAME(181575685, 12);

Run the second query multiple times during the first one still executing. You can see different locks hold by DECLARE CURSOR, OPEN CURSOR, and FETCH statements:

Here is the summary of the locks being held by each cursor statement:

DECLARE: just defines the attributes of a cursor and the query used to build the result set, but NOT execute the query, so it always run too fast for you to get its lock, and actually it takes no any locks.
FETCH: retrieves one row from the cursor, it only takes DB S lock.
OPEN: opens a cursor and populates it by executing the cursor query. Table/page/row locks will be obtained by this statement. By default (read committed isolation level), once the OPEN statement finished, locks will be released. So NO table/page/row locks inside each loop for the cursor query.

Final thought: cursors could be used in some applications for serialized operations, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.