Interesting SQL Server Cursors locking observation

If you are using SQL Server cursors,
you should be aware of the following locking behavior of a cursor. 

Lets take a simple example of a cursor in SQL Server,
we will use the cursor just to iterate through rows in a table.

DECLARE @CustomerID INT

DECLARE Customers_Cursor CURSOR FOR

SELECT CustomerID

FROM dbo.Customers

OPEN Account_Cursor

FETCH NEXT FROM Customers_Cursor INTO @CustomerID

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @CustomerID

FETCH NEXT FROM Customers_Cursor INTO @CustomerID

END

CLOSE Customers_Cursor

DEALLOCATE Customers_Cursor

What we want to check is what locks are acquired and how many times.
Let`s assume that the “Customers” table has PK on CustomerID column.

If you set an extended events session that captures “lock_acquired”
and “lock_released“ events, you will see that

For the same key/row, Shared Lock acquired and released twice:
The first time upon OPEN statement 

and the second time upon FETCH statement.

So if you have deadlocks involving cursors and shared locks,
keep this behavior in mind.

If all you want to do is just to iterate through the rows
without jumping back and forward
and without updating rows in the cursor`s table
You can use FAST FORWARD cursor type.
DECLARE Customers_Cursor CURSOR FAST_FORWARD FOR