Transactions

A transaction is a sequence of operations performed as a single logical unit of work. It either completes entirely (commit) or has no effect at all (rollback) — preserving the ACID guarantees, especially atomicity.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Define what a database transaction is and explain the fundamental guarantees it provides through the ACID properties.
• Trace a transaction through its complete lifecycle of states: Active → Partially Committed → Committed (or Failed → Aborted → Terminated).
• Compare and contrast the four SQL isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and the anomalies each one prevents.
• Explain the role of the Write-Ahead Log (WAL), undo logs, and redo logs in guaranteeing atomicity and durability even during system crashes.
• Differentiate between pessimistic concurrency control (locking-based) and optimistic concurrency control (MVCC / timestamp-based), and evaluate when each is appropriate.
• Implement transactional logic in application code using correct patterns for commit, rollback, and savepoint management.

2. Prerequisites

To get the most out of this lesson, you should be familiar with:

• Databases and DBMS: Understanding of how a DBMS manages data, including the query processing pipeline, buffer pool, and storage engine.
• SQL Fundamentals: Comfort writing SELECT, INSERT, UPDATE, and DELETE statements.
• Concurrency Basics: Familiarity with concepts like threads, race conditions, and mutual exclusion from operating systems or programming fundamentals.
• Disk I/O Patterns: Understanding the latency difference between writing to RAM vs. persistent disk storage (SSD/HDD), and why data loss can occur on power failure if data is only in volatile memory.

3. Why This Topic Matters

Every meaningful operation in a production system — transferring money between bank accounts, placing an e-commerce order, reserving a flight seat, updating inventory — relies on transactions to guarantee correctness. Without transactional guarantees, concurrent operations can corrupt data, partial failures can leave the system in an inconsistent state, and crashes can cause permanent data loss.

Consider the classic example: transferring $500 from Account A to Account B. This involves two writes — a debit from A and a credit to B. If the system crashes between these two writes without transactional protection, $500 vanishes from the financial system. This is not a theoretical edge case; it happens billions of times per day across global banking infrastructure. Transactions are the foundational mechanism that makes modern digital finance, e-commerce, healthcare records, and every critical system trustworthy.

In system design interviews, understanding transactions is essential because interviewers evaluate whether you can reason about data correctness under concurrency and failure conditions. Without this knowledge, candidates cannot properly design databases, avoid double-spend bugs, or architect systems that survive crashes gracefully.

4. Real-world Analogy

Imagine you are at a physical bank branch, standing at the teller's window to transfer $500 from your savings account to your checking account. The teller performs the following steps:

1. The teller opens a ledger page and writes a header: "Transfer #4821 — In Progress". (Transaction begins: Active state)
2. The teller subtracts $500 from the savings line. (First operation: debit)
3. The teller adds $500 to the checking line. (Second operation: credit)
4. The teller reviews both entries, verifies the math, and stamps the page as "Approved". (Partially Committed: all operations done, awaiting final confirmation)
5. The teller hands you a receipt and files the ledger page permanently. (Committed: changes are durable)

Now consider the failure scenario: if a fire alarm goes off between steps 2 and 3, the teller has already deducted $500 from savings but has not yet credited checking. A well-designed bank does not leave you with missing money. Instead, the teller tears up the entire ledger page, restoring both accounts to their original balances. This is a rollback. The key principle: either the entire transfer happens (both debit and credit), or none of it happens. There is no state where money disappears.

This is exactly how a database transaction works: the database engine uses logs to track in-progress operations, and if anything fails before the final commit, it undoes every change made so far, leaving the database as if the transaction never started.

5. Core Concepts

Understanding transactions requires mastering several interconnected concepts:

ACID Properties

ACID is the set of four guarantees that every transaction in a reliable DBMS must satisfy:

• Atomicity: A transaction is an indivisible unit. Either all of its operations are applied, or none are. If any operation within the transaction fails, the entire transaction is rolled back, and the database state is restored to what it was before the transaction began. This is enforced using undo logs.
• Consistency: A transaction transitions the database from one valid state to another valid state. All integrity constraints, foreign key relationships, uniqueness rules, and check constraints must hold after the transaction completes. If a transaction would violate any constraint, it is aborted.
• Isolation: Concurrent transactions must execute as if they were running sequentially (serially), even when they are physically interleaved. Each transaction must be isolated from the effects of other uncommitted transactions. The degree of isolation is configurable via isolation levels.
• Durability: Once a transaction is committed, its changes are permanent and survive any subsequent system failure — power outages, OS crashes, or hardware failures. This is achieved through the Write-Ahead Log (WAL) and flushing log records to durable storage before acknowledging the commit.

Transaction States

A transaction moves through a well-defined state machine during its lifecycle:

