Transaction Isolation Levels: Storage and Query Deep Dive

Introduction and Core Concepts

Transaction isolation levels are a fundamental concept in database systems that define the degree to which the operations of one transaction are isolated from those of other concurrent transactions. In any system where multiple users or application threads access shared data simultaneously, isolation levels determine what anomalies a transaction is permitted to observe and how the database storage engine and query executor coordinate to provide consistency guarantees. Without well-defined isolation, concurrent execution would lead to data corruption, lost updates, and unpredictable query results.

At its core, transaction isolation is about balancing two competing goals: correctness and concurrency. Stronger isolation provides more predictable and correct behavior by preventing interference between transactions, but it typically reduces throughput by requiring more locking, versioning, or validation. Weaker isolation allows higher concurrency and lower latency, often at the cost of allowing certain anomalies. Understanding this trade-off is essential for database architects, backend engineers, and anyone designing systems that require reliable data storage and retrieval under load.

The concept is rooted in the ACID properties of transactions — Atomicity, Consistency, Isolation, and Durability. While atomicity ensures all-or-nothing execution and durability ensures committed data survives failures, isolation specifically addresses the visibility of intermediate states. The SQL standard defines four primary isolation levels, but modern storage engines often implement more nuanced variants using multiversion concurrency control (MVCC), optimistic concurrency, and snapshot isolation to achieve practical performance at scale.

Why Isolation Levels Matter for Storage and Queries

From a storage perspective, isolation levels dictate how the database engine manages row versions, locks, undo logs, and write-ahead logs. A system offering Repeatable Read must retain old row versions for the duration of a transaction, while Read Committed can discard them more aggressively. This directly impacts storage overhead, vacuum and garbage collection strategies, and index maintenance complexity.

From a query perspective, the isolation level changes the semantics of SELECT statements. Under Read Committed, the same query executed twice within a single transaction may return different results if another transaction commits in between. Under Serializable, the database must ensure the result is equivalent to some serial execution order, which may require predicate locking or serialization failure retries. Choosing the wrong level can lead to subtle bugs in application logic, such as double-spending in financial systems or overselling inventory in e-commerce platforms.

• Correctness: Prevents anomalies like dirty reads, lost updates, and phantom reads that can violate business invariants.
• Performance: Lower isolation reduces lock contention and allows greater parallelism for read-heavy workloads.
• Predictability: Defines the contract between the application and the database, making reasoning about concurrent behavior possible.
• Scalability: Influences how well the database scales on multi-core and distributed architectures.

How It Works

Transaction isolation is enforced by the database's concurrency control mechanism. The two dominant approaches are pessimistic concurrency control (locking) and optimistic concurrency control (versioning and validation). In a lock-based system, transactions acquire shared locks for reads and exclusive locks for writes. The duration for which locks are held determines the isolation level. For example, Read Committed releases shared locks immediately after reading, while Repeatable Read holds them until transaction commit, preventing other transactions from modifying the data that has been read.

Modern systems, including PostgreSQL, Oracle, and InnoDB, primarily use Multiversion Concurrency Control (MVCC) to implement isolation without heavy locking for readers. In MVCC, each write creates a new version of the row stamped with the transaction ID or timestamp that created it, and optionally the transaction ID that deleted or superseded it. Readers then select the version visible according to their snapshot. A transaction's snapshot is typically defined by its start timestamp, and visibility rules determine which versions are considered committed and visible to that snapshot.

The ANSI SQL standard defines isolation in terms of prohibited anomalies. A dirty read occurs when a transaction reads uncommitted data from another transaction. A non-repeatable read occurs when a transaction reads the same row twice and gets different values because another transaction updated and committed that row in between. A phantom read occurs when a transaction executes a range query twice and the set of rows returned changes due to another transaction inserting or deleting rows that match the predicate. The four standard levels progressively prevent these anomalies.

Visibility Rules and Snapshots

