Stop 06 · SQL DatabasesTopics · 7/14

ACID Properties

In short

When a database promises your data is safe, it means four specific things: ACID. Interviewers love this one, and it explains why banks trust SQL databases.

Atomicity, Consistency, Isolation, Durability — the four guarantees relational databases provide for transactions, ensuring your data stays correct even under failures or concurrent access.

The blob holds a fresh ticket at a reservation counter while another traveller waits behind a rope.

Think of it like this

Booking a berth

Your reservation fully books or fails (atomic). It never sells a seat on a full train (consistent). Two people tapping 'book' on the last berth can't both get it (isolated). Once your ticket is confirmed, a power cut can't erase it (durable).

Watch it happen

One booking, four promises

Atomic

Consistent

Isolated

Durable

1/5Booking reduces a seat and creates a ticket. If either fails, both are undone.

The big ideas

What you need to know

Atomicity

All steps of a transaction happen, or none do. There's no half-finished state.

Consistency

Every commit leaves data obeying your rules: keys, constraints and checks.

Isolation

Transactions running at the same time don't trip over each other. Isolation levels decide how strict this is.

Durability

Once COMMIT returns, the change survives crashes. Postgres writes it to a log on disk first.

See it in code

Walk through the code

sql

1BEGIN;2 3-- Lock the train's row so a parallel booking has to wait4SELECT seats_left FROM trains WHERE id = 7 FOR UPDATE;5 6UPDATE trains SET seats_left = seats_left - 17WHERE id = 7 AND seats_left > 0;8 9INSERT INTO tickets (train_id, passenger) VALUES (7, 'Riya');10 11COMMIT;

1/4Everything between BEGIN and COMMIT is atomic: seat and ticket go together.

In real code, check how many rows the UPDATE changed. If it's 0, the train is full: ROLLBACK and tell the user.

Save yourself the pain

Common mistakes

Oops

Thinking ACID means two users can never see odd results.

Do this instead

Isolation has levels. Postgres defaults to Read Committed; stricter levels like Serializable trade speed for safety.

Oops

Assuming every database gives ACID in the same way.

Do this instead

Many NoSQL stores relax some guarantees to scale. Read the docs before trusting one with money.

Your mission

Race for the last seat

~30 min

Quick check

COMMIT returns, then the server loses power. Which ACID property means the booking is still there?

Go deeper

1Explain each ACID letter in your own words with a concrete example.
2Learn how isolation levels (read committed, serializable, etc.) trade off consistency vs performance.
3Understand why ACID guarantees are a major reason teams choose SQL over NoSQL for critical data.