• Active: The initial state. The transaction is executing its read and write operations. It remains active as long as operations continue successfully.
• Partially Committed: The final operation of the transaction has executed, but the changes have not yet been written to durable storage. The transaction is in a vulnerable window — a crash here could still cause a rollback.
• Committed: All changes have been permanently recorded (log flushed to disk). The transaction is now complete and its effects are visible to other transactions. This state is irreversible.
• Failed: A runtime error, constraint violation, deadlock, or explicit abort command has prevented the transaction from continuing normal execution.
• Aborted: The database has rolled back all changes made by the failed transaction using the undo log. The database is restored to the state before the transaction started. At this point, the system can either restart the transaction or terminate it.
• Terminated: The transaction has exited the system. This is the final terminal state after either a commit or an abort.

Commit and Rollback

• COMMIT: The explicit command that finalizes a transaction. Once committed, the WAL record is flushed to disk, guaranteeing that the changes survive any failure. All locks held by the transaction are released.
• ROLLBACK: The explicit command that aborts a transaction. The DBMS reads the undo log to reverse every write operation the transaction performed, restoring the original data values. All locks are released.
• SAVEPOINT: A named marker within a transaction that allows partial rollback. You can roll back to a savepoint without aborting the entire transaction, which is useful for complex multi-step operations where you want retry semantics within a single transaction boundary.

Isolation Levels

The SQL standard defines four isolation levels, each trading off consistency for concurrency performance:

• Read Uncommitted: The weakest level. A transaction can read data written by other uncommitted transactions (dirty reads). Rarely used in production due to data corruption risks.
• Read Committed: A transaction can only read data that has been committed by other transactions. Prevents dirty reads but allows non-repeatable reads (re-reading the same row may yield different values if another transaction committed in between). This is the default in PostgreSQL and Oracle.
• Repeatable Read: Guarantees that if a transaction reads a row, re-reading it will return the same value, even if other transactions commit changes. Prevents dirty reads and non-repeatable reads, but allows phantom reads (new rows inserted by other transactions may appear). This is the default in MySQL/InnoDB.
• Serializable: The strongest isolation level. Transactions execute as if they were running one after another in some serial order. Prevents all anomalies (dirty reads, non-repeatable reads, phantom reads) but has the highest performance cost due to aggressive locking or conflict detection.

6. Visualization

The following diagram illustrates the complete transaction state machine, showing the lifecycle from initiation through either successful commit or failure-driven rollback.

7. How It Works

Let us walk through the complete lifecycle of a transaction step by step, using a bank transfer of $500 from Account A to Account B as the example:

1. BEGIN TRANSACTION: The application issues a BEGIN statement. The DBMS assigns a unique transaction ID (e.g., txn_id=4821), records it in the transaction table, and sets the state to Active. A timestamp or log sequence number (LSN) is assigned for ordering.
2. Acquire Locks: Before modifying Account A, the Lock Manager acquires an exclusive (write) lock on Account A's row. If another transaction holds a conflicting lock, the current transaction waits in a queue or is aborted to prevent deadlock.
3. Write Undo Log Entry: Before modifying the data page in the buffer pool, the DBMS writes an undo log record: [txn_4821, Account_A, old_balance=$2000]. This record allows the system to reverse the change if the transaction fails.
4. Apply Debit Operation: The buffer pool page for Account A is modified in-memory: balance changes from $2000 to $1500. The page is marked "dirty" in the buffer pool.
5. Write WAL (Redo Log) Entry: A WAL record is written: [txn_4821, Account_A, SET balance=1500]. This ensures the change can be replayed during crash recovery even if the dirty page hasn't been flushed to disk yet.
6. Acquire Lock on Account B: The Lock Manager acquires an exclusive lock on Account B's row.
7. Write Undo Log for Account B: [txn_4821, Account_B, old_balance=$3000].
8. Apply Credit Operation: Account B's balance is updated in the buffer pool from $3000 to $3500.
9. Write WAL Entry for Credit: [txn_4821, Account_B, SET balance=3500].
10. Partially Committed State: All operations have executed successfully. The transaction enters the Partially Committed state. The DBMS now needs to finalize the commit.
11. Force WAL Flush: The DBMS performs a forced write (fsync) of all WAL records for txn_4821 to durable storage. This is the critical durability point — once the WAL records hit the disk platter, the transaction's effects are guaranteed to survive a crash.
12. Mark Committed: A commit record [COMMIT txn_4821] is written to the WAL. The transaction state transitions to Committed.
13. Release All Locks: The Lock Manager releases the exclusive locks on Account A and Account B. Other waiting transactions can now proceed.
14. Acknowledge Client: The DBMS sends a success response to the application. The transaction enters the Terminated state.
15. Background Page Flush (Lazy): At some later point, the buffer pool manager's background checkpoint process writes the dirty pages for Account A and Account B back to the data files on disk. This is not time-critical because the WAL already guarantees durability.

