MVCC, Transaction Isolation, and the Anomalies You Will Hit

The Black Box

Two dispatchers in the logistics platform assign deliveries simultaneously. Dispatcher A assigns driver D-100 to route R-500. Dispatcher B assigns driver D-100 to route R-501. Both transactions commit successfully. Driver D-100 now has two routes. The application has no locking, no constraints, and no visibility into the other transaction’s in-flight changes.

This is not a theoretical concurrency problem. It is the default behavior of PostgreSQL’s Read Committed isolation level.

The Mechanism

PostgreSQL implements two practical isolation levels:

Read Committed (default): Each statement within a transaction sees the most recently committed data at the time the statement begins. Different statements within the same transaction can see different snapshots.

Serializable: The entire transaction sees a single snapshot taken at the start. If two concurrent transactions modify the same data in a way that would produce a result inconsistent with some serial ordering, one of them is aborted with a serialization failure.

-- Concept: the double-assignment anomaly under Read Committed

-- Transaction A (Dispatcher A):
BEGIN;
SELECT assigned_route FROM drivers WHERE driver_id = 'D-100';
-- Result: NULL (driver is available)
UPDATE drivers SET assigned_route = 'R-500' WHERE driver_id = 'D-100';
COMMIT;  -- Success

-- Transaction B (Dispatcher B), concurrent:
BEGIN;
SELECT assigned_route FROM drivers WHERE driver_id = 'D-100';
-- Result: NULL (driver is available, because A has not committed yet)
UPDATE drivers SET assigned_route = 'R-501' WHERE driver_id = 'D-100';
-- B blocks here until A commits (row-level lock on D-100)
-- A commits. B's UPDATE proceeds.
COMMIT;  -- Success. Driver D-100 now has route R-501.
-- But B made its assignment decision based on stale data.
-- B's SELECT saw NULL, but by the time B's UPDATE ran, the driver was already assigned.

Under Read Committed, B’s SELECT sees a snapshot from before A’s COMMIT. B’s UPDATE blocks until A commits (because of the row-level lock on driver_id = 'D-100'), then proceeds. The UPDATE succeeds because Read Committed re-evaluates the WHERE clause after acquiring the lock. But the application logic (check if available, then assign) is based on the stale SELECT result.

The Serializable Fix

-- Concept: preventing double-assignment with Serializable isolation

-- Transaction A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT assigned_route FROM drivers WHERE driver_id = 'D-100';
-- NULL
UPDATE drivers SET assigned_route = 'R-500' WHERE driver_id = 'D-100';
COMMIT;  -- Success

-- Transaction B (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT assigned_route FROM drivers WHERE driver_id = 'D-100';
-- NULL (same snapshot as A's start)
UPDATE drivers SET assigned_route = 'R-501' WHERE driver_id = 'D-100';
COMMIT;
-- ERROR: could not serialize access due to concurrent update
-- SQLSTATE: 40001

-- B must retry. On retry, B's SELECT sees A's committed update.
-- assigned_route = 'R-500'. B can either choose a different driver or report unavailability.

The Observable Consequence

Serializable isolation prevents anomalies but requires the application to handle serialization failures. PostgreSQL returns SQLSTATE 40001 when it detects a conflict. The application must catch this error and retry the entire transaction.

// Concept: retry loop for serialization failures
// The application MUST retry, not just log and return an error

int maxRetries = 3;
for (int attempt = 1; attempt <= maxRetries; attempt++) {
    try (var conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        // Check driver availability
        var check = conn.prepareStatement(
                "SELECT assigned_route FROM drivers WHERE driver_id = ?");
        check.setString(1, "D-100");
        var rs = check.executeQuery();
        rs.next();
        if (rs.getString("assigned_route") != null) {
            conn.rollback();
            return "Driver already assigned";
        }

        // Assign driver
        var assign = conn.prepareStatement(
                "UPDATE drivers SET assigned_route = ? WHERE driver_id = ?");
        assign.setString(1, routeId);
        assign.setString(2, "D-100");
        assign.executeUpdate();

        conn.commit();
        return "Assigned successfully";
    } catch (SQLException e) {
        if ("40001".equals(e.getSQLState()) && attempt < maxRetries) {
            continue;  // Retry the entire transaction
        }
        throw e;
    }
}

The retry rate depends on concurrency. If 10 dispatchers assign drivers simultaneously and the average transaction takes 50ms, the expected retry rate with Serializable isolation is 2-5%. This is acceptable. If 1,000 concurrent transactions compete, the retry rate climbs to 30-50%, and Serializable isolation becomes impractical. At that scale, an explicit advisory lock or a queue-based assignment system is more efficient.

The Decision Rule

Use Read Committed (the default) for workloads where concurrent modifications to the same row are rare and the application can tolerate read anomalies. Most CRUD applications fall here.

Use Serializable isolation when correctness requires that concurrent transactions behave as if they ran one at a time. Assignment problems (drivers, inventory, seats) where double-booking is unacceptable. Always implement a retry loop. Serializable without retry is a bug.

Use explicit row locks (SELECT ... FOR UPDATE) when you need to prevent concurrent modification of specific rows but do not need full serializable semantics. This is the middle ground: more explicit than Serializable, less error-prone than Read Committed for write-write conflicts.