Drag and Drop Question You have an SQL Server 2014 server. You plan to create four stored procedures that will use transactions. The stored procedures will be configured as shown in the following…

Question

Exhibit

Answer Area

Explanation

SQL Server Isolation Levels - Explanation

Note: The question references a configuration table for SP1-SP4 that isn't included in your paste. The explanation below is based on the standard characteristics that drive each correct answer.

Isolation Level Reference

Individual Placements

SP1 -> SNAPSHOT

Why: SP1 requires high concurrency with consistent reads - readers must not block writers and writers must not block readers. SNAPSHOT uses row versioning (tempdb) to give each transaction a consistent view of data as it existed at transaction start. This eliminates read/write lock contention entirely, making it ideal when you need repeatable, consistent reads without acquiring locks.

Key distinction from SERIALIZABLE: Both prevent dirty/non-repeatable/phantom reads, but SERIALIZABLE uses range locks that block concurrent writers. SNAPSHOT avoids this at the cost of tempdb overhead.

SP2 -> REPEATABLE READ

Why: SP2 needs to re-read the same rows multiple times within a transaction and get the same result (prevents non-repeatable reads), but does not need to prevent phantom rows (new inserts by other transactions). SQL Server holds shared locks on read rows for the duration of the transaction, preventing updates to those rows - but range locks are not acquired, so new rows can be inserted.

Common mistake: Choosing SERIALIZABLE here is overly restrictive. If phantom reads aren't a concern, SERIALIZABLE adds unnecessary range locking and reduces concurrency.

SP3 -> READ COMMITTED

Why: SP3 is a standard read/write procedure with no special consistency requirements between statements. READ COMMITTED (SQL Server's default) acquires and immediately releases shared locks after each row is read. This minimizes lock duration and maximizes concurrency while still preventing dirty reads (reading uncommitted data from other transactions).

Common mistake: Using READ UNCOMMITTED to "minimize locks" - this eliminates read locks entirely but allows dirty reads, which is almost never acceptable in transactional business logic.

SP4 -> SERIALIZABLE

Why: SP4 requires the strictest consistency - it must prevent dirty reads, non-repeatable reads, and phantom reads. SERIALIZABLE places range locks on the data set, blocking other transactions from inserting rows that would fall within a queried range. This is appropriate when SP4 performs logic like "if no rows exist, insert one" (insert-if-not-exists patterns), where a phantom row appearing mid-transaction would corrupt the result.

Common mistake: Confusing SNAPSHOT with SERIALIZABLE. SNAPSHOT also prevents phantoms, but uses optimistic concurrency - two concurrent SNAPSHOT transactions modifying the same row will cause one to fail with an update conflict error. SERIALIZABLE uses pessimistic locking, which is safer when write conflicts must be prevented entirely rather than detected at commit.

Summary of the Logic

The arrangement follows a least-to-most restrictive matching based on each procedure's actual need:

• Need no blocking + consistent view -> SNAPSHOT (optimistic)
• Need stable row re-reads, no phantom concern -> REPEATABLE READ
• Need basic dirty-read prevention only -> READ COMMITTED (default, minimal locks)
• Need full serializability including phantoms -> SERIALIZABLE (pessimistic, most restrictive)

The guiding principle is always: use the least restrictive isolation level that satisfies your consistency requirement, since higher isolation = more locking = less concurrency.

No community discussion yet for this question.