SevyDevy

Module 4 · Storing Data That Keeps Growing · Lesson 14 of 35

What is ACID in DBMS? Why a booking is all or nothing

12 minFree System design course · Beginner to intermediateLesson 4.1By Saket Bhatnagar

Researched and drafted with AI, reviewed and edited by Saket Bhatnagar.

In short

  • What is ACID in DBMS? It is four promises a database makes about a transaction, a group of changes it saves together or not at all.
  • Like a UPI payment, it is all or nothing, it keeps your rules, it hides half-done work, and once saved it stays saved.
  • The promises cover only what you group together. Two saves sent one by one can be split by a crash.
Soon

Video lesson · coming soon

What is ACID in DBMS? Why a booking is all or nothing

Lala, your AI mentor, will walk you through this lesson in a short animated video. Until then, everything is here to read and try.

  1. 01 Paid ₹299, got no ticket
  2. 02 A UPI payment: both sides or neither
  3. 03 BEGIN, two writes, a crash
  4. 04 The four promises, with and without
  5. 05 Arjun’s fix in SQL
  6. 06 Next: why My bookings takes 4 seconds

Sale morning at the Kothrud office. Rohit, a fan in Nashik, picks seat A12 for match 9 and pays ₹299. His phone shows “Payment recorded”. No ticket arrives.

“Money gone, no ticket,” he messages Meera Kulkarni. His seat hold has run out, and the seats table never got its change, so A12 shows free. Arjun Nair finds why: a server restarted during a deploy, seconds after saving the payment.

He opens the database, which keeps data in tables of rows, one line per record. The payments table says Rohit paid for BK-10421. The seats table says A12 is “free”.

Which row is telling the truth? Can the database be trusted?

What is ACID in DBMS?

ACID is four promises a database makes about changes you save together. DBMS means database management system, the program that stores your data and guards it. A group of changes saved together is called a transaction.

For Rohit, the group is two changes: save the payment, and mark A12 sold.

Keep one line in your head: a transaction either fully happens or never happened. Each letter of ACID is one promise, and each gets its proper name further down.

  • A: all or nothing. Every change lands, or none does.
  • C: the rules hold. A change that breaks your rule is refused.
  • I: no peeking. Nobody sees a transaction half done.
  • D: saved stays saved. A crash cannot undo a finished one.

A booking transaction with a crash in the middle

A crash after write 1 rolls both writes back; with no crash, both become final together at COMMIT.

Diagram connections:

  • Begin transaction to Save payment row (First write)
  • Save payment row to Mark seat sold (No crash)
  • Mark seat sold to COMMIT (Both done)
  • Save payment row to Server crashes (Crash)
  • Server crashes to Rollback (Undo all)
  • Begin transaction: The transaction opens. Nothing is final yet.
  • Save payment row: Write 1: the ₹299 payment for BK-10421.
  • Mark seat sold: Write 2: seat A12 for match 9 is sold.
  • COMMIT: Both writes become final together.
  • Server crashes: The server dies between write 1 and write 2.
  • Rollback: The database undoes write 1, as if it never happened.

Neither change. The crash came before COMMIT, so the database undoes the payment row, as if Rohit never paid.

You have seen this when an app says “Payment failed”, your money is gone, and it returns a day later. Behind the screen, the payment and the order were two separate steps.

You have seen it when an app says “Confirmed”, your battery dies, and the booking is still there. Behind the screen, it was on disk before the app said so.

What is a database transaction, and what happens when the server crashes?

A transaction is a group of changes the database saves together or not at all. The app sends BEGIN, then the changes, then COMMIT, and ROLLBACK cancels the lot. In UPI terms, ROLLBACK is the bank reversing the payment.

  1. BEGIN

    The app opens a transaction for BK-10421. Nothing inside it is final yet.

  2. Write 1: the payment

    The ₹299 payment row for BK-10421 is saved as a draft.

  3. The crash

    The server dies before write 2. The A12 row is untouched.

  4. ROLLBACK

    The server’s link to the database drops with the transaction open, so the database throws write 1 away.

  5. No crash: COMMIT

    Write 2 marks A12 sold, and COMMIT makes both final at once.

How do the four promises protect a booking?

Each promise answers one way a booking could go wrong. Rohit was missing the first one.

With atomicity, Rohit’s payment row and seat A12 land together, or neither lands.

The C is not the consistency from requirements, where everyone gets the same answer. It means your written rules, like the bank’s “not below zero”.

The I is like two UPI payments at once: neither sees the other half done. At SeatJaldi, Meera’s report never counts a booking that is still being saved.

The D is durability, from lesson 5: saved stays saved.

Try it yourselfThe four promises, with and withoutFlip each card: a ₹299 booking without the promise, then with it.

What just happened? Each front looked fine to the app, and each fix works only inside a transaction.

What do engineers need to know about ACID?

ACID is a promise per transaction, not a switch for the whole database. In PostgreSQL, a statement (one SQL command) sent without BEGIN is its own transaction, committed when it succeeds.

