ACID properties mental model in SQL

Introduction

ACID properties help keep data safe and correct when many people use a database at the same time.

When you want to make sure money transfers between bank accounts are accurate and safe.

When multiple users update the same data, like booking seats for a concert.

When you want to avoid losing data if the computer crashes during a save.

When you need to keep data consistent after many changes.

When you want to make sure partial changes don't happen if something goes wrong.

Syntax

SQL

-- ACID is a set of rules, not a query syntax
-- It stands for:
-- A: Atomicity
-- C: Consistency
-- I: Isolation
-- D: Durability

ACID is not a command but a set of principles databases follow.

These properties work together to keep data reliable and safe.

Examples

This means both updates happen together or not at all.

SQL

-- Atomicity example
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Database keeps data rules true after changes.

SQL

-- Consistency example
-- A rule: balance cannot be negative
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- If balance < 0, rollback should happen (application or trigger logic required).

Changes don't mix up or interfere.

SQL

-- Isolation example
-- Two users update different accounts at the same time
-- Each sees their own changes only after commit.

Data is saved safely on disk.

SQL

-- Durability example
-- After COMMIT, changes stay even if power fails.

Sample Program

This simulates transferring 50 units from account 1 to account 2 safely.

SQL

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

SELECT id, balance FROM accounts ORDER BY id;

Important Notes

Atomicity means all parts of a change happen or none do.

Consistency means data rules are always true.

Isolation means changes don't mix up when many users work together.

Durability means once saved, data won't be lost.

Summary

ACID keeps data safe and correct in databases.

It stands for Atomicity, Consistency, Isolation, and Durability.

These properties work together to handle many users and failures smoothly.