DEV Community

Deepana
Deepana

Posted on

ACID properties with SQL transactions

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) -- prevents negative balance
);

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

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Session 2 (another terminal/session):

SELECT * FROM Accounts WHERE acc_no = 103;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • Restart the database.
  • Run: 
SELECT * FROM Accounts WHERE acc_no = 102;
Enter fullscreen mode Exit fullscreen mode

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

Conclusion

ACID properties ensure reliable and consistent database transactions. Atomicity prevents partial updates, Consistency enforces rules, Isolation keeps transactions independent, and Durability makes changes permanent. Practicing these ensures your data is always safe and trustworthy.

Top comments (0)