8. Internal Architecture

The transaction subsystem inside a DBMS is composed of several tightly integrated components. Below is a detailed breakdown of each component, its responsibilities, and its failure modes:

9. Request Lifecycle

Below is the end-to-end lifecycle of a transactional request as it flows through the DBMS from an application's perspective:

1. Application Layer: The application opens a database connection and sends BEGIN TRANSACTION.
2. Connection Manager: The DBMS accepts the connection, authenticates the user, and creates a session context for the transaction.
3. Transaction Manager: A new transaction ID is assigned, the transaction is registered in the active transaction table, and the state is set to Active.
4. SQL Parser: Subsequent SQL statements (UPDATE accounts SET balance = balance - 500 WHERE id = 'A') are parsed into an Abstract Syntax Tree (AST).
5. Query Optimizer: The optimizer generates the most efficient execution plan (e.g., index lookup on the primary key).
6. Execution Engine: The plan is executed. The engine requests the Lock Manager to acquire appropriate locks on affected rows.
7. Lock Manager: Grants locks if no conflicts exist. If conflicts exist, the transaction either waits or is aborted based on the deadlock detection strategy.
8. Log Manager: Before modifying any page, the engine writes undo and redo log entries to the WAL buffer.
9. Buffer Pool: The relevant data page is fetched (from cache or disk), the row is modified in-memory, and the page is marked dirty.
10. Repeat for Additional Statements: Steps 4–9 repeat for each SQL statement within the transaction.
11. COMMIT Request: The application sends COMMIT. The Log Manager flushes all WAL records to durable storage (fsync). A commit record is appended.
12. Lock Release: All locks held by the transaction are released. The transaction state moves to Committed then Terminated.
13. Response: A success acknowledgment is returned to the application. Dirty pages are flushed to disk lazily by the checkpoint process.

10. Deep Dive

Concurrency Control Strategies

The concurrency control mechanism determines how the DBMS manages simultaneous access by multiple transactions. The two dominant paradigms are:

Two-Phase Locking (2PL)

The gold standard for pessimistic concurrency control. A transaction acquires locks during its growing phase and releases them during its shrinking phase. The critical rule: once a transaction releases any lock, it cannot acquire new ones. This guarantees serializability but can cause significant contention.

• Strict 2PL: All exclusive (write) locks are held until the transaction commits or aborts. This prevents cascading aborts (where aborting one transaction forces aborting others that read its uncommitted data).
• Rigorous 2PL: All locks (both shared and exclusive) are held until commit/abort. This simplifies reasoning about correctness at the cost of reduced concurrency.

Multi-Version Concurrency Control (MVCC)

Used by PostgreSQL, MySQL/InnoDB, Oracle, and most modern databases. Instead of blocking readers with locks, MVCC maintains multiple versions of each row. When a transaction modifies a row, it creates a new version rather than overwriting the existing one. Readers access the version that was current at their transaction's start time (snapshot isolation).

• Advantage: Readers never block writers, and writers never block readers. This dramatically improves concurrency for read-heavy workloads.
• Cost: Old versions accumulate and must be garbage collected (PostgreSQL's VACUUM, InnoDB's purge thread). Version chain bloat can degrade performance if garbage collection falls behind.
• Write-Write Conflicts: MVCC does not eliminate all conflicts. Two concurrent transactions updating the same row still require conflict resolution (typically, the second writer is blocked or aborted).

Isolation Anomalies in Detail

Write-Ahead Logging (WAL) and ARIES Recovery

The Write-Ahead Log (WAL) is the backbone of both atomicity and durability. The WAL protocol mandates: no data page may be written to disk until all log records describing modifications to that page have been flushed to the log on stable storage.

The ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) protocol is the industry-standard crash recovery algorithm used by most modern DBMS engines. It operates in three phases:

1. Analysis Phase: Scan the WAL forward from the last checkpoint to determine: (a) which transactions were active at crash time (the "loser" transactions), and (b) which dirty pages might not have been flushed to disk.
2. Redo Phase: Replay all logged operations (for both committed and uncommitted transactions) to bring the database to the exact state it was in at the moment of the crash. This is necessary because some committed changes may not have been written to data files yet.
3. Undo Phase: Roll back all uncommitted ("loser") transactions by applying their undo log records in reverse order. This restores the database to a consistent state containing only the effects of committed transactions.

11. Production Example

Stripe's Payment Processing Pipeline

