🔒 Deep Dive: Transaction isolation levels control data visibility between transactions. Choose the right level for your use case.
Concurrent transactions can conflict. Isolation levels prevent data anomalies.
🚀 Isolation Levels Explained
-- ✅ Read Uncommitted (Dirty Reads)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
-- ✅ Can read uncommitted data (dirty reads)
-- Fastest but least consistent
COMMIT;
-- ✅ Read Committed (Default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- ✅ Only reads committed data
-- Prevents dirty reads
COMMIT;
-- ✅ Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
-- ✅ Data stays consistent during transaction
-- Prevents dirty reads and non-repeatable reads
COMMIT;
-- ✅ Serializable (Highest)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- ✅ Full isolation
-- Prevents all anomalies
COMMIT;
-- ✅ Snapshot (Optimistic)
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- ✅ Reads data as of transaction start
-- No locks, uses row versioning
COMMIT;
💡 Real-World: Isolation Level Selection
-- ✅ E-Commerce: Snapshot isolation (prevent deadlocks)
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
-- ✅ Financial: Serializable (highest consistency)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- ✅ Transfer money - must be consistent
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;
COMMIT;
-- ✅ Reporting: Read Uncommitted (fastest)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Orders WHERE OrderDate > '2024-01-01';
-- ✅ Dirty reads are acceptable for reports
-- ✅ Analytics: Snapshot (consistent view)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT
YEAR(OrderDate) AS Year,
SUM(TotalAmount) AS Total
FROM Orders
GROUP BY YEAR(OrderDate);
💡 Expert Tip: Deadlocks and Isolation
-- ✅ Handle deadlocks with TRY/CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRY
BEGIN TRANSACTION;
-- ✅ Critical section
UPDATE Orders SET Status = 'Processing' WHERE Id = 123;
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductId = 456;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
ROLLBACK TRANSACTION;
-- ✅ Retry logic
W AITFOR DELAY '00:00:00.100';
-- Retry transaction
END
ELSE
BEGIN
-- ✅ Other errors
ROLLBACK TRANSACTION;
THROW;
END
END CATCH;
-- ✅ Use NOLOCK hint (Read Uncommitted)
SELECT *
FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2024-01-01';
