ACID PROPERTIES WITH SQL TRNSACTIONS

Avatar photoByAdil

Step 1: Create the Accounts Table & Insert Sample Data 

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT CHECK (balance >= 0) 
);

INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);

Step 2: Atomicity

Goal: Transfer money from Alice → Bob, but rollback midway to prevent partial update. 

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
ROLLBACK;
SELECT * FROM Accounts;

Expected Result: No changes happen. Alice still has 5000, Bob 3000.
This shows atomicity — either all updates succeed, or none do.

Step 3: Consistency
Goal: Reject invalid state (negative balance). 

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

Expected Result: Error due to CHECK (balance >= 0).
This preserves data consistency — database rules are enforced.

Step 4: Isolation
Goal: Observe isolation between concurrent transactions.

Session 1: 

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 103;

Session 2 (another terminal/session): 

SELECT * FROM Accounts WHERE acc_no = 103;

Expected Result:

Depending on isolation level (REPEATABLE READ default in MySQL), Session 2 may not see uncommitted changes.
Session 1 commits → changes become visible. 

COMMIT;

This shows isolation — transactions do not interfere unexpectedly.

Step 5: Durability
Goal: Data persists after commit. 

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;

Restart the database.
Run: 

SELECT * FROM Accounts WHERE acc_no = 102;

Expected Result: Bob’s balance is updated permanently. ✅
This demonstrates durability — committed transactions survive crashes.



Similar Posts