Under MVCC, visibility is determined by comparing transaction IDs. Each transaction is assigned a monotonically increasing transaction ID (txid) or timestamp. When a transaction T with start timestamp S attempts to read a row version created by transaction C, the version is visible if C committed before S started and C is not still active in T's snapshot. Deleted versions are invisible if the deleting transaction is visible. This mechanism allows readers to never block writers and writers to never block readers, a crucial property for high-throughput OLTP systems.

Serializable isolation is the strongest level and requires that the concurrent execution be equivalent to some serial order. Implementing it efficiently is challenging. Techniques include strict two-phase locking (S2PL), where all locks are held until commit, and Serializable Snapshot Isolation (SSI), which tracks read-write dependencies at runtime and aborts transactions that could create a serialization anomaly. SSI adds overhead for tracking read sets and write sets but allows much higher concurrency than pure locking.

# Simplified MVCC visibility check
class RowVersion:
    def __init__(self, value, created_by, deleted_by=None):
        self.value = value
        self.created_by = created_by  # txid that created this version
        self.deleted_by = deleted_by  # txid that deleted/superseded it
        self.committed = False

def is_visible(version, reader_txid, active_txids, committed_txids):
    # Creator must be committed and not active at reader's snapshot
    if version.created_by == reader_txid:
        return version.deleted_by is None or version.deleted_by != reader_txid
    if version.created_by in active_txids:
        return False
    if version.created_by not in committed_txids:
        return False
    # Check if version was deleted by a visible transaction
    if version.deleted_by is not None:
        if version.deleted_by in committed_txids and version.deleted_by < reader_txid:
            # Deleted before reader started - not visible
            return False
        if version.deleted_by in active_txids:
            # Deleter still active - version still visible
            return True
    return True

# Example: active_txids = {105, 106}, reader_txid = 107
# A version created by 104 (committed) and deleted by 105 (active) is still visible to 107

Implementation

Implementing isolation levels requires a transaction manager, a version store or lock manager, and a query executor that respects visibility rules. Below are Python simulations that illustrate the core data structures. While simplified, they capture the essential logic used in real storage engines for managing concurrent access.

The first example models a lock manager that enforces isolation through shared and exclusive locks. The second shows an MVCC storage engine that maintains version chains and provides snapshot reads. Both approaches must handle deadlock detection, lock escalation, and garbage collection of old versions that are no longer visible to any active transaction.

from collections import defaultdict
import threading

class LockManager:
    def __init__(self):
        self.locks = defaultdict(list)  # resource -> list of (txid, mode)
        self.lock = threading.Lock()

    def acquire(self, txid, resource, mode, isolation_level):
        """
        mode: 'S' (shared) or 'X' (exclusive)
        isolation_level: 'READ_COMMITTED', 'REPEATABLE_READ', 'SERIALIZABLE'
        """
        with self.lock:
            # Check for conflicting locks
            for holder_txid, holder_mode in self.locks[resource]:
                if holder_txid == txid:
                    continue
                # S conflicts with X, X conflicts with S and X
                if mode == 'S' and holder_mode == 'X':
                    return False  # Would block
                if mode == 'X':
                    return False  # X conflicts with any

            # No conflict - grant lock
            self.locks[resource].append((txid, mode))
            return True

    def release(self, txid, resource=None, isolation_level='READ_COMMITTED'):
        with self.lock:
            if resource:
                self.locks[resource] = [(t, m) for t, m in self.locks[resource] if t != txid]
            else:
                # Release all locks on commit/abort
                # REPEATABLE_READ and SERIALIZABLE hold until commit
                # READ_COMMITTED releases S locks early (simulated by caller)
                for res in list(self.locks.keys()):
                    self.locks[res] = [(t, m) for t, m in self.locks[res] if t != txid]

    def is_serializable_conflict(self, txid, read_set, write_set, other_write_set):
        # Simplified SSI check: if T1 reads what T2 writes and T2 writes what T1 reads
        if (read_set & other_write_set) and (write_set & other_write_set):
            return True
        return False

class MVCCStore:
    def __init__(self):
        self.data = defaultdict(list)  # key -> list of RowVersion (newest first)
        self.next_txid = 1
        self.active = set()
        self.committed = set([0])  # 0 is bootstrap
        self.lock = threading.Lock()

    def begin(self):
        with self.lock:
            txid = self.next_txid
            self.next_txid += 1
            self.active.add(txid)
            # Snapshot is set of active txids at start
            snapshot = set(self.active)
            return txid, snapshot

    def write(self, txid, key, value):
        with self.lock:
            version = RowVersion(value, created_by=txid)
            # Mark previous version as deleted by this txid
            if self.data[key]:
                self.data[key][0].deleted_by = txid
            self.data[key].insert(0, version)

    def read(self, txid, snapshot, key):
        with self.lock:
            for version in self.data[key]:
                # Visible if creator committed before snapshot and not deleted visibly
                creator_committed = version.created_by in self.committed
                creator_active_at_snapshot = version.created_by in snapshot and version.created_by != txid
                if not creator_committed or creator_active_at_snapshot:
                    continue
                if version.deleted_by is not None:
                    # If deleter is committed and visible, this version is not visible
                    if version.deleted_by in self.committed and version.deleted_by not in snapshot:
                        continue
                    if version.deleted_by == txid:
                        continue
                return version.value
            return None

    def commit(self, txid):
        with self.lock:
            self.active.remove(txid)
            self.committed.add(txid)
            # Mark versions as committed
            for versions in self.data.values():
                for v in versions:
                    if v.created_by == txid:
                        v.committed = True

    def abort(self, txid):
        with self.lock:
            self.active.remove(txid)
            # Remove versions created by aborted txid
            for key in list(self.data.keys()):
                self.data[key] = [v for v in self.data[key] if v.created_by != txid]
                # Restore deleted_by if aborting deleter
                for v in self.data[key]:
                    if v.deleted_by == txid:
                        v.deleted_by = None

def demonstrate_isolation_levels():
    store = MVCCStore()

    # Initial data
    tx0, snap0 = store.begin()
    store.write(tx0, "balance", 1000)
    store.commit(tx0)

    # T1 starts (READ COMMITTED vs REPEATABLE READ difference)
    t1, snap1 = store.begin()
    # T2 starts concurrently
    t2, snap2 = store.begin()

    # T2 updates balance
    store.write(t2, "balance", 1500)
    store.commit(t2)  # Commits while T1 is still active

    # What does T1 see?
    # Under READ COMMITTED (new snapshot per statement), T1 would see 1500
    # Under REPEATABLE READ (snapshot at transaction start), T1 sees 1000
    val_repeatable = store.read(t1, snap1, "balance")
    # Simulate READ COMMITTED by using current active set
    current_snapshot = set(store.active)
    val_read_committed = store.read(t1, current_snapshot, "balance")

    print(f"REPEATABLE READ sees: {val_repeatable}")  # 1000
    print(f"READ COMMITTED sees: {val_read_committed}")  # 1500

    store.commit(t1)

demonstrate_isolation_levels()

These implementations highlight the storage cost of isolation. The MVCC store must keep multiple versions per key until no active transaction can see them. A background vacuum process, similar to PostgreSQL's VACUUM or InnoDB's purge thread, scans version chains and reclaims space. The lock manager, conversely, must manage wait queues and detect deadlocks, which adds CPU overhead and can cause transaction aborts under contention.

Step-by-Step Example

Consider a simple banking application with two concurrent transactions operating on accounts A and B. Account A has $1000 and account B has $500. Transaction T1 transfers $200 from A to B, while transaction T2 calculates the total sum of all accounts to generate a report. The correctness of the report depends critically on the isolation level.

Initially, the database state is A=1000, B=500. T1 begins and reads A, subtracts 200, and writes A=800. Before T1 commits, T2 begins. The interleaving of operations and the visibility rules will determine whether T2 sees an inconsistent intermediate state where money appears to have disappeared.

We will walk through this scenario under three isolation levels to show how storage and query execution diverge. The example uses a timeline where T1's write to A occurs before T2's reads, but T1's write to B occurs after T2's reads, creating a classic inconsistent read anomaly if isolation is insufficient.

