SQLx Transactions in Rust and Avoiding Half Written Data

A transfer that debits one account and fails before crediting the other is a bug report with a dollar amount. Transactions are the tool that rules it out, and SQLx makes the happy path short and the failure path safe by default: forgetting to commit rolls back. This post covers the shape, the helper function trick and the traps. It follows SQLx explained.

sqlx 0.8, PostgreSQL 17, Rust 1.90.

A transfer

use sqlx::PgPool;

pub async fn transfer(pool: &PgPool, from: i64, to: i64, cents: i64) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    let updated = sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1")
        .bind(cents)
        .bind(from)
        .execute(&mut *tx)
        .await?
        .rows_affected();
    if updated == 0 {
        return Err(sqlx::Error::RowNotFound);
    }

    sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
        .bind(cents)
        .bind(to)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

If the debit finds insufficient funds, the early return drops tx, and SQLx sends ROLLBACK. If the credit fails, ? returns and the same happens. Only reaching commit makes changes visible. The balance >= $1 condition in the UPDATE makes the check and the change one atomic statement, which avoids a race between a read and a write; the in-memory version of the same mistake is a read followed by a write on shared state, as in Arc and Mutex.

Helpers inside a transaction

A function that should work both alone and inside a transaction takes a connection:

use sqlx::PgConnection;

async fn debit(conn: &mut PgConnection, id: i64, cents: i64) -> Result<u64, sqlx::Error> {
    Ok(sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
        .bind(cents)
        .bind(id)
        .execute(conn)
        .await?
        .rows_affected())
}

Call it as debit(&mut tx, ...) (a Transaction derefs to the connection) or with &mut *pool.acquire().await?. Taking &PgPool instead would run on a different connection, outside the transaction, which is a silent bug.

Errors abort the transaction

In PostgreSQL, once a statement fails, the transaction is in an aborted state, and every later statement returns current transaction is aborted, commands ignored until end of transaction block. If you catch a unique violation to handle it, do it with a savepoint (tx.begin().await? creates a nested savepoint) or roll back and start over. Handling the error itself is in unique violations in SQLx.

Keep it short

A transaction holds a connection from the pool and any row locks it took. Do the slow work first, then open the transaction, run the statements, commit. A common mistake is to call another service between two statements: the locks last as long as that call, and the pool shrinks, as in pool sizing.

Locking reads

SELECT ... FOR UPDATE locks the rows until commit, which makes read-modify-write safe at the price of waiting. Add SKIP LOCKED for work queues. Choose the least locking that is correct, and order the locks consistently to avoid deadlocks.

Limits

Transactions do not cover side effects outside the database. For those, write an outbox row in the same transaction and let a worker perform the effect. The API is documented on sqlx::Transaction.