Database Concurrency Control in Practice: Isolation Levels, Locking Strategies, and MVCC for Production Systems

Most bugs in production systems are not logic bugs. They are concurrency bugs. Two requests arrive at the same millisecond, both read the same row, both decide to proceed, and then both write. The result is wrong. The logs look clean. The tests never caught it because tests rarely run concurrent requests.

Understanding how your database handles concurrent access is not optional knowledge for production engineers. It determines whether your inventory can go negative, whether your financial ledger can double-count, and whether your reservation system can overbook.

This guide covers the mechanics: isolation levels, MVCC, locking strategies, and how to apply them to real scenarios.

The Four Isolation Levels

The SQL standard defines four isolation levels. They exist on a spectrum between performance and correctness. Each level prevents a specific class of anomaly that the level below it allows.

Read Uncommitted

A transaction can read rows that another transaction has modified but not yet committed. This is called a dirty read.

Scenario: Transaction A starts updating an account balance from $100 to $50. Before A commits, Transaction B reads the balance and sees $50. Transaction A then rolls back. B made a decision based on data that never existed.

Almost no production system uses Read Uncommitted. Postgres does not even implement it (it silently upgrades to Read Committed). MySQL supports it, but it is rarely appropriate outside of analytics workloads where approximate counts matter more than precision.

Read Committed

A transaction only sees data that has been committed. Dirty reads are prevented. This is the default in Postgres and Oracle.

But Read Committed allows non-repeatable reads. Transaction A reads a row. Transaction B updates and commits that row. Transaction A reads it again within the same transaction and sees a different value.

Scenario: You are building a billing system. Transaction A reads a user’s plan tier to calculate their invoice. Between the two reads, Transaction B upgrades the user’s plan. Transaction A now has inconsistent data within a single transaction.

Read Committed also allows phantom reads. Transaction A queries for all orders with status = ‘pending’. Transaction B inserts a new pending order and commits. Transaction A runs the same query again and gets a different result set.

Repeatable Read

A transaction sees a consistent snapshot of rows it has already read. Non-repeatable reads are prevented. This is the default in MySQL InnoDB.

Repeatable Read still allows phantom reads in the standard definition. However, Postgres’s implementation of Repeatable Read (via MVCC, covered below) prevents phantom reads in practice, making it stronger than the SQL standard requires.

Serializable

The strongest isolation level. Transactions execute as if they ran one at a time, serially. All anomalies are prevented, including write skew.

Write skew is subtle. Two transactions each read an overlapping set of rows, make decisions based on what they read, and then each writes to a different row. Neither write conflicts with the other at the row level, but the combined result violates a constraint that depends on both rows together.

Scenario: A hospital on-call system requires at least one doctor to be on call at all times. Doctor A and Doctor B are both on call. Each runs a transaction: check if there is at least one other doctor on call, then take themselves off. Both read two doctors on call. Both decide it is safe to remove themselves. Both commit. Zero doctors are on call.

Serializable isolation catches this because it tracks the read/write dependencies between transactions and aborts one of them when a cycle is detected (using Serializable Snapshot Isolation in Postgres, or two-phase locking in traditional systems).

How MVCC Works

Most modern databases use Multi-Version Concurrency Control to implement isolation without requiring readers to block writers. Instead of locking rows for reads, the database maintains multiple versions of each row.

Postgres MVCC

Every row in Postgres has two hidden system columns: xmin (the transaction ID that created this row version) and xmax (the transaction ID that deleted or updated it, or zero if the row is still live).

When a transaction starts, Postgres records its transaction ID and a snapshot of which other transactions were active at that moment. When the transaction reads a row, it applies visibility rules:

• The row’s xmin must be committed and must have started before this transaction’s snapshot.
• The row’s xmax must be either zero (not deleted) or an uncommitted transaction ID (meaning the deletion has not happened yet from this transaction’s perspective).

When a transaction updates a row, Postgres does not modify the existing row in place. It inserts a new row version with the new values and marks the old row version with an xmax pointing to the updating transaction. If the transaction commits, the old version becomes invisible to future snapshots. If it rolls back, the new version is discarded.

This means reads never block writes and writes never block reads. The cost is storage bloat from dead row versions, which is why Postgres requires regular VACUUM to reclaim space.

MySQL InnoDB MVCC

MySQL InnoDB uses a similar approach but stores old row versions in a separate undo log segment rather than inline in the table. When a transaction needs an older version of a row, InnoDB reconstructs it by applying undo log entries in reverse.

The advantage is that the main table does not accumulate dead versions. The disadvantage is that long-running transactions cause the undo log to grow, and old versions must be reconstructed on every read that needs them.

Both Postgres and MySQL provide a consistent read view for the duration of a transaction (in Repeatable Read or Serializable mode). Under Read Committed, each statement within the transaction gets a fresh snapshot, which is why non-repeatable reads are possible.

Pessimistic Locking

Pessimistic locking assumes conflicts are likely and prevents them upfront by locking rows before operating on them.

