Transactions and ACID
Part of the free LogicWiz SQL & Databases course, Module 13: Transactions and concurrency. The queries and practice run in the app on real Postgres.
Aarav pays Shankar Stores ₹500 on UPI. The app takes ₹500 from Aarav, and then the server crashes before it adds ₹500 to the shop. The money is gone. How does a bank make "debit and credit" one single thing?
You'll learn to
- Group statements into one all-or-nothing transaction with BEGIN and COMMIT
- Undo work with ROLLBACK and roll back part of it with SAVEPOINT
- Explain what an aborted transaction is and why Postgres refuses more commands
- Explain A, C, I and D with a UPI example
Learn
All or nothing
A transaction is a group of statements that the database treats as one unit of work. Either every statement in it takes effect, or none of them does.
BEGIN;starts the transaction.COMMIT;makes all its changes permanent and visible to everyone.ROLLBACK;throws all its changes away, as if it never ran.
A UPI transfer is the classic example: "take ₹500 from Aarav" and "give ₹500 to Shankar Stores" must happen together or not at all. Half a transfer is the worst possible outcome.
A transfer as one transaction
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;
INSERT INTO upi_txns VALUES (21, 1, 3, 500.00, '2025-08-09 10:00', 'success', 'groceries');
COMMIT;
SELECT account_id, holder, balance
FROM accounts
WHERE account_id IN (1, 3)
ORDER BY account_id;
Three statements, one unit. If the server died after the first UPDATE, Postgres would undo it on restart, because the transaction never reached COMMIT.
ROLLBACK: undo everything since BEGIN
BEGIN;
UPDATE accounts
SET balance = 0
WHERE account_id = 3;
SELECT balance FROM accounts WHERE account_id = 3;
ROLLBACK;
SELECT balance FROM accounts WHERE account_id = 3;
Inside the transaction you see your own change (0.00). After ROLLBACK the shop has its ₹1,32,000 back. Click through the two result tables.
ACID, with one UPI payment
| Letter | Means | In the UPI transfer |
|---|---|---|
| Atomicity | All statements happen, or none do | Never debit without the credit |
| Consistency | Every rule (constraint) holds before and after | CHECK (balance = 0) is never broken; total money is unchanged |
| Isolation | Transactions running at the same time don't see each other's half-done work | Meher's balance report never sees money "in the air" |
| Durability | Once COMMIT returns, the change survives a crash or power cut | The app shows "Payment successful" only after COMMIT |
Postgres gets durability from its write-ahead log (WAL): the change is written to disk in the log before COMMIT returns. Module 16 opens the log up.
An error inside a transaction
BEGIN;
UPDATE accounts
SET balance = balance + 30000
WHERE account_id = 3;
UPDATE accounts
SET balance = balance - 30000
WHERE account_id = 1;
COMMIT;
Aarav has only ₹25,000, so the second UPDATE breaks CHECK (balance >= 0). The run stops there. The first UPDATE (the credit) is not kept: the whole transaction is now aborted.
An aborted transaction refuses everything until it ends
| Session A | Session B |
|---|---|
| BEGIN; | |
| UPDATE accounts SET balance = balance + 30000 WHERE accountid = 3; | |
| UPDATE accounts SET balance = balance - 30000 WHERE accountid = 1; | |
| SELECT balance FROM accounts WHERE accountid = 3; | |
| COMMIT; | |
| SELECT balance FROM accounts WHERE accountid = 3; |
SAVEPOINT: undo only the last part
BEGIN;
UPDATE accounts SET balance = balance - 999 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 999 WHERE account_id = 6;
SAVEPOINT before_tip;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 7;
ROLLBACK TO SAVEPOINT before_tip;
COMMIT;
SELECT account_id, balance
FROM accounts
WHERE account_id IN (1, 6, 7)
ORDER BY account_id;
A savepoint is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work after the bookmark; the gift to Sneha stays, the tip to Chai Point is undone. It is also how you recover from an error without losing the whole transaction.
Autocommit. Outside BEGIN … COMMIT, every statement is its own tiny transaction and commits at once. So two separate UPDATEs without BEGIN are two transactions: a crash between them leaves half a transfer. The opposite mistake is just as common: you type BEGIN in a SQL tool, forget COMMIT, and your changes sit invisible (and locked) until you close the window.
Exams ask for the states of a transaction: active (running) → partially committed (last statement done) → committed; or active → failed (an error) → aborted (rolled back, then restarted or killed). They also ask you to name the ACID property from a story, so practise that. MySQL: START TRANSACTION is the usual spelling (BEGIN works too), and only InnoDB tables have transactions; old MyISAM tables ignore ROLLBACK.
In one line
BEGIN … COMMIT makes statements all-or-nothing; ROLLBACK undoes them; SAVEPOINT lets you undo part; an error aborts the whole transaction.
Interview corner
What is ACID?
Atomicity: all statements of a transaction happen or none do. Consistency: every constraint holds before and after. Isolation: concurrent transactions don't see each other's unfinished work. Durability: once committed, the change survives a crash (Postgres writes it to the WAL before COMMIT returns).
The app debits one account and crashes before crediting the other. What happens?
If both updates were inside one BEGIN … COMMIT, the transaction never committed, so on restart Postgres treats it as rolled back: the debit vanishes. If they ran as two autocommit statements, the debit is already permanent and money is lost. That is why transfers must be one transaction.
What is a savepoint?
A named bookmark inside a transaction. ROLLBACK TO SAVEPOINT name undoes only the work after it and leaves the transaction open, which is also how you recover from an error in the middle of a long transaction without losing everything.