Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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

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).

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

More DBMS notes