The primary tool in SQL is SELECT FOR UPDATE. It acquires an exclusive lock on the selected rows, preventing other transactions from acquiring any lock on them until the current transaction commits or rolls back.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function reserveInventory(
  productId: string,
  quantity: number,
  orderId: string
): Promise<boolean> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // Lock the inventory row for this product exclusively.
    // Any other transaction trying to lock this row will wait here.
    const { rows } = await client.query<{ available: number }>(
      `SELECT available FROM inventory WHERE product_id = $1 FOR UPDATE`,
      [productId]
    );

    if (rows.length === 0) {
      await client.query("ROLLBACK");
      return false;
    }

    const available = rows[0].available;

    if (available < quantity) {
      await client.query("ROLLBACK");
      return false;
    }

    await client.query(
      `UPDATE inventory SET available = available - $1 WHERE product_id = $2`,
      [quantity, productId]
    );

    await client.query(
      `INSERT INTO reservations (order_id, product_id, quantity) VALUES ($1, $2, $3)`,
      [orderId, productId, quantity]
    );

    await client.query("COMMIT");
    return true;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

The lock is held from the SELECT FOR UPDATE until the COMMIT or ROLLBACK. Any concurrent transaction that tries to lock the same row will block. This is correct, but it introduces latency under contention.

SELECT FOR UPDATE SKIP LOCKED is a variant that skips locked rows instead of waiting. It is useful for job queue patterns where multiple workers pull tasks and you want each worker to grab a different task without blocking.

async function claimNextJob(workerId: string): Promise<string | null> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const { rows } = await client.query<{ id: string }>(
      `SELECT id FROM jobs
       WHERE status = 'pending'
       ORDER BY created_at
       LIMIT 1
       FOR UPDATE SKIP LOCKED`
    );

    if (rows.length === 0) {
      await client.query("ROLLBACK");
      return null;
    }

    const jobId = rows[0].id;

    await client.query(
      `UPDATE jobs SET status = 'processing', worker_id = $1 WHERE id = $2`,
      [workerId, jobId]
    );

    await client.query("COMMIT");
    return jobId;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Deadlocks with Pessimistic Locking

Pessimistic locking introduces deadlock risk. Transaction A locks row 1, then tries to lock row 2. Transaction B locks row 2, then tries to lock row 1. Both wait forever. The database detects this cycle and aborts one transaction with an error.

To prevent deadlocks when locking multiple rows, always acquire locks in a consistent order. If your application always locks accounts in ascending ID order, the cycle cannot form.

async function transferFunds(
  fromAccountId: string,
  toAccountId: string,
  amount: number
): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // Always lock accounts in ascending ID order to prevent deadlocks.
    const [firstId, secondId] =
      fromAccountId < toAccountId
        ? [fromAccountId, toAccountId]
        : [toAccountId, fromAccountId];

    await client.query(
      `SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE`,
      [firstId, secondId]
    );

    const { rows } = await client.query<{ id: string; balance: number }>(
      `SELECT id, balance FROM accounts WHERE id IN ($1, $2)`,
      [fromAccountId, toAccountId]
    );

    const from = rows.find((r) => r.id === fromAccountId)!;
    const to = rows.find((r) => r.id === toAccountId)!;

    if (from.balance < amount) {
      await client.query("ROLLBACK");
      throw new Error("Insufficient funds");
    }

    await client.query(
      `UPDATE accounts SET balance = balance - $1 WHERE id = $2`,
      [amount, fromAccountId]
    );

    await client.query(
      `UPDATE accounts SET balance = balance + $1 WHERE id = $2`,
      [amount, toAccountId]
    );

    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Optimistic Locking

Optimistic locking assumes conflicts are rare. Transactions proceed without acquiring locks. At commit time, the system checks whether the data has changed since it was read. If it has, the transaction is aborted and the caller must retry.

The common implementation uses a version column. Each row carries a version number that increments on every update. The update statement includes a WHERE version = <version-read> clause. If the row was updated by another transaction in the interim, the version will not match and zero rows will be updated, signaling a conflict.

interface Product {
  id: string;
  price: number;
  version: number;
}

async function updatePrice(
  productId: string,
  newPrice: number,
  maxRetries = 3
): Promise<void> {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const { rows } = await pool.query<Product>(
      `SELECT id, price, version FROM products WHERE id = $1`,
      [productId]
    );

    if (rows.length === 0) {
      throw new Error("Product not found");
    }

    const product = rows[0];

    const result = await pool.query(
      `UPDATE products
       SET price = $1, version = version + 1
       WHERE id = $2 AND version = $3`,
      [newPrice, productId, product.version]
    );

    if (result.rowCount === 1) {
      // Success: no concurrent modification.
      return;
    }

    // Another transaction updated the row. Retry after a short backoff.
    const backoffMs = Math.min(50 * 2 ** attempt, 500);
    await new Promise((resolve) => setTimeout(resolve, backoffMs));
  }

  throw new Error(`Failed to update price after ${maxRetries} attempts`);
}

