DEV Community

Cover image for ACID Properties in SQL Transactions
Ilakkiya
Ilakkiya

Posted on

ACID Properties in SQL Transactions

Managing data might seem straightforward, but behind every secure and reliable database are rules that ensure nothing breaks.
Let’s dive into ACID properties and see how SQL transactions guarantee that our operations are safe, consistent, and permanent — demonstrated with a simple Accounts table.

Creating the Accounts Table

We start by creating a table to hold account information and inserting some sample data.

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

INSERT INTO Accounts VALUES
(201, 'Arjun', 12000),
(202, 'Diya', 9500),
(203, 'Vikram', 7000);

Enter fullscreen mode Exit fullscreen mode

Atomicity — All or Nothing

Definition:
A transaction must be atomic: either all operations succeed, or none do.

Example: Money Transfer with Rollback

Consistency — Maintain Data Rules

Definition:
Transactions must leave the database in a valid state, respecting all constraints.

Example: Prevent Negative Balance

Result: The database rejected the invalid insert, maintaining consistent data.

Isolation — Transactions Don’t Interfere

Definition:
Concurrent transactions should not affect each other’s operations.

Example: Simulate Two Sessions

Session 1 (User A)

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 201;
SELECT * FROM Accounts WHERE acc_no = 201;
Enter fullscreen mode Exit fullscreen mode

Session 2 (User B)

SELECT * FROM Accounts WHERE acc_no = 201;

Enter fullscreen mode Exit fullscreen mode

Result: Session 2 doesn’t see the uncommitted changes from Session1.
Now User A commits and checks again:


Change visible only after commit → Isolation works.

Durability — Permanent Once Committed

Definition:
Once a transaction is committed, its changes persist even if the database restarts.

Example: Commit and Verify

START TRANSACTION;
UPDATE Accounts SET balance = balance + 1500 WHERE acc_no = 203;  -- Vikram gains ₹1500
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Restart the database and run:

Summary

✅ Atomicity: No partial updates
✅ Consistency: Only valid data allowed
✅ Isolation: Transactions don’t interfere
✅ Durability: Changes survive system failures

ACID properties ensure databases are reliable, robust, and safe, making SQL transactions predictable and trustworthy.

SQL #Transactions #ACID #DatabaseDesign #Learning #DevCommunity

Top comments (0)