Postgres Isolation Levels and Serialization Retries in Rust

Two requests update the same account at the same moment. Whether the result is right depends on isolation, which most application code never thinks about until a balance goes negative. This post explains what the levels in Postgres guarantee and how to write Rust that works with the failures the stronger levels produce. Versions: Postgres 17, SQLx 0.8, Rust 1.90.

The levels

| Level | Each statement sees | Prevents | Still allows | |---|---|---|---| | READ COMMITTED (default) | rows committed before the statement began | dirty reads | non-repeatable reads, phantoms, write skew | | REPEATABLE READ | a snapshot from the transaction's first statement | the above except write skew | write skew | | SERIALIZABLE | snapshot plus conflict detection | all anomalies | nothing, but may abort |

Postgres has no dirty reads at any level (READ UNCOMMITTED behaves as READ COMMITTED).

A write skew example

Two on-call doctors each check that at least two are on call, then each takes themselves off:

-- both transactions
SELECT count(*) FROM on_call WHERE shift = 'night';        -- sees 2
UPDATE on_call SET active = false WHERE doctor = :me;      -- different rows
COMMIT;

Under READ COMMITTED and REPEATABLE READ both commit, leaving zero doctors on call, since neither wrote the other's row. Under SERIALIZABLE, Postgres detects the read-write dependency cycle and aborts one transaction:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

That is SQLSTATE 40001. The error is the feature: the database refused to let the invariant break.

Choosing the level in SQLx

use sqlx::{PgPool, Postgres, Transaction};

async fn begin_serializable(pool: &PgPool) -> Result<Transaction<'_, Postgres>, sqlx::Error> {
    let mut tx = pool.begin().await?;
    sqlx::query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE").execute(&mut *tx).await?;
    Ok(tx)
}

SET TRANSACTION must be the first statement in the transaction. Alternatively set default_transaction_isolation for a role or a connection.

The retry loop

use std::time::Duration;

fn is_retryable(e: &sqlx::Error) -> bool {
    match e {
        sqlx::Error::Database(db) => matches!(db.code().as_deref(), Some("40001") | Some("40P01")),
        _ => false,
    }
}

pub async fn with_retry<T, F, Fut>(pool: &PgPool, mut work: F) -> Result<T, sqlx::Error>
where
    F: FnMut(Transaction<'static, Postgres>) -> Fut,
    Fut: std::future::Future<Output = Result<T, sqlx::Error>>,
{
    let mut attempt = 0u32;
    loop {
        let tx = pool.begin().await?;
        match work(tx).await {
            Ok(v) => return Ok(v),
            Err(e) if is_retryable(&e) && attempt < 5 => {
                attempt += 1;
                let backoff = Duration::from_millis(10 * 2u64.pow(attempt) + fastrand::u64(0..10));
                tracing::warn!(attempt, "retrying transaction after conflict");
                tokio::time::sleep(backoff).await;
            }
            Err(e) => return Err(e),
        }
    }
}

The closure takes ownership of a fresh transaction and is expected to commit it. Because the body can run several times, it must be free of side effects other than database writes. A conflict can appear at any statement or at COMMIT, so the check wraps the whole closure including the commit. Cap attempts and add jitter, otherwise colliding transactions retry in lockstep (async resilience patterns).

Deadlocks

Two transactions that lock rows in opposite order wait for each other. Postgres detects this after deadlock_timeout (1 s by default) and aborts one with 40P01. The same retry handles it, and the better fix is a consistent lock order: always update rows sorted by id.

Explicit locking at the default level

Often you do not need SERIALIZABLE. A targeted lock expresses the intent:

SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;

Other transactions touching that row wait. For queues, FOR UPDATE SKIP LOCKED (SKIP LOCKED queues). For counters, a single UPDATE accounts SET balance = balance - $2 WHERE id = $1 AND balance >= $2 is atomic without any extra level. For versioned rows, optimistic checks (optimistic locking).

Guidelines

1. Keep transactions short: no network calls or user waits inside.
2. Make retried work idempotent, and keep emails and webhooks outside, using an outbox (outbox pattern).
3. Use SERIALIZABLE selectively, for the transactions that guard cross-row invariants, and measure abort rates.
4. Read-only analytical queries can use REPEATABLE READ READ ONLY DEFERRABLE for a consistent snapshot without aborts.
5. Test concurrency with two tasks and a barrier, to reproduce the anomaly deliberately (testing concurrent code for in-process races).

For the transaction API itself see SQLx transactions.