August 9, 2025

Understanding the Internal Working of Database Transactions

A database transaction is a series of operations that are treated as a single logical unit of work. The key point: either all the operations happen, or none of them do.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;



Internals DB Operations


1. Step-by-Step Internal Flow

  1. BEGIN → The database assigns a transaction ID (TID) and starts tracking all operations.

  2. Read/Write Operations → Data pages are fetched from disk into the Buffer Pool.

  3. Locking / MVCC →

    • Lock-based systems: lock rows/tables to ensure isolation.

    • MVCC systems: keep multiple versions of data so reads don’t block writes.

  4. Undo Logging → Before any change, the old value is written to an Undo Log.

  5. Change in Memory → Updates are made to in-memory pages in the buffer pool.

  6. Redo Logging (WAL) → The intended changes are written to a Write-Ahead Log on disk.

  7. Commit → The WAL is flushed to disk, guaranteeing durability.

  8. Post-Commit → Locks are released, and dirty pages are eventually written to disk.

  9. Rollback (if needed) → Use the Undo Log to restore old values.

2. Key Internal Components

  • Buffer Pool → Memory space to hold frequently used data.

  • Undo Log → Stores old versions for rollback.

  • Redo Log / WAL → Ensures committed changes survive crashes.

  • Transaction Table → Tracks active transactions.

  • Lock Table → Keeps record of who holds which lock.

  • Version Store (in MVCC) → Keeps older row versions for consistent reads.

3. Isolation Levels (How Concurrency Is Controlled)

  • Read Uncommitted → Reads can see uncommitted changes (dirty reads possible).

  • Read Committed → Reads only see committed data.

  • Repeatable Read → Guarantees same row returns same data in one transaction.

  • Serializable → Full isolation as if transactions run one after another.

4. Propagation Levels (For Nested Transactions)

  • REQUIRED → Use current transaction or start one if none exists.

  • REQUIRES_NEW → Always start a new transaction.

  • MANDATORY → Must join existing transaction.

  • NESTED → Sub-transaction inside a main one.

  • SUPPORTS → Join if exists, else run without.

  • NOT_SUPPORTED → Run without transaction.

  • NEVER → Fail if there’s an active transaction.

5. How Popular Databases Handle Transactions

Database Concurrency Control Logging Default Isolation
PostgreSQL MVCC WAL Read Committed
MySQL (InnoDB) MVCC + Locks Redo + Undo Logs Repeatable Read
Oracle MVCC Redo + Undo Read Committed
SQL Server Locks + Snapshot Transaction Log Read Committed
MongoDB Document Locks Journal Log Snapshot Isolation

6. Real-World Analogy

Imagine editing a Word document:

  • Begin → Open file.

  • Undo Log → Ctrl+Z stores old text.

  • Redo Log / WAL → Auto-save captures changes.

  • Commit → Click Save.

  • Rollback → Close without saving.

In short: Internally, a database transaction relies on logging, memory buffers, and concurrency control to ensure that your operations are safe, consistent, and recoverable — even in the event of crashes.



By
Labels: