SQL Server Interview Question #155

What is a transaction isolation level?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

A transaction isolation level defines the degree to which a transaction is isolated from concurrent changes made by other transactions.

SQL Server supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. READ COMMITTED is the default isolation level, although its implementation can use locks or row versioning depending on database configuration.

Higher isolation can prevent more concurrency anomalies but may increase blocking or resource usage. The correct level depends on the application's consistency requirements.

Code Example

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Products
WHERE Id = 10;

COMMIT;