Stripe processes hundreds of millions of payment transactions per day, where correctness is non-negotiable. A single duplicated charge or missing refund has direct financial and legal consequences. Here is how transactions play a central role:

• Idempotency Keys: Every API request includes a unique idempotency key. Stripe uses a database transaction to atomically check whether the key already exists and, if not, insert it and begin processing. This prevents double-charges from network retries.
• Multi-Table Atomicity: A payment involves writes to multiple tables: charges, balance_transactions, transfers, and ledger entries. All of these are wrapped in a single database transaction. If any write fails (e.g., insufficient funds), the entire set of writes is rolled back.
• Serializable Isolation for Balance Checks: For critical operations like balance checks before payouts, Stripe uses the highest isolation level to prevent race conditions where two concurrent payout requests both read a sufficient balance and both proceed, resulting in an overdraft.
• WAL-based Durability: Stripe's PostgreSQL instances use synchronous WAL replication to standby replicas. A commit is acknowledged only after the WAL record has been persisted on at least two nodes, ensuring that no committed payment data is lost even if the primary server is destroyed.

Amazon DynamoDB Transactions

Amazon's DynamoDB introduced transaction support (TransactWriteItems, TransactGetItems) to provide ACID guarantees across up to 100 items within or across tables in a single AWS region. This enables use cases like:

• Maintaining a gaming leaderboard where a player's score, rank, and achievements must all update atomically.
• Processing e-commerce orders where inventory decrement, order creation, and payment status update must be all-or-nothing.
• DynamoDB transactions use an optimistic concurrency control model internally — reads and writes in a transaction are validated for conflicts at commit time, and the transaction is rejected if any item was modified concurrently.

12. Advantages

• Data Integrity: Transactions guarantee that the database never contains partial or inconsistent results, even after failures. Constraints are enforced atomically.
• Crash Recovery: The WAL and undo log provide a deterministic mechanism to recover from any type of crash (process, OS, hardware) without data loss for committed transactions.
• Concurrency Safety: Isolation levels provide a formal, well-understood framework for managing concurrent access. Developers can choose the appropriate level based on their consistency and performance requirements.
• Simplified Application Logic: Without transactions, the application layer would need to implement its own rollback logic, conflict detection, and crash recovery — complex, error-prone, and nearly impossible to get right at scale.
• Composability: Multiple operations can be grouped into a single atomic unit, allowing complex business logic (e.g., order processing involving inventory, payment, and shipping) to be expressed cleanly.
• Auditability: Transaction logs (WAL) provide a complete, ordered history of all data modifications, which is invaluable for debugging, compliance, and regulatory requirements in finance and healthcare.

13. Limitations

• Performance Overhead: Transactions introduce overhead from lock acquisition/release, WAL writes (synchronous disk I/O on commit), and undo log maintenance. High-throughput write workloads can be significantly bottlenecked by WAL flush latency.
• Scalability Ceiling: Transactions that span many rows or tables acquire many locks, reducing concurrency. Long-running transactions hold locks for extended periods, blocking other transactions and creating queuing delays.
• Deadlocks: When two or more transactions form a circular wait dependency on locks, the system must detect the deadlock and abort at least one transaction (the "victim"). This adds complexity and can cause unpredictable latency spikes.
• Single-Node Scope: Traditional ACID transactions operate within a single database instance. They do not natively span multiple databases, services, or microservices. Extending transactional guarantees across distributed systems requires specialized protocols (2PC, Sagas) with their own tradeoffs.
• Serializable Costs: The strongest isolation level (Serializable) can dramatically reduce throughput by requiring all concurrent transactions to be compatible with some serial execution order. In practice, most systems default to weaker levels (Read Committed) and handle edge cases at the application layer.
• MVCC Bloat: In MVCC systems, old row versions accumulate until garbage collected. Under high update rates, version chain bloat can cause table bloat (increased storage), slower sequential scans, and the need for periodic maintenance (VACUUM in PostgreSQL).

14. Trade-offs

Transaction design involves several fundamental engineering trade-offs:

15. Performance Considerations