Timeline Under Read Committed

1. T1: BEGIN; READ A (1000)
2. T1: WRITE A = 800 (uncommitted version created, old version retained)
3. T2: BEGIN; READ A — sees 800 only if T1 has committed, otherwise sees 1000. Under Read Committed with MVCC, T2 sees the last committed version (1000) because T1 is still active.
4. T2: READ B — sees 500 (committed)
5. T2: SUM = 1500 (1000+500) — appears consistent but misses T1's in-flight transfer
6. T1: WRITE B = 700; COMMIT
7. T2: READ A again — now sees 800 (T1 committed). If T2 sums again, it gets 1500 (800+700) — different result on second read (non-repeatable read).

Under Read Committed, T2 never sees dirty data, but it can see different values for the same row if it reads twice. The total sum is either 1500 before or after, but if T2's report queries A and B at different times, it might compute 1300 (800+500) if it reads A after T1's commit but B before, which is an inconsistent snapshot.

Timeline Under Repeatable Read and Serializable

Under Repeatable Read with MVCC, T2 takes a snapshot at BEGIN that includes T1 as active. Therefore, even after T1 commits, T2 continues to see A=1000 and B=500 for the entire transaction. Its sum is consistently 1500, providing a stable snapshot. However, Repeatable Read does not prevent phantom reads: if T2 were counting accounts with balance > 600, and T1 inserted a new account C=800, T2 might miss it on first query but see it on second if the predicate is re-evaluated without snapshot protection.

Serializable prevents even that. If T2 is a reporting transaction that reads a range and T1 inserts into that range, a serializable engine using SSI would detect a read-write dependency cycle (T2 reads predicate, T1 writes to predicate, T1 commits before T2) and abort one of the transactions. Alternatively, predicate locking would block T1's insert until T2 commits. This ensures the execution is equivalent to either T1 then T2 or T2 then T1, never an interleaving that violates serial equivalence.

# Step-by-step simulation of the transfer anomaly
def transfer_anomaly_demo():
    store = MVCCStore()
    # Setup
    tx_init, _ = store.begin()
    store.write(tx_init, "A", 1000)
    store.write(tx_init, "B", 500)
    store.commit(tx_init)

    t1, snap1 = store.begin()  # Transfer transaction
    t2, snap2 = store.begin()  # Report transaction

    # T1 modifies A
    store.write(t1, "A", 800)
    # T2 reads both accounts under REPEATABLE READ (using snap2)
    a_t2 = store.read(t2, snap2, "A")  # Should be 1000, not 800
    b_t2 = store.read(t2, snap2, "B")  # 500
    print(f"T2 snapshot sum (RR): {a_t2 + b_t2} (A={a_t2}, B={b_t2})")

    # T1 modifies B and commits
    store.write(t1, "B", 700)
    store.commit(t1)

    # T2 reads again - still sees old snapshot under RR
    a_t2_again = store.read(t2, snap2, "A")
    print(f"T2 second read A under RR: {a_t2_again} (still 1000)")

    # Under READ COMMITTED, T2 would use fresh snapshot
    fresh_snap = set(store.active)  # T1 no longer active
    a_rc = store.read(t2, fresh_snap, "A")
    print(f"T2 read A under RC after T1 commit: {a_rc} (now 800)")

    store.commit(t2)

transfer_anomaly_demo()

Complexity Analysis

The performance characteristics of isolation levels are not captured by traditional algorithmic Big O alone, but rather by the overhead they impose on storage and query execution under concurrency. The cost manifests in lock management, version chain traversal, validation, and garbage collection.

For read operations, Read Committed is the cheapest: it requires only checking the latest committed version, often O(1) if the version chain is short and indexed. Repeatable Read may need to traverse multiple versions to find the one visible to the transaction's snapshot, leading to O(V) where V is the number of versions for that key. In the worst case, with long-running transactions, V can grow large and cause significant read amplification.

