Transaction Isolation

When two users try to update the same row at the same time, who wins? How much "In-Progress" data can other users see? Isolation Levels define the balance between Data Consistency and Performance (Concurrency).

1. The Four Standard Levels

• Read Uncommitted (NOLOCK): Fastest. Can see "Dirty Reads" (data that might be rolled back). Great for reporting, dangerous for banking.
• Read Committed: The default. You only see data that has been finished. Can suffer from "Non-Repeatable Reads."
• Repeatable Read: Prevents data from changing while you are reading it.
• Serializable: The strictest. Locks entire ranges of keys. Zero chance of phantom data, but very slow.

2. SNAPSHOT Isolation (The MVP)

Snapshot isolation provides the consistency of a strict level but without the Blocking. It uses Row Versioning in TempDB. When you read, you see a "Snapshot" of the data as it was when your query started. Writers don't block Readers, and Readers don't block Writers.

4. Interview Mastery

Q: "Why should I avoid using 'WITH (NOLOCK)' on every table?"

Architect Answer: "Developers use NOLOCK to 'speed up' queries by preventing blocks. However, NOLOCK can result in **Duplicate Rows** or **Missing Rows** in your result set if a Page Split occurs while you are reading. It can even cause your query to crash with an 'Internal Error'. Instead of NOLOCK, you should use **Read Committed Snapshot Isolation (RCSI)** at the database level, which gives you the same speed without the risk of reading corrupt data."