• WAL Flush Latency: The synchronous fsync call on commit is typically the single largest latency contributor. On rotating disks, this can be 5-10ms; on NVMe SSDs, it drops to 50-200μs. Using group commit (batching multiple transactions' WAL records into a single fsync) can improve throughput by 10-50x.
• Lock Contention: Hot rows (e.g., a global counter, a popular product's inventory count) become serialization bottlenecks. All concurrent transactions wanting to update the same row must queue. Solutions include sharding the counter, using atomic increment operations, or application-level batching.
• Transaction Duration: Keep transactions as short as possible. A transaction that holds locks for 100ms while making an external API call blocks all other transactions on those rows for that entire duration. Never perform network I/O, user interaction, or expensive computation inside a transaction boundary.
• Connection Pool Sizing: Each active transaction occupies a database connection. If the connection pool is too small relative to the number of concurrent transactions, requests queue at the application layer. If it is too large, the database's memory and CPU are overwhelmed by context switching.
• MVCC Garbage Collection: In PostgreSQL, unvacuumed dead tuples cause table bloat, slower sequential scans, and index bloat. In MySQL/InnoDB, the undo log purge thread must keep pace with the update rate. Monitor pg_stat_user_tables.n_dead_tup (PostgreSQL) or History list length (InnoDB) as key health metrics.
• Index Maintenance: Each index on a modified column must be updated within the transaction. Tables with many indexes experience higher write latency. Consider dropping non-essential indexes for write-heavy tables.
• Checkpoint Frequency: More frequent checkpoints reduce crash recovery time but increase I/O load during normal operation. Less frequent checkpoints reduce I/O overhead but extend recovery time after a crash.

16. Failure Scenarios

Understanding how transactions behave under failure is critical for building reliable systems:

• Process Crash Mid-Transaction: The database process terminates (e.g., OOM kill) while a transaction is Active. On restart, the Recovery Manager reads the WAL, identifies the uncommitted transaction (no COMMIT record found), and applies the undo log to roll back all its changes. The database returns to a consistent state.
• Power Failure After Partial Commit: The crash occurs after the WAL commit record is flushed but before all dirty pages are written to data files. On restart, the ARIES redo phase replays the logged operations from the WAL onto the data files, ensuring all committed changes are durably reflected.
• Disk Full on WAL Partition: If the WAL disk runs out of space, the DBMS cannot write new log records and therefore cannot commit any new transaction. All active transactions will stall and eventually time out. Operations teams must immediately free disk space or expand storage. This is a critical monitoring alert.
• Deadlock Detection: Transaction T1 holds Lock A and requests Lock B; Transaction T2 holds Lock B and requests Lock A. The Lock Manager detects this cycle (typically within milliseconds) and selects a victim transaction to abort. The victim is rolled back, its locks are released, and the other transaction proceeds. Applications must be prepared to retry aborted transactions.
• Long-Running Transaction Blocks All Others: A transaction that runs for minutes (e.g., a large batch update, an open transaction forgotten by a developer) holds locks for its entire duration. This blocks all other transactions attempting to access the same data, causing cascading timeouts across the application. Monitoring for long-running transactions and setting statement timeouts is essential.
• Replication Lag with Read-Your-Writes: In primary-replica setups, a transaction commits on the primary, but the replica hasn't received the WAL record yet. A read query directed to the replica returns stale data. Solutions include routing reads to the primary after writes, or using synchronous replication (at the cost of write latency).

17. Best Practices

1. Keep Transactions Short: Minimize the time between BEGIN and COMMIT. Do all computation and validation before opening the transaction. Never include network calls, file I/O, or user prompts inside transaction boundaries.
2. Use the Appropriate Isolation Level: Default to Read Committed for most workloads. Use Repeatable Read or Serializable only for operations that require them (e.g., financial balance checks, inventory reservations). Always explicitly set the isolation level rather than relying on server defaults.
3. Always Handle Transaction Errors: Wrap transactions in try-catch-finally blocks. On any exception, explicitly ROLLBACK. Never leave a transaction open — this holds locks and connections indefinitely.
4. Use Connection Pooling: Maintain a pool of database connections (e.g., PgBouncer, HikariCP). This prevents connection exhaustion and reduces the overhead of establishing new connections for each transaction.
5. Implement Retry Logic: Transactions may be aborted due to deadlocks, serialization failures, or transient errors. Implement exponential backoff retry logic with jitter. Limit the number of retries to avoid infinite loops.
6. Use Savepoints for Complex Logic: When a transaction involves multiple independent steps (e.g., inserting a user, creating a profile, sending a welcome email record), use savepoints to allow partial retry without aborting the entire transaction.
7. Monitor Transaction Metrics: Track commit rate, rollback rate, average transaction duration, lock wait time, deadlock count, and WAL flush latency. Set alerts for long-running transactions (>5 seconds) and high deadlock rates.
8. Use Idempotency Keys: For transactions triggered by external events (API calls, message queue consumption), use idempotency keys to ensure that replaying the same event does not create duplicate records.
9. Avoid Holding Locks Across User Interactions: Never open a transaction, show data to a user, wait for the user to make a decision, and then commit. This pattern holds locks for seconds to minutes (or indefinitely), destroying database throughput.
10. Set Statement and Transaction Timeouts: Configure statement_timeout and idle_in_transaction_session_timeout (PostgreSQL) to automatically kill runaway transactions that exceed expected durations.

18. Common Mistakes

• Not Using Transactions at All: Performing multi-step operations (e.g., deducting inventory and creating an order) as separate, auto-committed statements. If the second step fails, the database is left in an inconsistent state with no automated recovery.
• Opening Transactions Too Early: Beginning a transaction before doing input validation, API calls, or computations. The transaction holds locks while non-database work is performed, unnecessarily increasing contention and lock duration.
• Forgetting to COMMIT or ROLLBACK: Leaving a transaction open (especially in interactive database clients like psql or application code with connection pooling). The open transaction holds locks and prevents autovacuum from cleaning up dead tuples.
• Making Network Calls Inside Transactions: Calling an external API (e.g., payment gateway, email service) within a transaction boundary. If the API call takes 5 seconds due to network latency, the transaction's locks are held for 5 seconds, blocking all concurrent access.
• Using Too High an Isolation Level: Setting Serializable isolation globally when only a few specific operations require it. This causes unnecessary aborts and retries for all transactions, significantly reducing throughput.
• Ignoring Deadlock Handling: Not implementing retry logic for deadlock errors. The application receives a deadlock error, logs it, and returns a 500 error to the user instead of transparently retrying the operation.
• Large Batch Operations in a Single Transaction: Updating millions of rows in a single UPDATE within one transaction. This acquires millions of row locks, fills the WAL with enormous log entries, and blocks all other access to those rows. Instead, batch updates into smaller chunks (e.g., 1,000 rows per transaction).
• Confusing Application-Level and Database-Level Transactions: Some ORMs (e.g., Django, Hibernate) auto-wrap requests in transactions. Developers may not realize their code is already inside a transaction and issue redundant BEGIN statements, leading to unexpected nesting behavior.

19. Implementation

Below are complete, production-quality implementations demonstrating transaction management in SQL, TypeScript (with Node.js and PostgreSQL), and Python.

SQL: Bank Transfer with Savepoints

TypeScript: Transaction with Retry Logic (Node.js + pg)

Python: Transaction with Context Manager (psycopg2)

20. Interview Questions

Easy

Q1: What are the four ACID properties, and which one is most directly responsible for ensuring that a failed transaction doesn't leave partial changes?

Answer: The four ACID properties are Atomicity, Consistency, Isolation, and Durability. Atomicity is directly responsible — it guarantees that a transaction is all-or-nothing. If any part of the transaction fails, the DBMS uses the undo log to reverse all changes made so far, leaving the database as if the transaction never started.

Q2: What is the difference between COMMIT and ROLLBACK?

Answer: COMMIT finalizes a transaction, making all its changes permanent by flushing the WAL to disk and releasing all locks. Once committed, changes survive any subsequent crash. ROLLBACK aborts a transaction, using the undo log to reverse every change made during the transaction and restore the database to its pre-transaction state. All locks are released without persisting any changes.

Medium

Q3: Explain the difference between a dirty read and a phantom read. At which isolation levels is each prevented?

Answer: A dirty read occurs when transaction T2 reads data written by T1 before T1 has committed. If T1 subsequently aborts, T2 has read and potentially acted on data that never officially existed. Dirty reads are prevented at Read Committed and all higher isolation levels. A phantom read occurs when T1 executes a range query (e.g., SELECT * FROM orders WHERE status='pending'), then T2 inserts a new row matching that range and commits, and T1 re-executes the same query and sees the new "phantom" row that wasn't there before. Phantom reads are only prevented at the Serializable isolation level (or in systems that implement gap locking at Repeatable Read, like MySQL/InnoDB).

Q4: Why do modern databases use MVCC instead of strict Two-Phase Locking for concurrency control?

Answer: MVCC provides significantly better concurrency for read-heavy workloads because readers never block writers and writers never block readers. In 2PL, a write lock on a row blocks all concurrent reads of that row, which is unacceptable for applications with high read-to-write ratios (which is the vast majority of real-world applications). MVCC achieves this by maintaining multiple versions of each row — readers access the version visible at their snapshot timestamp, while writers create new versions. The trade-off is the need for garbage collection of old versions (e.g., PostgreSQL's VACUUM), which adds operational complexity.

Hard

Q5: Explain the ARIES crash recovery algorithm's three phases and why the redo phase replays operations for both committed AND uncommitted transactions.

Answer: ARIES has three phases: (1) Analysis — scans the WAL from the last checkpoint forward to identify which transactions were active (uncommitted) at crash time and which pages might be dirty. (2) Redo — replays all logged operations forward from the oldest dirty page's LSN, regardless of whether the transaction committed or not. This phase must redo uncommitted operations because the WAL uses physiological logging — log records may depend on the physical state of pages that could have been partially flushed. Skipping uncommitted operations could leave pages in a state where subsequent undo operations produce incorrect results. (3) Undo — rolls back all uncommitted transactions by applying their undo records in reverse. The redo phase restores the exact state at crash time; the undo phase then removes the effects of losers. This separation is what makes ARIES both correct and efficient.

Q6: Design a system where two bank accounts can be updated atomically even though they reside on different database shards. What protocol would you use, and what are its failure modes?

Answer: This requires a distributed transaction protocol. The standard approach is Two-Phase Commit (2PC): A coordinator sends a PREPARE message to both shard databases. Each shard writes a prepare record to its WAL and responds with VOTE_COMMIT if it can guarantee the transaction will succeed, or VOTE_ABORT if it cannot. If all shards vote COMMIT, the coordinator writes a commit decision to its own WAL and sends COMMIT to all shards. If any shard votes ABORT, the coordinator sends ABORT to all shards. Failure modes: (1) If a shard crashes after voting COMMIT but before receiving the coordinator's decision, it is "in doubt" — it has promised to commit but doesn't know the final decision. It must hold locks until it can contact the coordinator, causing blocking. (2) If the coordinator crashes after collecting votes but before broadcasting the decision, all shards that voted COMMIT are blocked indefinitely (this is the fundamental weakness of 2PC). (3) This is why many systems prefer Sagas for cross-service transactions — they sacrifice atomicity for availability by using compensating transactions to undo partial work.

21. Practice Exercises

Easy

Exercise 1: Write SQL to create a bank_accounts table with columns account_id (primary key), owner_name, balance (with a CHECK constraint ensuring balance ≥ 0), and updated_at. Then write a transaction that transfers $200 from account 'A-101' to account 'A-102', including a SELECT FOR UPDATE to lock the rows first.

Medium

Exercise 2: You have an e-commerce system with two tables: orders and inventory. Write a transaction in Python (or TypeScript) that atomically: (a) decrements the inventory count for a product, (b) creates a new order record, and (c) rolls back if the inventory would go below zero. Include retry logic for deadlock errors. Your code should handle the edge case where two users try to buy the last item simultaneously.

Hard

Exercise 3: Design and implement a simple in-memory transaction engine in TypeScript or Python. Your engine should support: (a) begin() — start a new transaction with a unique ID, (b) read(txn_id, key) — read a value, (c) write(txn_id, key, value) — write a value (tracked in an undo buffer), (d) commit(txn_id) — make all writes permanent, (e) rollback(txn_id) — undo all writes using the undo buffer. The engine should support Read Committed isolation — a transaction should not see uncommitted writes from other transactions.

22. Challenge Problem

Scenario: Concert Ticket Reservation System

You are designing a concert ticket reservation system for a popular venue with 50,000 seats. When a major artist announces a tour, 200,000 users may attempt to purchase tickets simultaneously within the first 30 seconds. Your system must guarantee:

• No seat is sold to more than one person (no double-booking).
• A user who selects a seat and enters payment information has a 5-minute hold on that seat before it is released back to the pool.
• The payment processing step involves an external payment gateway (3-5 second latency).
• If payment fails, the seat must be released immediately for others to purchase.
• The system should handle at least 10,000 concurrent seat reservation attempts per second.

Design Challenge: How would you structure the database schema and transaction logic to meet these requirements? Consider: (1) How do you prevent double-booking without serializing all requests through a single row lock? (2) How do you handle the 5-minute reservation hold without holding a database transaction open for 5 minutes? (3) How do you integrate the external payment gateway call without blocking database locks? (4) What isolation level do you choose and why? (5) How do you handle the thundering herd of 200,000 users hitting the system in 30 seconds?

23. Summary

A transaction is a sequence of database operations that executes as a single, indivisible unit of work. It provides four guarantees — collectively known as ACID: Atomicity (all or nothing), Consistency (valid state transitions), Isolation (concurrent independence), and Durability (survives crashes). Transactions move through a well-defined state machine: Active → Partially Committed → Committed (on success) or Active → Failed → Aborted → Terminated (on failure).

The DBMS enforces ACID using several internal components: the Lock Manager provides isolation through shared and exclusive locks, the Write-Ahead Log (WAL) provides atomicity and durability by recording all changes before they are applied, the Undo Log enables rollback of failed transactions, and the Recovery Manager uses the ARIES algorithm to restore consistency after crashes. Modern databases use MVCC to maximize concurrency by allowing readers and writers to operate on different versions of the same data without blocking each other.

The SQL standard defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each trading off consistency for concurrency performance. Choosing the right isolation level, keeping transactions short, implementing retry logic for deadlocks, and avoiding network calls inside transaction boundaries are critical best practices for production systems.

24. Cheat Sheet

25. Quiz

Q1: Which ACID property guarantees that a transaction either fully completes or has no effect at all?

A) Consistency   B) Isolation   C) Atomicity   D) Durability