Optimistic locking works well when read throughput is high and write conflicts are genuinely rare. Price catalog updates, user profile edits, and content management systems are good fits.

It is wrong for high-contention scenarios. If ten requests race to decrement the same inventory counter, nine of them will retry. Under sustained load, this creates a retry storm that amplifies database pressure rather than reducing it.

Locking Strategies: Tradeoffs

Strategy	Throughput	Deadlock Risk	Correctness	Best Fit
Pessimistic (SELECT FOR UPDATE)	Lower under contention	Medium (requires ordering)	High	Inventory, wallets, seats
SKIP LOCKED	High	Low	Task-scoped only	Job queues, work dispatch
Optimistic (version column)	High under low contention	None	High when conflicts are rare	Catalog, profiles, CMS
Serializable isolation	Lowest	None (SSI aborts instead)	Highest	Financial audits, on-call systems
Read Committed (default)	Highest	None	Allows non-repeatable reads	Read-heavy analytics, reporting

Choosing Isolation Levels for Common Scenarios

Double-Spending Prevention

A user has a $200 wallet balance. Two requests arrive simultaneously: buy item A for $150, buy item B for $100. Without locking, both read $200, both pass the balance check, and both deduct. The balance goes to -$50.

Use pessimistic locking with SELECT FOR UPDATE on the wallet row. One request will acquire the lock, complete, and release it. The other will then read the updated balance ($50), fail the check, and return an error. This is the correct behavior.

Read Committed with optimistic locking would also work, but the retry burden under high throughput on a single wallet is poor UX. For wallets and escrow accounts, pessimistic locking is the right default.

Inventory Reservation

An e-commerce product has 3 units in stock. Fifty users try to add it to their cart at the same time.

The correct pattern is pessimistic locking on the inventory row. SELECT FOR UPDATE serializes the writes. Each transaction decrements the count and checks the result. When the count hits zero, subsequent transactions see it immediately and return out-of-stock.

One refinement: if you need high read throughput for display purposes but only need correctness at checkout, use Read Committed for the display query (acceptable that it shows 3 while a concurrent checkout is in progress) and Repeatable Read with pessimistic locking for the actual reservation transaction.

Financial Ledgers

Double-entry ledgers are append-only by design. You never update existing ledger rows. Each transaction inserts debit and credit rows and updates the running balance on the account.

The account balance update is the critical section. Use SELECT FOR UPDATE on the account row before computing and writing the balance update. This serializes all writes to a single account.

If your ledger is high volume (payment processors, crypto exchanges), consider a different design: do not store a running balance at all. Compute it on read as SUM(amount) over ledger entries. This eliminates the write contention on the balance column entirely. Caching the computed balance with a generation counter handles read performance.

For multi-account operations (transfers), use Serializable isolation or acquire locks in a consistent order as shown in the transfer example above.

Analytics Queries on Live Data

If you are running reports or dashboards against the same database handling transactional writes, Read Committed is usually fine. You accept that the report reflects a slightly inconsistent state, but for aggregated counts and sums, the error is negligible.

If you need a truly consistent snapshot for a long-running analytical query, consider Repeatable Read. This gives you a stable view of the data as of the transaction start time, using MVCC without blocking writers. The cost is that your transaction holds an older snapshot for longer, which in Postgres means VACUUM cannot reclaim dead versions older than your snapshot’s xmin.

Production Considerations

Long-running transactions are the most common source of operational problems with MVCC. A transaction open for minutes or hours holds a snapshot that prevents Postgres VACUUM from reclaiming old row versions. The table bloats. Query performance degrades. Monitoring pg_stat_activity for long-running transactions and setting statement_timeout and idle_in_transaction_session_timeout are basic operational hygiene.

For Serializable isolation in Postgres, SSI uses predicate locks (tracking what you read, not just what you write) and adds overhead. Benchmark your specific workload. For OLTP systems with short transactions and moderate concurrency, the overhead is often acceptable. For batch jobs with thousands of rows per transaction, it can be significant.

If you are using an ORM, verify what isolation level it uses by default and whether its locking helpers map to what you expect. Some ORMs implement optimistic locking as a convenience feature. Others use SELECT FOR UPDATE under the hood for methods named things like lock(). Read the generated SQL, not just the documentation.

Advisory locks in Postgres are worth knowing about for coordination problems that do not map cleanly to rows. pg_try_advisory_lock(key) acquires a session-level lock on an arbitrary integer key. This is useful for distributed leader election or ensuring a background job runs on only one application instance.

Closing

Concurrency control is not a database configuration detail. It is part of your application’s correctness model. Choosing the wrong isolation level is equivalent to writing a race condition into your architecture. The database will not save you by default.

The practical starting point: use Read Committed for reads and reporting, pessimistic locking (SELECT FOR UPDATE) for anything that modifies shared numeric state under contention, and Serializable for complex invariants that span multiple rows. Optimistic locking is the right call when you have measured that conflicts are rare and the retry cost is acceptable.

Understanding what your database actually does when two transactions arrive at the same time is the difference between a system that works and a system that mostly works.