Introduction & History

From System R and Jim Gray’s 1981 paper to SQL-92’s four levels, the 1995 “Critique of ANSI SQL Isolation Levels,” and PostgreSQL's Serializable Snapshot Isolation today.

Imagine two people trying to withdraw money from the same joint bank account at the exact same second — one at an ATM in Delhi, another using a mobile app in Mumbai. Both see a balance of ₹10,000. Both try to withdraw ₹8,000. If the bank's computer system is not careful, it could let both withdrawals succeed, and the account would go ₹6,000 into a hole that should never have existed.

Isolation is the database rule that stops this from happening. It is one of the four promises a database makes whenever you run a transaction — the other three being Atomicity, Consistency, and Durability, together known as ACID. Isolation is specifically the promise that deals with multiple things happening at once.

1.1 · A SHORT HISTORY

The idea of isolation goes back to the 1970s, when researchers at IBM were building System R, one of the first relational database systems. Jim Gray, a computer scientist who later won the Turing Award, was among the first to formally describe transactions and the problems that occur when multiple transactions run at the same time. His 1981 paper, written with Kim, Putzolu, and Traiger, described phenomena like “dirty reads” and laid the groundwork for what we now call isolation levels.

In 1992, the SQL standard (SQL-92) formally defined four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — based on which anomalies they prevent. This standard is still the reference point every database vendor talks about today, even though modern databases like PostgreSQL, Oracle, and MySQL have each built their own internal engines (often using a technique called MVCC, which we will explain in detail) to deliver these guarantees more efficiently than the original lock-based designs from the 1970s.

Interestingly, the original SQL-92 definitions describe isolation purely in terms of which anomalies each level must prevent, not in terms of how a database should implement that prevention. This turned out to be a smart design choice: it let vendors innovate freely on the internal engine (locking, versioning, hybrid approaches) while application developers could still reason about behavior using one shared vocabulary. Later academic work, especially a well-known 1995 paper titled “A Critique of ANSI SQL Isolation Levels” by Berenson, Bernstein, Gray, and others, pointed out gaps and ambiguities in the original standard — for example, it showed that the standard's definitions didn't fully capture anomalies like write skew. This critique directly influenced how modern databases like PostgreSQL describe and implement their strongest level, Serializable Snapshot Isolation (SSI), which we'll explore later.

The Problem Isolation Solves

Thousands of transactions per second on the same rows — and every read-check-write in your code is a potential race condition unless someone is guarding the door.

Databases are almost never used by one person at a time. A production system like an e-commerce checkout page, a food delivery app, or a payroll system might be handling thousands of transactions every second, all reading and writing to the same tables. Without isolation, chaos would be the default, not the exception.

2.1 · A CONCRETE BEGINNER EXAMPLE

Say a table accounts has one row: id=1, balance=1000. Two transactions run “at the same time”:

The final balance is 500. But 700 + 500 = 1200 was withdrawn from an account that only had 1000. Transaction B's write silently erased Transaction A's withdrawal — a bug called a lost update. Proper isolation prevents exactly this kind of silent data corruption.

2.2 · A SECOND EXAMPLE — THE FLASH SALE PROBLEM

Imagine an online store selling exactly 10 units of a limited-edition sneaker during a flash sale. A thousand people click “Buy Now” within the same second. Each request runs roughly this logic:

SELECT stock FROM products WHERE id = 55;   -- e.g. returns 10
-- application code checks: is stock > 0?
UPDATE products SET stock = stock - 1 WHERE id = 55;

If a thousand of these run concurrently without proper isolation and locking, far more than 10 requests could read “stock = 10” before any of them writes back a decremented value, and all of them might proceed to think they successfully bought a pair. The store ends up promising more sneakers than it has — a direct financial and reputational cost. This exact pattern (read-check-write without protection) is one of the most common concurrency bugs in real production code, and it's precisely what isolation levels and row locking are designed to prevent.

Core Concepts