Answer: C) Atomicity

Q2: What mechanism does the DBMS use to guarantee that committed data survives a power failure?

A) Buffer Pool   B) Lock Manager   C) Write-Ahead Log (WAL)   D) Query Optimizer

Answer: C) Write-Ahead Log (WAL)

Q3: At which isolation level can a transaction read data that has been written by another transaction that has NOT yet committed?

A) Serializable   B) Repeatable Read   C) Read Committed   D) Read Uncommitted

Answer: D) Read Uncommitted

Q4: In the ARIES recovery protocol, why does the Redo phase replay operations from BOTH committed and uncommitted transactions?

A) To reduce recovery time   B) Because the physical page state may depend on uncommitted changes being present before undo can be applied correctly   C) Because uncommitted transactions should be committed during recovery   D) To simplify the implementation

Answer: B) Because the physical page state may depend on uncommitted changes being present before undo can be applied correctly

Q5: What is the primary advantage of MVCC over Two-Phase Locking (2PL)?

A) MVCC uses less memory   B) MVCC eliminates all concurrency anomalies   C) Readers never block writers and vice versa   D) MVCC does not require a WAL

Answer: C) Readers never block writers and vice versa

Q6: A deadlock occurs when:

A) A single transaction acquires too many locks   B) Two or more transactions form a circular wait dependency on locks   C) The WAL disk runs out of space   D) A transaction exceeds the statement timeout

