DEV Community

Cover image for The Backbone of Database Reliability: Exploring ACID in Action with SQL Transactions A Step-by-Step Guide
Thushitha TK
Thushitha TK

Posted on

The Backbone of Database Reliability: Exploring ACID in Action with SQL Transactions A Step-by-Step Guide

When working with relational databases, ACID properties ensure data is handled reliably, consistently, and safely.

ACID stands for:

  • Atomicity 🧩 – All-or-nothing transactions
  • Consistency ⚖️ – Database rules are preserved
  • Isolation 🚧 – Transactions operate independently
  • Durability 💪 – Committed transactions persist permanently

We’ll use an Accounts table to demonstrate each property step by step, with SQL examples you can try yourself.

⭐Step 1: Create the Accounts Table and Insert Sample Data

🧑‍💻CODE:

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, 'Calindra', 5800),
(102, 'Thalorin', 4200),
(103, 'Veylith', 6900);
SELECT * FROM Accounts;

Explanation:

  • acc_no is the primary key, ensuring each account is unique 🔑
  • balance has a CHECK constraint to prevent negative values ❌
  • Sample data gives three accounts to work with for transactions ✅

⭐Step 2: Atomicity 🧩 – All-or-Nothing Transactions

Goal: Ensure that if part of a transaction fails, no partial updates occur.
Scenario: Transfer 1500 from Calindra to Thalorin, but simulate an error midway.

🧑‍💻CODE:

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

Observation:

  • After ROLLBACK, all balances remain unchanged 🔄
  • Prevents incomplete transactions, critical in banking systems 💳

⭐Step 3: Consistency ⚖️ – Enforcing Data Rules

Goal: Ensure the database always remains in a valid state.
Scenario: Try to insert a record with a negative balance:

🧑‍💻CODE:

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Elarion', -2000);

Result:

Explanation:

  • Database constraints ❌ prevent invalid data
  • Consistency ensures all business rules and constraints are preserved

Tip 💡: Use constraints, triggers, and validations to maintain consistent data, especially in critical systems like finance, healthcare, or inventory.

⭐Step 4: Isolation 🚧 – Transactions Don’t Interfere

Goal: Ensure simultaneous transactions don’t cause conflicts.
Scenario: Simulate two sessions:

🧑‍💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 700 WHERE acc_no = 101;
SELECT balance FROM Accounts WHERE acc_no = 101;
COMMIT;

Session 1: Update an account but do not commit yet:

Session 2: Read the same account’s balance:

Observation:

  • Session 2 sees the original balance until Session 1 commits 🔒

Session 1 Commit:

  • After commit, Session 2 sees the updated balance ✅

Tip 💡: Different isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how strict transaction isolation is.

⭐Step 5: Durability 💪 – Committed Transactions Persist

Goal: Ensure committed transactions are permanent, even after a crash or restart.
Scenario: Update Veylith’s balance and commit:

🧑‍💻CODE:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2500 WHERE acc_no = 103;
COMMIT;
SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 103;

Observation:

  • After restart, Veylith’s balance retains the updated value 💾
  • Durability is typically ensured using write-ahead logs (WAL) and disk storage

💡 Closing Thoughts

In this guide, we explored ACID properties — Atomicity 🧩, Consistency ⚖️, Isolation 🚧, and Durability 💪 — using a simple Accounts table to illustrate real-world scenarios:

  • Atomicity: Transactions are all-or-nothing — rollback ensures no partial updates.
  • Consistency: Constraints like balance >= 0 keep data valid at all times.
  • Isolation: Concurrent transactions operate independently, preventing conflicts.
  • Durability: Committed changes survive database crashes and restarts.

By understanding and applying ACID principles, you can build robust, reliable, and secure database applications — whether in banking 💳, e-commerce 🛒, healthcare 🏥, or enterprise systems 🏢.

Mastering ACID not only ensures data integrity and safety but also gives you the confidence to handle real-world transactional challenges with SQL.

✅ Remember: ACID isn’t just a theory — it’s the backbone of trustworthy, dependable database systems.

Top comments (0)