All 7 Isolation & Locking questions
More in Isolation & Locking
What is a lock in SQL Server, and why does the database engine use locks? What is a deadlock, and how does SQL Server automatically handle one when it occurs? What is the difference between an optimistic and a pessimistic approach to handling concurrency, and where does SQL Server's snapshot isolation fit into this? What are lock escalation and its potential impact on a busy system? How would you diagnose and resolve a blocking problem where many queries are waiting on a single long running transaction? How does Read Committed Snapshot Isolation differ from regular Read Committed isolation, and what tradeoff does enabling it introduce?
Advertisement
SQL Server › Isolation & Locking

What are the four standard transaction isolation levels in SQL Server, and what does isolation level actually control?

Beginner

Answer

The isolation level controls how much one transaction can be affected by changes made by other concurrently running transactions, with Read Uncommitted allowing the most interference, Read Committed being the default and more balanced, Repeatable Read providing more consistency by preventing changes to already read data, and Serializable providing the strictest, fully isolated behavior.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE CustomerId = 1;
COMMIT TRANSACTION;
Real-world example A reporting application uses the default Read Committed isolation level for most queries, balancing reasonable consistency with good overall concurrency for the many simultaneous users accessing the system.

Common follow-ups: What is a dirty read, and which isolation level allows it to happen?;Which isolation level provides the strongest guarantees against interference from other transactions?

Transactions & ACID;Query Optimization & Plans
All 7 Isolation & Locking questions