Answer: B) Two or more transactions form a circular wait dependency on locks

Q7: Which transaction state indicates that all operations have executed but changes have NOT yet been flushed to durable storage?

A) Active   B) Partially Committed   C) Committed   D) Failed

Answer: B) Partially Committed

Q8: What is a phantom read?

A) Reading data from a crashed transaction   B) A re-executed range query returns new rows inserted by a concurrent committed transaction   C) Reading the same row twice and getting different values   D) Reading uncommitted data from another transaction

Answer: B) A re-executed range query returns new rows inserted by a concurrent committed transaction

Q9: Why should you avoid making external API calls (e.g., to a payment gateway) inside a database transaction?

A) External APIs do not support transactions   B) The network latency extends the transaction's lock-holding duration, blocking concurrent access to the same data   C) It violates the ACID Consistency property   D) It causes the WAL to overflow

Answer: B) The network latency extends the transaction's lock-holding duration, blocking concurrent access to the same data

Q10: What is the purpose of a SAVEPOINT in a transaction?

A) It commits the transaction up to that point   B) It creates a named marker that allows partial rollback without aborting the entire transaction   C) It releases all locks acquired so far   D) It forces a WAL flush to disk

Answer: B) It creates a named marker that allows partial rollback without aborting the entire transaction

26. Further Reading

