When building enterprise applications using MS SQL Server, especially in high-concurrency systems (banking, e-commerce, booking systems), understanding ACID properties and Isolation Levels is critical.
In this article, we’ll cover:
ACID ensures reliable database transactions. Atomicity means all operations succeed or none do. Consistency keeps data valid according to rules and constraints. Isolation prevents concurrent transactions from interfering incorrectly. Durability guarantees that once committed, data remains permanently stored, even after failures.
Isolation defines how one transaction behaves when multiple transactions run in parallel.
Without isolation control, you may face:
SQL Server uses locks to maintain consistency.
Shared Lock (S Lock)
Applied during read
Multiple shared locks allowed
BEGIN TRAN
SELECT * FROM Accounts WHERE Id = 1;
— Shared lock applied
Exclusive Lock (X Lock)
BEGIN TRAN
UPDATE Accounts SET Balance = 5000 WHERE Id = 1;
— Exclusive lock applied
Read Block Write | Shared lock prevents update
Write Block Read | Exclusive lock prevents read
Write Block Write | Exclusive locks block each other
What is it?
Lowest isolation level, Allows Dirty Reads.
Equivalent to:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Or:
SELECT * FROM BankAccount WITH (NOLOCK);
Default isolation level in SQL Server.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
What it prevents
Enabled at database level:
ALTER DATABASE YourDB
SET READ_COMMITTED_SNAPSHOT ON;
What changes?
How it works
SQL Server stores previous row versions in tempdb.
Readers get the last committed version instead of waiting.
Enabled using:
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON;
Used inside transaction:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
What it prevents
Uses row versioning.
Highest isolation level.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
What it prevents
How?
Uses range locks.
Quick Links
Contact Sales
Careers
© 2025
Icanio - All rights reserved.