Transactions, ACID, serializability, and the crucial difference between the theoretical gold standard and the four practical isolation levels every database offers.

3.1 · WHAT IS A TRANSACTION?

A transaction is a group of one or more database operations (reads and writes) that are treated as a single unit. Either every operation in the group succeeds, or none of them do. In SQL, a transaction typically looks like this:

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

3.2 · ACID, BRIEFLY

3.3 · WHAT “ISOLATION” ACTUALLY MEANS

Formally, isolation means that the result of running several transactions concurrently is the same as if they had run one after another, in some order — even though, under the hood, the database engine may be interleaving their work for speed. This idea is called serializability, and it is the gold standard that isolation levels try to approximate.

3.4 · WHY NOT JUST RUN EVERYTHING ONE AT A TIME?

You might ask: why not simply let only one transaction run at a time, so there's never any interference? Technically, this would guarantee perfect isolation — it's called serial execution. But it would also make the database painfully slow, because every user would have to wait in a single-file queue, even if their transactions touch completely unrelated data (like two different customers checking out in two different cities). Isolation levels exist to let the database run many transactions in parallel for speed, while still preventing the specific kinds of interference that would corrupt data or confuse users.

3.5 · SERIALIZABILITY vs ISOLATION LEVELS — A KEY DISTINCTION

It's worth separating two related but different ideas clearly:

• Serializability is the theoretical gold-standard correctness property: the outcome must match some serial ordering of the transactions.
• Isolation levels are practical, named configurations a database offers, each of which allows certain deviations from full serializability in exchange for speed. Only the “Serializable” isolation level actually promises true serializability; the other three are useful, well-understood compromises.

A helpful mental model: think of full serializability as 100% correctness with the highest cost, and each weaker isolation level as a carefully chosen discount — the database vendors and standards committees picked these specific discounts because, in practice, they cover the vast majority of real application needs without paying the full cost every time.

3.6 · A JAVA EXAMPLE — A METHOD THAT NEEDS CAREFUL ISOLATION

