In Microsoft SQL Server transaction processing, which isolation level places and then holds shared locks on every row it reads so that the same rows cannot be modified by other transactions until the current transaction completes? Select the correct isolation level from the standard SQL Server list.

Introduction / Context:
SQL Server supports several transaction isolation levels that balance concurrency against consistency. The question asks which level holds locks on all rows that are read so the same rows cannot be changed by others during the transaction, thereby preventing non-repeatable reads.

Given Data / Assumptions:

• Platform: Microsoft SQL Server.
• Isolation levels considered: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Goal: Identify the level that keeps shared locks on read rows until transaction end.

Concept / Approach:

At REPEATABLE READ, SQL Server acquires shared (S) locks on rows it reads and holds them until the transaction completes. This prevents other transactions from updating those rows, eliminating non-repeatable reads. However, it does not inherently protect against phantoms; that requires range locks (key-range locking) at SERIALIZABLE.

Step-by-Step Solution:

Verification / Alternative check:

Check documentation: REPEATABLE READ guarantees that if a row is read once, it cannot be modified by others until the transaction ends. New rows that satisfy the same predicate can still appear (phantoms), which is why SERIALIZABLE exists.

Why Other Options Are Wrong:

READ UNCOMMITTED: no S locks; allows dirty/non-repeatable reads. READ COMMITTED: releases S locks after statement; repeatability not guaranteed. SERIALIZABLE: stricter than necessary for the condition described (adds range locks for phantom prevention).

Common Pitfalls:

Confusing prevention of non-repeatable reads (REPEATABLE READ) with phantom prevention (SERIALIZABLE). Also assuming READ COMMITTED holds locks until commit—it does not.

Final Answer:

REPEATABLE READ