• Designing Data-Intensive Applications by Martin Kleppmann — Chapters 7 ("Transactions") provides the most accessible and rigorous treatment of transaction isolation levels, concurrency anomalies, and MVCC in modern distributed systems.
• ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging by C. Mohan et al. (1992) — The foundational paper on the ARIES crash recovery protocol used by virtually all modern RDBMS engines.
• A Critique of ANSI SQL Isolation Levels by Berenson et al. (1995) — The seminal paper that identified Snapshot Isolation and the write skew anomaly, revealing gaps in the original SQL standard's isolation level definitions.
• PostgreSQL Documentation: Transaction Isolation — Detailed, practical documentation on how PostgreSQL implements MVCC and its specific behaviors at each isolation level.
• Database Internals by Alex Petrov — Chapters on storage engines, write-ahead logging, and concurrency control provide deep implementation-level insights across multiple database engines.
• CMU Database Group: Advanced Database Systems (15-721) by Andy Pavlo — Lecture series covering MVCC implementation strategies, ARIES, and modern concurrency control techniques with hands-on projects.

27. Next Lesson Preview

In this lesson, we covered transactions within a single database instance, where the DBMS has complete control over locking, logging, and recovery. But what happens when a transaction needs to span multiple databases or services — for example, deducting inventory from a warehouse database AND charging a customer in a separate payment database?

In the next lesson, Distributed Transactions, we will explore the protocols that coordinate atomic operations across independent systems: Two-Phase Commit (2PC) for strong consistency with its blocking limitations, Three-Phase Commit (3PC) as a non-blocking improvement, and Sagas as the eventual-consistency approach favored in microservice architectures. We will analyze the CAP theorem implications, compare choreography vs. orchestration patterns for Sagas, and examine how companies like Uber and Netflix handle cross-service atomicity at massive scale.