Explain ACID property with example.

---

1. What is ACID Property in SQL Server?

ACID stands for:

• Atomicity: A transaction is all or nothing. If any part fails, the entire transaction fails.
• Consistency: A transaction keeps the database in a valid state before and after execution.
• Isolation: Transactions run separately without affecting each other.
• Durability: Once a transaction is committed, its result is permanent, even if the server crashes.

Example:

BEGIN TRANSACTION;

UPDATE BankAccounts
SET Balance = Balance - 1000
WHERE AccountNumber = 'A001';

UPDATE BankAccounts
SET Balance = Balance + 1000
WHERE AccountNumber = 'A002';

COMMIT TRANSACTION;

Here, either both updates succeed or neither happens. This ensures Atomicity, Consistency, Isolation, and Durability.

---

2. What are the Types of Isolation Levels?

• Read Uncommitted: Allows dirty reads. Data can be read even if not committed.
• Read Committed: Only reads committed data. No dirty reads allowed. (This is SQL Server’s default.)
• Repeatable Read: Ensures that if you read the same row twice, you will get the same result both times. No dirty or non-repeatable reads.
• Serializable: Highest level. Prevents dirty reads, non-repeatable reads, and phantom reads. Locks the range of data.
• Snapshot: Reads consistent data using versioning without locking.

Command to set isolation level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

---

3. Dirty Read, Non-Repeatable Read, and Phantom Read

Dirty Read

Definition: Reading uncommitted changes made by another transaction.

Step-by-step Example:

In Session 1:

BEGIN TRANSACTION;
UPDATE Products
SET Price = 500
WHERE ProductID = 1;
-- Do not commit yet

In Session 2:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Price FROM Products WHERE ProductID = 1;

Here, Session 2 reads the updated price even though Session 1 has not committed. If Session 1 rolls back, Session 2 read wrong data. This is called a dirty read.

---

Non-Repeatable Read

Definition: When you read the same row twice and get different data because another transaction modified it.

Step-by-step Example:

In Session 1:

BEGIN TRANSACTION;
SELECT Price FROM Products WHERE ProductID = 1;
-- Price shows 400

In Session 2:

BEGIN TRANSACTION;
UPDATE Products
SET Price = 600
WHERE ProductID = 1;
COMMIT;

Back in Session 1:

SELECT Price FROM Products WHERE ProductID = 1;
-- Now Price shows 600

The same SELECT gave two different results inside the same transaction. This is a non-repeatable read.

---

Phantom Read

Definition: When a new row appears after you run the same query twice during a transaction.

Step-by-step Example:

In Session 1:

BEGIN TRANSACTION;
SELECT * FROM Orders WHERE OrderDate = '2025-04-20';
-- 5 rows returned

In Session 2:

BEGIN TRANSACTION;
INSERT INTO Orders (OrderDate, CustomerID)
VALUES ('2025-04-20', 101);
COMMIT;

Back in Session 1:

SELECT * FROM Orders WHERE OrderDate = '2025-04-20';
-- 6 rows returned

A new row appeared that wasn't there before. This is a phantom read.

---

4. Important Commands for Transactions

Start a transaction:

BEGIN TRANSACTION;

Commit the transaction:

COMMIT;

Rollback (undo) the transaction:

ROLLBACK;

Set isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;