Write operations have similar base cost — creating a new version is O(1) — but the commit path differs. Under Serializable with SSI, commit requires validating read and write sets against concurrent transactions, which can be O(R + W) where R is the read set size and W is the write set size. Lock-based Serializable must acquire and hold locks for the entire transaction, increasing contention and the probability of deadlocks, which are detected via wait-for graph traversal O(T + E) where T is transactions and E is wait edges.

Space complexity is dominated by version storage. Read Committed can reclaim old versions as soon as no active transaction needs them, keeping space overhead low. Repeatable Read and Serializable must retain versions for the duration of the longest-running transaction, leading to worst-case O(N * V) space where N is number of keys and V is versions per key. This is why long-running analytical queries on OLTP systems can cause storage bloat if not managed with techniques like snapshot too old or statement-level snapshots.

In distributed databases, the complexity increases further. Achieving Repeatable Read across shards requires coordinating snapshots via a global timestamp oracle, adding network round trips O(log N) or O(1) with TrueTime-like mechanisms. Serializable across shards often requires two-phase commit plus additional validation, making it the most expensive option for geo-distributed deployments.

Variations and Optimizations

Beyond the four ANSI levels, production systems offer several important variations that optimize for specific workloads. Snapshot Isolation (SI), popularized by Oracle and PostgreSQL, is not part of the SQL standard but provides stronger guarantees than Read Committed without the full cost of Serializable. SI gives each transaction a consistent snapshot and prevents dirty and non-repeatable reads, but it allows write skew, a subtle anomaly where two transactions each read overlapping data and make disjoint updates that together violate a constraint.

For example, consider a hospital scheduling system where two doctors each check that at least one doctor is on call, then both go off call. Under SI, both transactions read that two doctors are on call, then each updates its own status to off call, leaving zero doctors on call — a violation of the invariant that SI does not prevent. Serializable Snapshot Isolation (SSI) was developed to detect and prevent such anomalies while retaining the performance benefits of SI.

Read Committed Snapshot Isolation (RCSI) is another optimization used by SQL Server and Oracle. It provides statement-level snapshots rather than transaction-level snapshots, so each statement sees the latest committed data as of statement start. This reduces version retention pressure and is ideal for long-running transactions that perform many reads but do not require repeatable reads.

Common Optimizations

Several techniques reduce the overhead of stronger isolation. Predicate locking can be optimized with index-range locking or gap locking (as in InnoDB) rather than locking entire tables. MVCC garbage collection can be made incremental and concurrent, using epoch-based reclamation to avoid stop-the-world pauses. For read-heavy workloads, many systems offer explicit snapshot reads or follower reads that bypass the primary's lock manager entirely.

Another optimization is to use optimistic concurrency for low-contention workloads: transactions execute without acquiring locks, then validate at commit time that no conflicts occurred. If validation fails, the transaction is retried. This is highly efficient when conflicts are rare, as it avoids lock overhead in the common case. Conversely, pessimistic locking is better for high-contention workloads where retries would be frequent.

• Gap Locking: Locks index gaps to prevent phantoms without locking the whole table, balancing concurrency and correctness.
• Time-Travel Queries: Expose MVCC snapshots explicitly (e.g., AS OF SYSTEM TIME) to allow analytical queries on historical data without impacting OLTP isolation.
• Epoch-Based Reclamation: Group transactions into epochs and reclaim versions only when all transactions in earlier epochs have completed, simplifying GC.
• SSI Read-Only Optimization: Read-only transactions under SSI never need to abort, allowing them to run without tracking write dependencies.

Real-World Applications

Transaction isolation levels are not abstract theory; they directly determine the reliability and performance of real systems. The choice of level should be driven by the application's consistency requirements and contention profile, not left at the database default.

In financial systems, such as payment processing and ledger management, Serializable or at least Repeatable Read is often mandatory. A transfer between accounts must be atomic and isolated to prevent double-spending or lost updates. Many fintech companies run critical transactions at Serializable and handle serialization failures with application-level retries, accepting lower throughput for absolute correctness.

E-commerce inventory management is another classic case. When multiple users attempt to purchase the last item in stock, Read Committed can lead to overselling because two transactions both read stock=1, both decrement to 0, and both commit, resulting in stock=-1. Repeatable Read with SELECT ... FOR UPDATE (pessimistic locking) or Serializable is required to ensure only one purchase succeeds.