public void redeemCoupon(Connection conn, String couponCode, long userId) throws SQLException {
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    try (PreparedStatement check = conn.prepareStatement(
            "SELECT used FROM coupons WHERE code = ? FOR UPDATE")) {
        check.setString(1, couponCode);
        ResultSet rs = check.executeQuery();
        if (rs.next() && !rs.getBoolean("used")) {
            try (PreparedStatement markUsed = conn.prepareStatement(
                    "UPDATE coupons SET used = TRUE, used_by = ? WHERE code = ?")) {
                markUsed.setLong(1, userId);
                markUsed.setString(2, couponCode);
                markUsed.executeUpdate();
            }
            conn.commit();
        } else {
            conn.rollback(); // already used -- reject
            throw new IllegalStateException("Coupon already redeemed");
        }
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}

Here, FOR UPDATE combined with Serializable isolation guarantees that even if two requests try to redeem the same coupon code at the exact same instant, only one of them will see used = false and succeed — the other will either wait and then see it as already used, or receive a serialization error it can retry.

Concurrency Anomalies

Dirty reads, non-repeatable reads, phantom reads, lost updates, and write skew — the five specific bugs every isolation level is measured against.

Before understanding isolation levels, you need to understand the specific problems (“anomalies”) that can occur when transactions overlap. Each isolation level is essentially a promise: “I will prevent these specific anomalies, but I might still allow those other ones, in exchange for better speed.”

4.1 · SEEING DIRTY READS IN PRACTICE

-- Session A (Read Uncommitted)
BEGIN;
UPDATE accounts SET balance = balance - 700 WHERE id = 1;
-- not committed yet -- A is still deciding whether to roll back

-- Session B (also Read Uncommitted)
SELECT balance FROM accounts WHERE id = 1;  -- sees the -700 already applied!

-- Session A changes its mind
ROLLBACK;
-- Session B already acted on data that officially never happened

4.2 · SEEING PHANTOM READS IN PRACTICE

-- Session A (Repeatable Read)
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'PENDING'; -- returns 40

-- Session B (runs and commits fully in between)
INSERT INTO orders (status) VALUES ('PENDING');
COMMIT;

-- Back in Session A, same transaction, same query
SELECT COUNT(*) FROM orders WHERE status = 'PENDING'; -- some engines: still 40, others: 41

Whether Session A sees 40 or 41 the second time depends on the exact database engine's Repeatable Read implementation — this is precisely the kind of subtlety that makes isolation levels tricky in interviews and in real debugging sessions.

4.3 · WRITE SKEW IN SQL TERMS

-- Both doctors' sessions run concurrently, both under Serializable
-- Session Asha
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = TRUE; -- sees 2
UPDATE doctors SET on_call = FALSE WHERE name = 'Asha';
COMMIT;

-- Session Vikram (interleaved)
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = TRUE; -- sees 2
UPDATE doctors SET on_call = FALSE WHERE name = 'Vikram';
COMMIT;

Under plain Repeatable Read, both transactions can succeed, leaving zero doctors on call. A true Serializable engine using conflict detection (like PostgreSQL's SSI) can detect that these two transactions have a dependency cycle through the shared on_call condition and will abort one of them with a serialization error, forcing a retry that re-checks the real, updated count.

The Four Isolation Levels

Read Uncommitted, Read Committed, Repeatable Read, Serializable — and the anomalies each one is willing to trade away for speed.

The SQL standard defines four isolation levels. As you move from top to bottom in the table below, the database gives you stronger correctness guarantees, but usually at the cost of more locking, more waiting, or more retries — meaning lower raw throughput.

5.1 · READ UNCOMMITTED

This is the weakest level. A transaction can see changes made by other transactions even before those changes are committed. It's like reading a rough draft of an email someone is still typing — the sender might delete the whole thing, but you already saw it and acted on it.

5.2 · READ COMMITTED

A transaction only ever sees data that has been committed by other transactions. It will never see a half-finished write. However, if it reads the same row twice, and someone else commits a change in between, it can see two different values — a non-repeatable read.

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees 1000

-- Session B (runs and commits fully in between)
UPDATE accounts SET balance = 1300 WHERE id = 1;
COMMIT;

-- Back in Session A, same transaction
SELECT balance FROM accounts WHERE id = 1;  -- now sees 1300

5.3 · REPEATABLE READ

Once a transaction reads a row, it is guaranteed to see the exact same value for that row if it reads it again — even if another transaction commits a change in the meantime. The transaction effectively works from a stable “snapshot” of the data. This level still classically allows phantom reads (new rows appearing that match a filter), although MySQL's InnoDB engine has extra techniques (gap locks) that block most phantoms too.

5.4 · SERIALIZABLE

The strongest and safest level. The database guarantees that the result of running your concurrent transactions is identical to some serial (one-after-another) ordering of those same transactions. This prevents every anomaly, including write skew. The cost is that the database may have to block transactions or force one of them to retry (via an error like “could not serialize access due to concurrent update”) when a true conflict is detected.

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

try {
    PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    debit.setBigDecimal(1, amount);
    debit.setLong(2, fromAccountId);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    credit.setBigDecimal(1, amount);
    credit.setLong(2, toAccountId);
    credit.executeUpdate();

    conn.commit();
} catch (SQLException e) {
    conn.rollback(); // if the database detects a conflict, retry the whole block
    throw e;
} finally {
    conn.setAutoCommit(true);
}

This Java snippet transfers money between two accounts under the Serializable isolation level. If the database detects that another concurrent transaction has created a genuine conflict, it throws an exception, the code rolls back, and the caller is expected to retry.

Architecture & Components

Transaction manager, lock manager, MVCC store, write-ahead log, and deadlock detector — the five parts that make isolation real.

Isolation isn't magic — it's implemented by specific components inside the database engine. Here are the main pieces:

Internal Working — Locks vs MVCC

Two big strategies, one usual answer: pessimistic locking blocks readers and writers, MVCC gives every transaction its own snapshot, and every modern engine mixes both.

There are two broad strategies databases use to implement isolation. Most modern systems use a mix of both.

7.1 · STRATEGY 1 — PESSIMISTIC LOCKING

The database assumes conflicts are likely, so it locks data before allowing access. If Transaction A is writing to a row, Transaction B must wait until A commits or rolls back before it can write (and sometimes even read) that same row.

• Shared Lock (S) — multiple transactions can hold this at once, used for reads. Blocks writers.
• Exclusive Lock (X) — only one transaction can hold this, used for writes. Blocks everyone else.
• Two-Phase Locking (2PL) — a transaction acquires all the locks it needs first (growing phase), then releases them only after it's done (shrinking phase). This is the classical technique for guaranteeing serializability with locks.

7.2 · LOCK COMPATIBILITY

7.3 · LOCK GRANULARITY

Databases don't always lock at the same level of detail. Choosing the right granularity is itself a trade-off between concurrency and overhead:

• Row-level locks — lock only the specific row being read or written. Maximum concurrency, but more bookkeeping overhead if millions of rows are touched.
• Page-level locks — lock a whole disk page (which might contain many rows). A middle ground used by some older or simpler engines.
• Table-level locks — lock the entire table. Cheap to manage but blocks everyone else working on that table; usually reserved for schema changes (like ALTER TABLE) rather than everyday transactions.

7.4 · STRATEGY 2 — MULTI-VERSION CONCURRENCY CONTROL (MVCC)

Instead of making readers wait for writers, the database keeps several historical versions of each row. When a transaction starts, it gets a consistent “snapshot” — a point-in-time view of the data as it existed at that moment. Readers never block writers, and writers never block readers, because they're simply looking at different versions of the same row.

PostgreSQL implements MVCC by storing multiple physical row versions with hidden xmin and xmax transaction-id columns marking when each version was created and (if applicable) deleted. Oracle achieves something similar using “undo segments” that let a reader reconstruct an older version of a row on demand. MySQL's InnoDB engine keeps old versions in a structure called the “undo log” as well.

7.5 · LOCKS STILL EXIST UNDER MVCC

Even MVCC databases use row-level locks for writes, to prevent two transactions from both trying to update the exact same row at the same time (this is what causes the classic “could not serialize access” or “deadlock detected” errors you may have seen).

Transaction Lifecycle & Data Flow

BEGIN → Active → PartiallyCommitted → Committed (or Failed → Aborted), and where isolation guarantees actually kick in along the way.

Every transaction, regardless of isolation level, moves through the same basic lifecycle:

1. BEGIN

   The transaction manager assigns a transaction ID and, for MVCC systems, records the current snapshot boundary.

2. Reads

   Depending on isolation level, reads either see the latest committed data (Read Committed) or a frozen snapshot (Repeatable Read / Serializable).

3. Writes

   The engine acquires row locks and either updates data in place (locking systems) or writes a new row version (MVCC).

4. Conflict Check

   Under stricter levels, the database checks whether any concurrent transaction has touched the same data in a conflicting way.

5. COMMIT

   Changes are made permanent and visible to future transactions; the write-ahead log is flushed to disk for durability.

6. ROLLBACK

   If any step failed or a conflict was detected, all changes are undone as if they never happened.

Advantages, Disadvantages & Trade-offs

Correctness on one axis, coordination cost on the other — and why “never wrong” and “never waits” cannot both be maximized.

Performance & Scalability

Why teams default to Read Committed, reach for FOR UPDATE where correctness matters, and keep transactions ruthlessly short.

In practice, most systems don't use Serializable everywhere because it can hurt throughput under high contention. Instead, teams commonly:

• Use Read Committed (the default in PostgreSQL and Oracle) for most everyday queries, since it's fast and prevents dirty reads.
• Reserve Serializable or explicit row locking (SELECT ... FOR UPDATE) for the specific operations that truly need it, like debiting a wallet balance.
• Keep transactions short — the longer a transaction stays open, the longer it holds locks or keeps old row versions alive, both of which hurt other transactions.
• Add appropriate indexes, since row-level locks in most engines are actually applied to index entries — a missing index can accidentally cause the engine to lock far more rows (or even the whole table) than necessary.

-- Lock the specific row so no other transaction can modify it
-- until this transaction commits or rolls back
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 700 WHERE id = 1;
COMMIT;

FOR UPDATE is a common, lighter-weight alternative to full Serializable isolation: it locks only the specific rows you touch, letting Read Committed handle everything else at full speed.

10.1 · MEASURING THE COST

Database teams typically watch metrics like lock wait time, number of deadlocks per hour, and transaction retry rate to understand whether their chosen isolation level is causing a performance bottleneck. A sudden spike in retries after a code deployment is a common early sign that a new query pattern is creating unexpected contention.

10.2 · CONNECTION POOLING AND TRANSACTION SCOPE

Application frameworks (like Spring in Java) manage database connections through a pool, since opening a new connection for every request is slow. A transaction typically holds one pooled connection for its entire duration. If transactions run long — for example, because a developer accidentally left a slow, unrelated computation inside a @Transactional method — this ties up a scarce pooled connection, which can starve other requests even before any lock contention becomes a problem. This is why “keep transactions short” is repeated so often: it isn't just about locks, it's about connection pool health too.

10.3 · BATCHING AND CONTENTION HOTSPOTS

A single, frequently updated row (like a global counter, or the “available seats” field for a single popular event) becomes what's called a hotspot — every concurrent transaction wants to lock the exact same row, forcing them into a queue no matter how well-indexed the query is. Common fixes include splitting the counter into multiple shards that are summed on read, using a dedicated in-memory queue in front of the database, or using database-native atomic increment operations that avoid a full read-modify-write cycle.

High Availability & Isolation in Distributed Systems

CAP, Spanner's TrueTime, CockroachDB's Raft, DynamoDB's quorums, and Cassandra's lightweight transactions — different bets on the same trade-off.

Isolation gets significantly harder once data is spread across multiple machines — for example, in a sharded database or a globally replicated system used by companies like Google or Amazon. A transaction might need to touch rows that live on different physical servers, and coordinating a consistent snapshot across all of them takes real engineering effort.

11.1 · THE CAP THEOREM CONNECTION

The CAP theorem says a distributed system can only fully guarantee two out of three properties during a network failure: Consistency, Availability, and Partition tolerance. Strong isolation is closely tied to strong consistency — and pushing for very strong isolation across a distributed system usually means accepting either higher latency or reduced availability during network partitions.

11.2 · HOW DISTRIBUTED DATABASES APPROACH THIS

11.3 · REPLICATION AND READ REPLICAS

Many production systems route read queries to replica servers for scalability. Because replication has some lag, a read from a replica might show slightly older data than the primary server — this is a deliberate trade-off called read-your-writes vs eventual consistency, separate from (but related to) transaction isolation. Systems that need strict isolation across reads and writes usually route related operations to the same node or use “read-your-own-writes” session guarantees.

11.4 · QUORUM-BASED SYSTEMS

Some distributed databases (like Cassandra or DynamoDB in certain configurations) don't rely on a single leader for every write. Instead, they use quorum reads and writes: a write is considered successful once a majority of replicas acknowledge it, and a read queries a majority of replicas and returns the most recent value among them. This gives tunable consistency — you can trade off latency against strictness by adjusting how many replicas must respond — but it generally provides weaker isolation guarantees than a single-leader, MVCC-based relational database, and typically doesn't offer multi-row transactional isolation out of the box.

11.5 · FAILURE RECOVERY AND ISOLATION

When a database crashes mid-transaction, the write-ahead log is what allows it to recover correctly: on restart, the engine replays committed changes that hadn't yet been flushed to the main data files, and discards any changes from transactions that never reached the commit point. This recovery process is what makes durability and isolation work together — a transaction that appeared “committed” to a client must never partially disappear after a crash, and a transaction that never committed must never partially appear.

Security Angle

TOCTOU coupon exploits, multi-tenant leakage under Read Uncommitted, and why financial audits always check isolation levels alongside encryption.

Isolation is mostly a correctness feature, but it has real security implications too:

• Preventing information leakage between tenants — in multi-tenant SaaS systems, weak isolation combined with poor row-level security could let one customer's in-flight (uncommitted) data leak to another session under Read Uncommitted.
• Race-condition exploits — attackers sometimes deliberately trigger lost updates or write skew (for example, redeeming the same discount coupon twice by firing two requests at the exact same millisecond). Correct isolation (often Serializable or explicit locking) is a direct defense against this class of bug, sometimes called a “TOCTOU” — time-of-check to time-of-use — vulnerability.
• Auditability — durable, isolated transaction logs give security teams a trustworthy trail of exactly what changed and when, which is essential for compliance in regulated industries like banking and healthcare.

12.1 · ISOLATION AND COMPLIANCE

Regulated industries often have explicit rules that touch on transaction correctness, even if they don't use the word “isolation” directly. For example, financial regulations frequently require that account balance changes be traceable, non-repudiable, and free from double-processing — all of which depend on the database correctly preventing lost updates and dirty reads. When security teams perform audits of financial or healthcare systems, reviewing the isolation level and locking strategy used around sensitive write paths is a standard part of the assessment, alongside more commonly discussed topics like encryption and access control.

Monitoring, Logging & Metrics

Lock wait time, deadlock rate, serialization failures, long-running transactions, replication lag — five signals every DBA watches.

Production teams track isolation-related health using a handful of key signals:

Tools like PostgreSQL's pg_stat_activity and pg_locks, MySQL's performance_schema, and general-purpose observability platforms (Datadog, Prometheus with database exporters, New Relic) are commonly used to expose these metrics on dashboards, with alerts set on deadlock spikes or lock-wait percentiles crossing a threshold.

-- Find queries currently waiting on a lock
SELECT pid, wait_event_type, wait_event, query, state
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- See which transaction is blocking which
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query
FROM pg_locks blocked
JOIN pg_locks blocking
  ON blocked.locktype = blocking.locktype
 AND blocked.relation = blocking.relation
 AND blocked.pid != blocking.pid
JOIN pg_stat_activity blocked ON blocked.pid = blocked.pid;

These two diagnostic queries are commonly reached for during an incident when requests suddenly start timing out under load.

Deployment & Cloud Databases

PostgreSQL's SSI, MySQL's gap locks, Oracle's undo segments, SQL Server's snapshot mode, Spanner's external consistency, CockroachDB's single-level design.

Here's how isolation defaults and options look across widely used systems as of today:

When deploying to managed cloud services (Amazon RDS, Aurora, Google Cloud SQL, Azure Database), the underlying engine (PostgreSQL or MySQL compatibility mode) still determines the isolation behavior — the cloud layer mainly changes replication, backup, and scaling mechanics, not the core isolation semantics.

14.1 · CLOUD-SPECIFIC CONSIDERATIONS

• Amazon Aurora separates compute from a distributed, multi-copy storage layer, but preserves standard PostgreSQL/MySQL isolation semantics on top of it — the isolation guarantees your application relies on don't change just because the storage engine underneath is different.
• Read replicas in managed services typically apply changes asynchronously, so a report generated from a read replica endpoint might lag the primary by anywhere from milliseconds to a few seconds under heavy load — this is a replication concern layered on top of, not a replacement for, transaction isolation.
• Serverless database offerings (like Aurora Serverless or Cloud Spanner's autoscaling) can pause or scale compute resources, which occasionally affects how long a long-running transaction can safely stay open before being reset — another reason short transactions are considered a best practice.
• Multi-region deployments add real network latency to any transaction that must coordinate across regions for strong isolation — teams often deliberately design their data model so that latency-sensitive isolation-critical writes (like a wallet balance) stay within a single region, while lower-stakes data replicates globally with weaker consistency.

APIs, Microservices & Distributed Transactions

Why two-phase commit fell out of favor, and how the saga pattern trades strict cross-service isolation for the availability that microservices actually need.

Isolation within a single database is a well-solved problem. It becomes much harder when a business operation spans multiple independent services, each with its own database — a common situation in microservice architectures.

15.1 · WHY TWO-PHASE COMMIT FELL OUT OF FAVOR

The traditional answer was Two-Phase Commit (2PC) — a coordinator asks every participating database to “prepare,” waits for all of them to say yes, then tells them all to commit. It works, but it holds locks across all participants for the entire round trip, which can badly hurt availability if any one service is slow or down.

15.2 · THE SAGA PATTERN

Modern microservice systems more often use the Saga pattern instead: break the operation into a sequence of local transactions, each with its own database commit, and define a compensating action (like a refund) for each step in case a later step fails. This sacrifices strict cross-service isolation (other services might briefly see a partially completed saga) in exchange for much better availability and scalability.

Design Patterns & Anti-patterns

Optimistic concurrency, consistent lock ordering, idempotency keys — and the “Serializable everywhere just to be safe” trap.

16.1 · GOOD PATTERNS

16.2 · ANTI-PATTERNS TO AVOID

Best Practices & Common Mistakes

Default to Read Committed, escalate deliberately, wrap Serializable in retries, keep transactions short — and never ship code you haven't tested under real concurrent load.

Real-World Industry Examples

Core banking, flash sales, ride-hailing dispatch, seat holds, and Netflix's deliberate mix of strong-and-weak — the same principle applied everywhere.

18.1 · WHAT THESE EXAMPLES HAVE IN COMMON

Across every one of these industries, the underlying engineering decision is the same: identify exactly which operations would cause real damage (financial loss, overselling, double-booking, safety issues) if isolation were weak, and apply the strongest guarantee only there. Everything else — browsing history, product recommendations, view counters, non-critical logs — is usually served from weaker, faster, more available paths. This selective application of strong isolation is a recurring theme in system design interviews: candidates who can explain where they'd apply Serializable isolation, and why everywhere else doesn't need it, tend to demonstrate stronger practical judgment than candidates who either apply it everywhere or nowhere.

Frequently Asked Questions

The recurring questions, answered in the same shape every real interviewer and every real DBA phrases them.

Summary & Key Takeaways

The seven ideas worth memorising, and the one question that quietly rescues most production code.

20.1 · KEY TAKEAWAYS

• Isolation is the “I” in ACID: it ensures concurrent transactions don't corrupt each other's work, ideally behaving as if they ran one at a time.
• Without isolation, systems suffer from dirty reads, non-repeatable reads, phantom reads, lost updates, and write skew.
• The SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each preventing a progressively larger set of anomalies at a progressively higher performance cost.
• Databases implement isolation using pessimistic locking, MVCC (multi-version snapshots), or a combination of both; most modern engines lean heavily on MVCC for read scalability.
• Distributed systems make isolation much harder, connecting directly to the CAP theorem and requiring techniques like consensus protocols (Raft, Paxos) and synchronized clocks (Spanner's TrueTime).
• In microservice architectures, strict cross-service isolation is often traded for the Saga pattern and eventual consistency, in exchange for better availability.
• Good engineering practice means choosing the weakest isolation level that is still safe for each specific operation — not the strongest one everywhere, and not the weakest one everywhere.

20.2 · THE ONE HABIT WORTH KEEPING

If you take away just one habit from this entire guide, let it be this: before writing any code that reads a value, makes a decision, and writes a result back, pause and ask “what happens if this exact same code runs twice, at the exact same millisecond, on two different requests?” That single question is the seed of almost everything covered above, from dirty reads to write skew to the Saga pattern, and asking it consistently is what separates code that merely works in a demo from code that survives real, concurrent, production traffic.