Skip to content

Transactions

1. What is it? (2-3 lines)

A Database Transaction is a logical unit of work executing one or more database operations (SELECT, INSERT, UPDATE, DELETE) as a single atomic boundary. It guarantees that either all operations succeed completely or none take effect.

2. Why does it exist?

Real-world systems perform multi-step data mutations (e.g. transferring money from Account A to Account B requires decrementing A and incrementing B). Without transactions, system crashes, network failures, or concurrent writes midway through execution leave databases in corrupted, inconsistent states.

3. Where is it used?

  • Financial Systems: Payment processing, banking ledgers, e-commerce checkout.
  • Relational Databases (OLTP): PostgreSQL, MySQL (InnoDB), Oracle, CockroachDB.
  • Distributed Transaction Managers: Two-Phase Commit (2PC), Saga Orchestrators.

4. How does it work? (simple flow)

  1. Begin: Client issues BEGIN / START TRANSACTION.
  2. Buffer Operations: Database assigns a unique TxnID, acquires locks/latches, and writes log records to WAL memory buffers.
  3. Execute: Operations modify in-memory pages (Buffer Pool) and apply uncommitted state visible within the transaction context.
  4. Commit: Client issues COMMIT. Engine flushes WAL to non-volatile disk (fsync), releases row locks, and marks transaction as committed.
  5. Rollback: On error or ROLLBACK, engine uses Undo Logs / WAL to reverse uncommitted modifications and restore initial state.

5. Advantages

  • Enforces data integrity and ACID properties across multi-table operations.
  • Simplifies application error handling via atomic ROLLBACK.
  • Provides isolated views of data under concurrent user modifications.

6. Limitations

  • Long-running transactions hold locks, blocking concurrent writes and inflating undo log / WAL sizes.
  • Distributed transactions across microservices incur massive network latency and availability penalties.
  • High transaction volume increases lock contention, deadlocks, and CPU overhead.

7. Common interview questions (10)

  1. What are the 4 ACID guarantees provided by database transactions?
  2. How does a database execute a ROLLBACK operation under the hood?
  3. What is the difference between a Local Transaction and a Distributed Transaction?
  4. How do Two-Phase Commit (2PC) and the Saga Pattern handle distributed transaction boundaries?
  5. Why are long-running database transactions considered a critical anti-pattern in high-scale OLTP systems?
  6. How does SAVEPOINT allow partial rollbacks within a single transaction block?
  7. What is the difference between Autocommit mode and Explicit Transaction blocks?
  8. How does a database maintain transaction isolation when multiple transactions modify the same table concurrently?
  9. What happens to active transactions if the database server abruptly loses power?
  10. How do distributed SQL databases (CockroachDB/Spanner) coordinate transaction IDs across global nodes?

8. Common misconceptions

  • Misconception: Transactions require a relational SQL database. Fact: Distributed KV stores (FoundationDB, CockroachDB) and MongoDB support multi-document ACID transactions.
  • Misconception: COMMIT writes all changed data tables to disk. Fact: COMMIT flushes only the WAL log buffer to disk (fsync); data pages are updated in RAM and written asynchronously later.

10. 30-second cheat sheet

  • Definition: Atomic boundary grouping multiple SQL operations into a single All-or-Nothing execution unit.
  • Commit Flow: Append WAL \(\rightarrow\) fsync log to disk \(\rightarrow\) Release locks \(\rightarrow\) Return SUCCESS.
  • Rollback Flow: Read Undo Log / WAL \(\rightarrow\) Revert dirty memory updates \(\rightarrow\) Release locks \(\rightarrow\) Return ABORTED.
  • Rule of Thumb: Keep transactions ultra-short (<10ms) to prevent lock contention and undo log bloat!