DBMS · Module 6 — Transactions & Concurrency
Transactions & ACID
A transaction is one unit of work that either fully happens or fully doesn't, and ACID names its four guarantees — atomicity, consistency, isolation, durability.
Aisha pays her fees online. The database does two things: takes 12,000 off her balance, and adds 12,000 to the college account.
Between those two steps, the power cuts.
Her money is gone. The college never received it. Nobody did anything wrong.
Why & what
Real work is rarely one statement. A payment is two updates. Booking a seat is three. And a half-finished job is often worse than a job never started — as Aisha just discovered.
So the database needs a way to treat several statements as one indivisible unit.
A transaction is a group of statements that the database treats as a single unit of work — it either all happens, or none of it does.
The guarantees a transaction gives you are remembered as ACID.
How it works
Aisha's payment, written properly:
BEGIN;
UPDATE Student SET fees_due = fees_due - 12000 WHERE roll_no = 101;
UPDATE College SET cash — = cash + 12000;
COMMIT;
- BEGIN — from here on, nothing is permanent yet. The database is keeping notes.
- The two updates run. Anyone else looking right now still sees the old values. The work is invisible until it's finished.
- COMMIT — both changes become real together. This is the moment they exist for everyone else.
- If anything fails before COMMIT — a crash, an error, or an explicit ROLLBACK — the database throws away both updates. Aisha's balance goes back to 12,000. Nothing half-done survives.
- Name the four guarantees: oAtomicity — all steps happen, or none do. This is what saves Aisha.
| o | Consistency — the database obeys its rules (Module 3's constraints) before and after. |
|---|---|
| o | Isolation — other transactions can't see your half-finished work. |
| o | Durability — once COMMIT returns, a crash cannot undo it. |
Pause here. Which letter of ACID is broken if the power cut leaves Aisha's money deducted and the college unpaid?
Atomicity. Only part of the unit survived.
Notice: a transaction is one unit of work — the database sees it whole or not at all.
Common confusion
Atomicity and consistency get mixed up constantly. Split them like this:
- Atomicity is about the steps — did all of them happen, or none?
- Consistency is about the rules — is the database still valid at the end?
A transaction can be perfectly atomic and still be refused for consistency, if what it tried to do broke a foreign key.
Second: durability doesn't mean "written to disk instantly." It means the database has done enough that a crash can't lose the change — usually by writing a log entry first, then updating the actual data later. You'll see why in Module 7.
Interview angle
- "Explain ACID." — Give one line each plus one example. The payment example covers atomicity and isolation naturally, which is more convincing than four definitions.
- "What's the difference between COMMIT and ROLLBACK?" — COMMIT makes every change in the transaction permanent; ROLLBACK discards all of them and restores the earlier state.
- "Give a real example where atomicity matters." — Any two-sided money movement: deduct here, add there. Half of it is corruption, not a partial success.
Quiz: skipped — reinforced above.
Recap
A transaction is one unit of work that either fully happens or fully doesn't, and ACID names its four guarantees — atomicity, consistency, isolation, durability.