For web applications with user-facing dashboards, Read Committed is often sufficient and preferred for performance. A social media feed, analytics dashboard, or product catalog can tolerate slightly stale or non-repeatable reads in exchange for much higher concurrency and lower latency. Many ORMs default to Read Committed for this reason.

In distributed databases like CockroachDB, TiDB, and Google Spanner, isolation levels are tied to consensus protocols. Spanner's TrueTime and external consistency provide Serializable isolation globally, which is powerful but requires careful clock synchronization and adds commit latency. Systems that prioritize availability, like Cassandra and DynamoDB, offer tunable consistency where isolation is relaxed to eventual consistency, pushing conflict resolution to the application layer.

Comparison with Alternatives

Isolation levels are part of a broader spectrum of consistency models. It is important to distinguish them from related concepts like eventual consistency, causal consistency, and linearizability, which are often used in distributed and NoSQL systems. While isolation levels govern the visibility of concurrent transactions within a single database, these alternative models describe guarantees across replicas and data centers.

Eventual consistency, for example, guarantees that if no new updates are made, all replicas will eventually converge, but it provides no guarantees about what a transaction will read during convergence. This is suitable for DNS, shopping carts, or social media likes where temporary inconsistency is acceptable. In contrast, even the weakest SQL isolation level, Read Uncommitted, still provides transactional atomicity within a single node.

Another alternative is to avoid database-level isolation entirely and implement application-level concurrency control using optimistic locking with version columns, compensating transactions (sagas), or conflict-free replicated data types (CRDTs). These approaches can be more scalable for specific patterns but require significant application complexity and are error-prone if not implemented carefully.

Choosing between these models involves evaluating the CAP theorem trade-offs. Strong isolation levels favor consistency over availability during network partitions, while eventual consistency favors availability. For most traditional RDBMS workloads, the SQL isolation levels remain the right abstraction because they provide well-understood guarantees with manageable performance overhead on single-node or strongly consistent distributed deployments.

Common Mistakes and Best Practices

One of the most frequent mistakes is assuming the default isolation level is sufficient without verification. Different databases have different defaults: PostgreSQL and Oracle default to Read Committed, MySQL InnoDB defaults to Repeatable Read, and SQL Server defaults to Read Committed but offers Snapshot as an option. Migrating an application between databases without adjusting isolation expectations can introduce subtle bugs that only appear under concurrency.

Another common pitfall is using Read Committed for operations that require Repeatable Read, such as read-modify-write cycles. A classic example is checking a balance before withdrawing: if the check and the update are not in a single atomic statement or protected by SELECT FOR UPDATE, another transaction can modify the balance in between, leading to overdrafts. The fix is to either use a higher isolation level or use explicit row-level locking and atomic updates like UPDATE accounts SET balance = balance - 100 WHERE balance >= 100.

Developers also often mishandle serialization failures. Under Serializable, transactions can be aborted with errors like "could not serialize access due to concurrent update" even when there is no logical conflict from the application's perspective. Applications must be designed to retry such transactions with exponential backoff, and the retry logic must be idempotent. Failing to retry leads to spurious user-visible errors.

Best Practices

• Choose the weakest level that is correct: Start with Read Committed and only escalate to Repeatable Read or Serializable when you can demonstrate a specific anomaly that violates an invariant. This maximizes concurrency.
• Keep transactions short: Long-running transactions hold snapshots and locks, increasing version retention, blocking vacuum, and raising deadlock probability. Break large batch jobs into smaller chunks.
• Use explicit locking when needed: Instead of raising the global isolation level, use SELECT ... FOR UPDATE or FOR SHARE for specific critical reads. This provides targeted protection without penalizing all transactions.
• Test under concurrency: Unit tests with single-threaded execution will not catch isolation bugs. Use concurrency tests, stress tests, and tools like Jepsen or pg_isolation_regress to validate behavior under race conditions.
• Monitor for bloat and contention: Track metrics like version chain length, lock wait time, serialization failure rate, and table bloat. Set alerts for long-running transactions that may be holding snapshots.
• Document invariants: Clearly document which business invariants require strong isolation (e.g., "account balance never negative") and ensure they are enforced at the database level with constraints plus appropriate isolation, not just application checks.

