Notes › DBMS
Transactions and ACID
A transaction is a group of operations that must succeed or fail as one unit.
Classic example — bank transfer of ₹500 from A to B:
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;
If the server crashes after the first UPDATE, money would vanish. A transaction guarantees that either both updates happen or neither does.
Commands
| Command | Effect |
|---|---|
START TRANSACTION / BEGIN |
start |
COMMIT |
make all changes permanent |
ROLLBACK |
undo everything since the start |
SAVEPOINT s1 |
a checkpoint inside the transaction |
ROLLBACK TO s1 |
undo only back to the savepoint |
BEGIN;
INSERT INTO orders VALUES (101, 'A', 999);
SAVEPOINT after_order;
UPDATE stock SET qty = qty - 1 WHERE item = 'pen';
ROLLBACK TO after_order; -- undo the stock change, keep the order
COMMIT;
By default most databases run in autocommit mode — every statement is its own transaction.
ACID
| Property | Meaning | How the DB guarantees it |
|---|---|---|
| Atomicity | all or nothing | undo log — roll back partial work |
| Consistency | data moves from one valid state to another (constraints, keys, checks hold) | constraints + correct transaction logic |
| Isolation | concurrent transactions don't see each other's half-done work | locking or MVCC |
| Durability | once committed, it survives crashes | write-ahead log (WAL) / redo log flushed to disk before commit returns |
- In the transfer: atomicity = no half transfer; consistency = total money unchanged and balance ≥ 0; isolation = another query never sees A debited but B not credited; durability = after "Transfer successful", a power cut doesn't undo it.
Write-ahead logging
Before a change is written to the data files, a log record describing it is written to disk. After a crash, the DB redoes committed transactions and undoes uncommitted ones from the log.
Transaction states
Active → Partially committed → Committed
↓ ↓
Failed → Aborted (rolled back) → restart or kill
Concurrency problems (read anomalies)
| Problem | What happens |
|---|---|
| Dirty read | T2 reads data T1 changed but hasn't committed; T1 rolls back → T2 used fake data |
| Non-repeatable read | T1 reads a row twice; T2 updates it in between → different values |
| Phantom read | T1 runs the same query twice; T2 inserts/deletes rows in between → different set of rows |
| Lost update | T1 and T2 both read 100, both add → one write overwrites the other |
Isolation levels (SQL standard)
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | possible | possible | possible |
| Read Committed | ✗ | possible | possible |
| Repeatable Read | ✗ | ✗ | possible* |
| Serializable | ✗ | ✗ | ✗ |
Higher isolation = safer but slower (more locking/aborts).
- Default in PostgreSQL / Oracle / SQL Server: Read Committed.
- Default in MySQL InnoDB: Repeatable Read (*InnoDB also prevents most phantoms with gap locks).
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Fixing a lost update
-- lock the row while you read it
SELECT balance FROM accounts WHERE id = 'A' FOR UPDATE;
-- or do it in one atomic statement
UPDATE accounts SET balance = balance - 500 WHERE id = 'A' AND balance >= 500;
Optimistic locking alternative: keep a version column and UPDATE … WHERE id = ? AND version = ?; if 0 rows change, someone else won — retry.
MVCC (multi-version concurrency control)
Instead of blocking readers, the DB keeps several versions of a row. Each transaction reads a consistent snapshot, so readers don't block writers and writers don't block readers. Used by PostgreSQL, MySQL InnoDB, Oracle.
Transactions in practice (Node + MongoDB)
MongoDB has multi-document transactions (needs a replica set):
const session = await mongoose.startSession();
try {
await session.withTransaction(async () => {
await Account.updateOne({ _id: a }, { $inc: { balance: -500 } }, { session });
await Account.updateOne({ _id: b }, { $inc: { balance: 500 } }, { session });
});
} finally {
session.endSession();
}
Single-document updates in MongoDB are already atomic — design documents so most writes touch one document.
Distributed transactions (brief)
Across multiple databases/services: two-phase commit (2PC) — a coordinator asks all participants to prepare, then tells all to commit (or abort). Microservices often use the Saga pattern instead: a chain of local transactions with compensating actions to undo.
Interview one-liners
- ACID = Atomicity, Consistency, Isolation, Durability.
- Atomicity via undo logs; durability via write-ahead/redo logs; isolation via locks or MVCC.
- Read Committed stops dirty reads; Repeatable Read stops non-repeatable reads; Serializable stops phantoms.
SELECT … FOR UPDATElocks rows to avoid lost updates.- Related: Concurrency Control, Schedules and Locking, Indexing, B+ Trees and SQL vs NoSQL.