Naveens K

Posted on Oct 3

Understanding ACID Properties in SQL Transactions

#beginners #database #sql #architecture

Intro

When working with databases, data integrity is everything. Imagine transferring money from one account to another — you wouldn’t want money to disappear or double by mistake, right?

That’s where ACID properties come in. They ensure our transactions are reliable, safe, and consistent, even when things go wrong.

In this blog, let’s break down ACID with SQL examples using a simple Accounts table.

What is ACID?

ACID stands for:

Atomicity → All or nothing (no partial transactions).
Consistency → Data must remain valid before and after a transaction.
Isolation → Transactions run independently of each other.
Durability → Once committed, changes are permanent (even after crash).

Setting up a Table

We’ll create an Accounts table with a balance check to avoid negative values.

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

INSERT INTO Accounts VALUES
(101, 'Santhosh', 5000),
(102, 'Ganges', 3000),
(103, 'Rakshanth', 7000),
(104, 'Jaswant', 6000);

Atomicity

Atomicity ensures that either the entire transaction happens or nothing happens.

Example: Money transfer (Rollback midway)

START TRANSACTION;

-- Deduct ₹1000 from Alice
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;

-- Add ₹1000 to Bob
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;

-- Oops! Something went wrong
ROLLBACK;

-- Check balances (should remain unchanged)
SELECT * FROM Accounts;

Since we rolled back, no changes were applied. That’s Atomicity in action!

Consistency

Consistency ensures that a transaction brings the database from one valid state to another.

Example: Insert a record with negative balance

INSERT INTO Accounts VALUES (104, 'Naveens', -500);

This fails because of our CHECK (balance >= 0) constraint.
The database remains valid → Consistency maintained.

Isolation

Isolation ensures that transactions don’t interfere with each other.

Imagine two sessions running at the same time:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;
-- Don’t commit yet

Session 2:

SELECT * FROM Accounts WHERE acc_no = 103;

On READ COMMITTED → Session 2 sees old balance.
On READ UNCOMMITTED → Session 2 may see uncommitted changes (dirty read).
On SERIALIZABLE → Session 2 waits until Session 1 commits.

Different isolation levels decide how much transactions can "see" each other’s work.

Durability

Durability ensures that once a transaction is committed, it survives crashes.

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

Now restart your database and run:

SELECT * FROM Accounts WHERE acc_no = 102;

You’ll still see the updated balance.
That’s Durability — committed data is permanent.

Final Thoughts

The ACID properties are the backbone of reliable databases.

Atomicity → No partial updates
Consistency → Always valid data
Isolation → Independent transactions
Durability → Permanent changes

So next time you run a SQL transaction, remember: ACID is silently ensuring your data stays safe, even if things go wrong.

DEV Community