That was Rohit’s bug, so Arjun wraps both writes in one BEGIN and COMMIT.

sql
BEGIN;
INSERT INTO payments (booking_id, amount_rupees, status)
VALUES ('BK-10421', 299, 'recorded');
UPDATE seats
SET status = 'sold', booking_id = 'BK-10421'
WHERE match_id = 9 AND seat_code = 'A12' AND status = 'free';
-- Reply UPDATE 1: commit. Reply UPDATE 0: send ROLLBACK instead.
COMMIT;

What just happened? The app reads how many rows the UPDATE changed. One means A12 was still free, so it commits. Zero means the seat is gone, so it rolls back, payment row and all.

If the server dies before COMMIT, the database rolls back on its own. If a statement fails, PostgreSQL refuses the rest, and COMMIT ends as a rollback.

How strict should no peeking be?

Stricter apartness costs waiting or retries, so you choose.

PostgreSQL’s isolation levels
LevelSees half-done work?Same row reads the same twice?Must the app be ready to retry?
Read uncommittedNo, acts as read committedNoNo
Read committed (default)NoNo, each statement sees fresh dataNo
Repeatable readNoYes, it reads one frozen view taken at its startYes
SerializableNoYesYes; results match some one-at-a-time order

Set a level right after BEGIN with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. A report of several queries that must agree, like Meera’s revenue report, needs repeatable read. Two bookings that each check a total and then write need serializable, because repeatable read can still let both through.

How does durability work?

Before changing its table files, the database notes each change in a running diary on disk. At COMMIT it waits only for the diary, which is written in order and cheap to save. After a crash, it replays the diary and redoes committed work the tables missed.

sql
ALTER TABLE payments
ADD CONSTRAINT amount_is_positive CHECK (amount_rupees > 0);

What just happened? The database cannot guard a rule it was never told. Adding the rule scans every existing row and blocks the table while it does. On a big table, add it NOT VALID, then run VALIDATE CONSTRAINT. A missing amount (NULL) still passes unless the column is NOT NULL.

Keep transactions short, because other work on the same rows may wait. Send BEGIN, the writes and COMMIT on one connection, the open line between app and database. A pool of connections can hand each query to a different one, and then nothing is grouped. The bank does not wait for the shop’s SMS, so send yours after COMMIT.

Try it yourself

  1. List the changes

    One booking needs a booking row for BK-10421, a ₹299 payment row, and A12 marked sold.

  2. Cross out the outsiders

    Remove anything outside the database, such as the SMS and the email.

  3. Write it

    BEGIN, the three changes, COMMIT, plus a ROLLBACK if the seat update changes zero rows.

  4. Crash it on paper

    The server crashes after the second change. What does the database hold?

  5. Check your answer

    Three changes sit between BEGIN and COMMIT, and the SMS comes after. After the crash: no booking row, no payment row, A12 still free. Anything outside the database inside the transaction is wrong.

Back to Rohit. Which row was telling the truth? Both were, each about half of one booking saved in two separate steps. Arjun wraps both writes in one transaction, then switches off the server after write 1 on purpose. The database rolls back: no payment row, A12 still free, and Rohit can tap Book again.

Like a UPI payment, a booking is now both sides or neither, so yes, the database can be trusted with it. Arjun still refunds Rohit’s first ₹299 by hand, because that money left his bank, and a rollback cannot reach a bank.

Meera has her line for investors, “A booking on SeatJaldi is all or nothing”. SeatJaldi has 10 lakh registered users, and she raises a ₹2 crore seed round. Bookings are safe now, but the “My bookings” page has started taking 4 seconds. Why would a safe database be slow to find one person’s bookings?

Check yourself

Check yourself

A transaction saves a payment row, then the server crashes before the seat update and before COMMIT. What does the database hold?

Pick an answer to see why it is right or wrong.

Check yourself

Meera runs the revenue report while a booking is half done. Which promise keeps it out of her total?

Pick an answer to see why it is right or wrong.

Check yourself

Arjun sends the payment insert and the seat update as two statements, with no BEGIN, and a crash comes between them. All or nothing?

Pick an answer to see why it is right or wrong.

Remember these three

  1. A transaction either fully happens or never happened; ACID is the database’s four promises about it.
  2. The promises tie together only the changes you put in one transaction.
  3. Keep transactions short and inside the database, and send the SMS after COMMIT.

Questions people ask

What is ACID in DBMS?
ACID is four promises a database makes about each transaction. Atomicity is all or nothing, consistency means rules hold, isolation means parallel work does not mix, and durability means committed data survives a crash.
What are ACID properties with an example?
In a ₹299 booking, atomicity saves the payment and the seat together or not at all. Consistency refuses a negative amount. Isolation hides half-done bookings, and durability keeps confirmed ones.
What is a database transaction?
A database transaction is a group of changes the database saves together or not at all. BEGIN starts it, COMMIT makes it final, and ROLLBACK undoes it all.

Sources and further reading