Finally, avoid the anti-pattern of using Read Uncommitted to improve performance. In most MVCC systems, Read Uncommitted is either not truly implemented (it behaves like Read Committed) or it allows dirty reads that can cause application logic to act on data that will never be committed, leading to cascading errors that are extremely difficult to debug.

Interview Questions

Transaction isolation is a favorite topic in system design and database interviews because it reveals depth of understanding about concurrency, storage engines, and trade-offs. Below are common questions with guidance on what interviewers expect.

1. What is the difference between Read Committed and Repeatable Read, and what anomaly does Repeatable Read prevent that Read Committed does not?

Answer: Read Committed guarantees that a transaction only reads committed data, but the same row read twice in the same transaction may return different values if another transaction committed an update in between (non-repeatable read). Repeatable Read guarantees that if a transaction reads a row, subsequent reads of the same row will see the same value, typically by holding shared locks until commit or by providing a transaction-level snapshot. It prevents non-repeatable reads but, in the ANSI definition, still allows phantom reads. In practice, many MVCC implementations like PostgreSQL's Repeatable Read prevent phantoms for single-table scans but not for all predicate cases; only Serializable fully prevents phantoms.

2. Explain write skew and why Snapshot Isolation does not prevent it. How does Serializable Snapshot Isolation solve it?

Answer: Write skew is an anomaly where two transactions each read an overlapping set of data, then each makes an update based on what they read, and together the updates violate a constraint. Example: two doctors checking on-call status. Snapshot Isolation prevents dirty and non-repeatable reads but allows write skew because neither transaction overwrites the other's write (they write disjoint rows), so no write-write conflict is detected. SSI tracks read-write dependencies and builds a serialization graph; if it detects a dangerous structure (two consecutive read-write dependencies forming a cycle), it aborts one transaction to break the cycle, thus preventing write skew while maintaining snapshot performance.

3. How does MVCC implement isolation without blocking readers and writers? What are its storage trade-offs?

Answer: MVCC keeps multiple versions of each row, each tagged with creator and deleter transaction IDs. Readers use their snapshot (set of active transactions at start) to determine which version is visible, never acquiring shared locks. Writers create new versions and mark old ones as superseded. This allows readers to never block writers and vice versa. Trade-offs include storage overhead for old versions, need for garbage collection (vacuum/purge) to reclaim space, and read amplification when traversing long version chains. Long-running transactions exacerbate this by preventing reclamation of versions they might still need.

4. When would you choose Serializable over Repeatable Read, and how would you handle serialization failures in application code?

Answer: Choose Serializable when you must preserve invariants that involve multiple rows or predicates, such as uniqueness constraints across ranges, foreign key checks without locking, or complex business rules like "total inventory never negative." Repeatable Read is insufficient if phantom reads or write skew could violate those invariants. In application code, serialization failures manifest as SQLSTATE 40001 errors. The application should catch these, retry the entire transaction with exponential backoff and jitter, ensure the transaction is idempotent or can be safely retried, and limit retry attempts to avoid livelock. Many frameworks provide automatic retry wrappers for this.

5. Describe how a database detects deadlocks under two-phase locking and how it resolves them. What is the Big O of detection?

Answer: The lock manager maintains a wait-for graph where nodes are transactions and edges represent waits (T1 waits for lock held by T2). A deadlock exists if there is a cycle in this graph. Detection is typically done periodically or when a transaction has waited beyond a threshold, using DFS cycle detection O(T + E) where T is transactions and E is wait edges. Resolution involves choosing a victim transaction to abort, usually the one with lowest cost (fewest locks, youngest, or least work done), releasing its locks and allowing others to proceed. MVCC systems reduce deadlocks by avoiding read locks, but write-write deadlocks can still occur.