In the ANSI/SQL Server isolation hierarchy, which is the strictest isolation level that prevents dirty reads, non-repeatable reads, and phantom rows by using key-range locking?

Introduction / Context:
Different isolation levels trade concurrency for consistency. The strictest level prevents not just dirty and non-repeatable reads but also phantoms.

Given Data / Assumptions:

• Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Requirement: eliminate dirty reads, non-repeatable reads, and phantom inserts.

Concept / Approach:

SERIALIZABLE enforces serial transaction execution semantics by acquiring key-range locks, which block inserts or deletes in the qualified key ranges during the transaction, thereby preventing phantoms.

Step-by-Step Solution:

Verification / Alternative check:

Documentation lists SERIALIZABLE as the top level; query hints like HOLDLOCK (equivalent to SERIALIZABLE) show range locking behavior.

Why Other Options Are Wrong:

REPEATABLE READ lacks range locks. READ COMMITTED releases S locks per statement. READ UNCOMMITTED allows dirty reads.

Common Pitfalls:

Confusing REPEATABLE READ with phantom prevention. Assuming SERIALIZABLE always means poor performance; it depends on workload and index design.

Final Answer:

SERIALIZABLE.