What ACID property in SQL Server and its uses?

 

ACID is a set of properties in SQL Server (and other RDBMS) that ensures reliable processing of database transactions. ACID stands for:

1. Atomicity – All operations within a transaction are completed; if not, the transaction is aborted.
2. Consistency – Data must be in a consistent state before and after the transaction.
3. Isolation – Transactions are isolated from each other until they are complete.
4. Durability – Once a transaction is committed, it remains so even in the case of a system failure.

---

Focus on Isolation

Isolation determines how transaction integrity is visibly affected by other concurrent transactions. SQL Server supports several Isolation Levels, each handling concurrency in different ways:

---

Read Issues

• Dirty Read – Reading uncommitted data from another transaction.
• Non-Repeatable Read – A row is read twice and returns different data each time.
• Phantom Read – New rows are added by another transaction, affecting results of a repeat query.

---

Set Isolation Level

You can set the isolation level in SQL Server using:

SET TRANSACTION ISOLATION LEVEL <LEVEL>;

Where <LEVEL> can be:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

---

Example: Understanding Isolation Levels

Let's go step-by-step with two sessions.

---

Step 1: Setup Sample Table

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT
);

INSERT INTO Products VALUES (1, 'Laptop', 10);

---

Step 2: Session A – Start a Transaction

BEGIN TRAN;
UPDATE Products SET Quantity = 5 WHERE ProductID = 1;
-- Do not commit yet

---

Step 3: Session B – Set Isolation Level and Read

a. READ UNCOMMITTED (Allows Dirty Read)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Products WHERE ProductID = 1;
-- Will show Quantity = 5 (dirty read)

b. READ COMMITTED (Default, prevents dirty read)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM Products WHERE ProductID = 1;
-- Will block until Session A commits or rolls back

c. REPEATABLE READ (Prevents dirty & non-repeatable reads)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT * FROM Products WHERE ProductID = 1;
-- Re-reading gives the same result, but phantom rows may appear on re-query
COMMIT;

d. SERIALIZABLE (Strictest – prevents all)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT * FROM Products WHERE Quantity > 0;
-- Prevents insert/update/delete by others affecting this range
COMMIT;

e. SNAPSHOT (Uses row versioning)

Requires enabling snapshot isolation first:

ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

Then in session:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT * FROM Products;
-- Always sees a consistent snapshot, even if others commit changes later
COMMIT;