Records write locked on a RepeatableRead

Question

I have a SQL Server query (using the LLBL ORM, if that is important to the question) that is doing a large fetch on a set of related tables.

This query is being performed in a transaction with the Isolation Level of Repeatable Read, and is being filtered by two 'state' columns in the main table of the query.

Will the records being 'write locked' only be those that match the filter in the main table, or will all records be effectively write locked until the fetch has completed? I am guessing the later will be required to ensure no new records are added to the result set during the transaction.

Answer 1

to ensure no new records are added to the result set during the transaction

This requires Serializable isolation level. Repeatable Read only ensures that the rows read will be able to read again later in the transaction, but does not prevent a concurrent transaction from inserting new rows and those new rows will be visible, in the original transaction, after they are committed by the insert. Under Serializable Read the locks will extend to ranges, so no new records that satisfy the filter will appear. Depending on the table schema (indexes available) the restriction may extend to the entire table(s).

You should give some serious consideration to doing everything under Snapshot Isolation too, this resolves almost all known anomalies, but is more expensive on resource, see Row Versioning Resource Usage.