Cursors and Locking
In Microsoft SQL Server Compact 4.0, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statements. 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.
For base table or index cursors only, the transaction isolation level can affect the locks that are acquired.
Important
For the default isolation level of READ COMMITTED, SQL Server Compact 4.0 does not acquire locks when positioning and reading data. Cursors in READ COMMITTED require a Sch-S lock to protect the stability of the schema while the cursor is open. Exclusive locks are taken if data is changed.
Locks are held until the end of the current transaction for both cursors and independent SELECT statements.
Acquiring Locks
Although cursors follow 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 required. For an independent SELECT, all the rows are retrieved when the statement is executed. Cursors 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.
Forward-only cursors do not retrieve rows until they are fetched. Locks are not acquired on the rows until they have been positioned on.