Rustango docs
← Guides

Transactions

An atomic block runs a group of statements as one unit: they all commit, or none of them do. It works on PostgreSQL, MySQL (and MariaDB) and SQLite. Where the backends differ, this page says so.

For a plain transaction handle (transaction_pool) and row locks, see the ORM guide.

New to a term here? Transaction, rollback — see the glossary.

Table of contents

A first block

The examples use this model and a pool: &Pool:

use rustango::sql::Auto;
use rustango::Model;

#[derive(Model, Debug, Clone)]
#[rustango(table = "accounts")]
pub struct Account {
    #[rustango(primary_key)]
    pub id: Auto<i64>,
    #[rustango(unique, max_length = 64)]
    pub email: String,
    pub balance: i64,
}

rustango::sql::atomic(&pool, closure) begins a transaction, runs the closure, and commits when it returns Ok. An Err rolls everything back.

use rustango::sql::{atomic, ExecError, Pool};

async fn transfer(pool: &Pool, from: i64, to: i64, amount: i64) -> Result<(), ExecError> {
    let p = pool.clone(); // the closure must own what it uses
    atomic(pool, move |_tx| {
        Box::pin(async move {
            let mut a = Account::find_or_fail(from, &p).await?;
            let mut b = Account::find_or_fail(to, &p).await?;
            a.balance -= amount;
            b.balance += amount;
            a.save(&p).await?;
            b.save(&p).await?;
            Ok(()) // Ok commits; an Err (or a failed `?`) rolls both saves back
        })
    })
    .await
}

The closure gets an owned clone of the pool, not a borrow: the block's future must not borrow the caller. Pool is cheap to clone. The rustango::atomic!(&pool, |tx| { … }) macro writes the Box::pin for you.

What runs in the block

Inside the closure, every ORM call given the same pool (or a clone of it) runs on the block's transaction:

  • reads: fetch, find, count, get_or_create, …
  • writes: insert, save, delete, bulk writes, queryset updates
  • raw SQL: raw_query_pool, raw_execute_pool
  • DDL (see DDL in a block)
  • framework writes: audit rows, fixtures, ViewSet and admin writes, media, SSO sign-up and invites

They see the block's uncommitted rows, and its rollback undoes them. On SQLite, and on MySQL at its default level (repeatable read), all reads in the block see one snapshot.

These run outside the block, on their own connection: pool.acquire(), Tenant::conn, the cache, the job claim, tenancy registry changes, a task started with tokio::spawn, and a pool connected on its own (even to the same database). They do not see the block's writes, and they can wait on locks the block holds.

The closure's argument is an AtomicTx. tx.lock().await? gives a TxGuard that derefs to PoolTx, for the _tx helpers and the _on forms: acc.insert_tx(&mut *guard). Run one statement at a time: while a guard is held, or when two statements run at once (join!), calls on the pool return ExecError::NestedAtomic. Drop the guard before the next &pool call.

Cost. A read or raw statement costs one round trip. An ORM write costs three, because it runs in its own savepoint (so a failed write leaves the block usable). For many rows, write through the guard (insert_tx) or a bulk write.

Nested blocks

atomic on the same pool inside a block opens a savepoint. Its Err rolls back only its own writes. An Err from the outer block rolls back everything, nested work included.

let p = pool.clone();
atomic(pool, move |_tx| {
    Box::pin(async move {
        account("[email protected]", 0).insert(&p).await?;
        let q = p.clone();
        let inner = atomic(&p, move |_sp| {
            Box::pin(async move {
                account("[email protected]", 0).insert(&q).await?;
                // Stands in for any failure inside the nested block.
                Err::<(), _>(ExecError::Driver(rustango::sql::sqlx::Error::RowNotFound))
            })
        })
        .await;
        assert!(inner.is_err()); // b@ is rolled back, a@ is kept
        Ok(())
    })
})
.await?;

(account(email, balance) builds an Account with id: Auto::Unset.)

Nesting goes by pool object: pass the request's pool down rather than connecting again. A dropped or panicking nested block is rolled back. If a savepoint statement itself fails, the whole block rolls back with ExecError::AtomicAborted. PostgreSQL slows down past 64 savepoints in one transaction, and every ORM write is one, so keep blocks with many writes short.

After the commit

rustango::sql::on_commit(callback) queues a callback to run after the outermost block commits. It never runs on rollback, and a rolled-back nested block drops its callbacks. Callbacks run in the order they were queued.

use rustango::sql::on_commit;

let p = pool.clone();
atomic(pool, move |_tx| {
    Box::pin(async move {
        let mut acc = account("[email protected]", 0);
        acc.insert(&p).await?;
        on_commit(move || {
            tokio::spawn(async move { /* send the welcome email */ });
        });
        Ok(())
    })
})
.await?;

The callback is sync: spawn async work from it. A callback that panics is logged and the others still run. on_commit outside a block panics.

When a statement fails

BackendA failed read or raw statementA failed ORM write
PostgreSQLAborts the block: later statements and atomic return ExecError::AtomicAbortedUndone alone (its savepoint); the block goes on
MySQL, MariaDBUndone alone; the block goes onUndone alone; the block goes on
SQLiteUndone alone; the block goes onUndone alone; the block goes on

A closure that ignores an error and returns Ok commits the rest of the block (on PostgreSQL after an aborted read, atomic returns AtomicAborted instead).

Some errors end the whole transaction on the server: a MySQL deadlock (1213), a MySQL lock-wait timeout (1205) when the server rolls back on it, an SQLite automatic rollback, a lost connection. Every later ORM statement in the block, and atomic itself, then returns ExecError::AtomicAborted. MariaDB reports a nowait() miss as 1205; with the default innodb_rollback_on_timeout = OFF it undoes only the statement.

Keep going after a failed read. To try a step that may fail (a nowait() lock, a probe of a table that may not exist), put it in a nested block. Its failure then undoes only that block, on every backend:

let p = pool.clone();
atomic(pool, move |_tx| {
    Box::pin(async move {
        let q = p.clone();
        // A failed read in a nested block undoes only that block, even on PG.
        let locked = atomic(&p, move |_sp| {
            Box::pin(async move {
                Account::objects()
                    .filter("email", "[email protected]")
                    .nowait()
                    .fetch(&q)
                    .await
            })
        })
        .await;
        match locked {
            Ok(_rows) => { /* got the lock */ }
            Err(_) => { /* row busy: the outer block goes on */ }
        }
        Ok(())
    })
})
.await?;

Raw SQL that ends the transaction (COMMIT, ROLLBACK, or a statement MySQL commits on) makes the block return ExecError::AtomicEndedEarly. Statements after it ran without a transaction, so do not retry blindly.

SQLite takes the write lock at BEGIN

On SQLite the outermost block begins with BEGIN IMMEDIATE. A block that reads and then writes does not fail with SQLITE_BUSY at its first write; instead a second writer waits at its own BEGIN. Other connections that write wait busy_timeout (5 seconds by default) and then fail with SQLITE_BUSY; on an in-memory database they wait with no timeout. Keep SQLite blocks short.

atomic_immediate did this before atomic did. It is deprecated: call atomic.

Isolation levels and settings

atomic_with(&pool, options, closure) begins the outermost block with an isolation level, PostgreSQL settings, or both:

use rustango::sql::{atomic_with, raw_query_pool, AtomicOptions, Isolation, TxSetting};

let opts = AtomicOptions::new()
    .isolation(Isolation::RepeatableRead)
    .setting(TxSetting::new("app.account_id", "42")?); // PostgreSQL only
let p = pool.clone();
let seen: Vec<(Option<String>,)> = atomic_with(pool, opts, move |_tx| {
    Box::pin(async move {
        raw_query_pool("SELECT current_setting('app.account_id', true)", vec![], &p).await
    })
})
.await?;
  • Levels: Isolation::ReadCommitted, RepeatableRead, Serializable. PostgreSQL begins with BEGIN ISOLATION LEVEL …. MySQL sets the level for that transaction only; the next block on the connection is back to the server default. SQLite accepts any level: it is always serializable. Without a level, the block uses the server default (PostgreSQL: read committed; MySQL: repeatable read).
  • Settings (TxSetting) are PostgreSQL only, for example app.account_id for row security policies. Each is set with set_config(name, value, true) and is gone when the block ends. The value is bound. The name must be dotted (prefix.name) with every part a plain identifier, so search_path or role return ExecError::InvalidTxSetting. On MySQL and SQLite, settings return ExecError::Sql(SqlError::OperatorNotSupportedInDialect) before the block begins.
  • Nested blocks cannot change these: atomic_with with options inside a block on the same pool returns ExecError::InAtomic.

Is this code in a block?

rustango::sql::in_atomic(&pool) is true while the running task is inside a block on pool (or a clone of it), so ORM calls given pool run on its transaction. It is false outside, and for any other pool. It stays true in a block that failed or ended early.

Get or create in a transaction

get_or_create_tx and update_or_create_tx take an open PoolTx. In a block, lock the guard and pass it:

use rustango::sql::get_or_create_tx;

let email = "[email protected]".to_owned();
let (acc, created) = atomic(pool, move |tx| {
    Box::pin(async move {
        let mut g = tx.lock().await?;
        let filter = Account::objects().filter("email", email.clone());
        get_or_create_tx(
            filter,
            move |t| {
                Box::pin(async move {
                    let mut acc = Account { id: Auto::Unset, email, balance: 0 };
                    acc.insert_tx(t).await?;
                    Ok(acc)
                })
            },
            &mut *g,
        )
        .await
    })
})
.await?;
use rustango::sql::update_or_create_tx;

let (acc, created) = atomic(pool, move |tx| {
    Box::pin(async move {
        let mut g = tx.lock().await?;
        update_or_create_tx(
            Account::objects().filter("email", "[email protected]"),
            |t, mut acc: Account| {
                Box::pin(async move {
                    acc.balance += 10;
                    acc.save_tx(t).await?;
                    Ok(acc)
                })
            },
            |t| {
                Box::pin(async move {
                    let mut acc = Account {
                        id: Auto::Unset,
                        email: "[email protected]".into(),
                        balance: 10,
                    };
                    acc.insert_tx(t).await?;
                    Ok(acc)
                })
            },
            &mut *g,
        )
        .await
    })
})
.await?;

Races. The create closure runs in a savepoint. When two transactions create the same row at once, the loser's insert hits the unique constraint: its savepoint rolls back and it reads the winner's row (created == false; update_or_create_tx then updates it). The transaction stays usable. This needs a UNIQUE constraint on the filtered columns. A get_or_create_tx inside another's create closure (a parent row, then the child) works the same way.

Backend and levelThe loser gets
PostgreSQL read committed (default)The winner's row
PostgreSQL repeatable readThe unique violation error
PostgreSQL serializableA serialization failure (40001)
MySQL, MariaDBThe winner's row (re-read with a locking read)
SQLiteNo race: blocks run one at a time

Under repeatable read or serializable on PostgreSQL, retry the whole block.

The pool forms get_or_create and update_or_create behave the same inside a block: their reads join it and the create closure runs in a savepoint. Do not hold the guard across them.

The _on forms

The _on methods take any executor: a &Pool, a held connection (&mut PoolConn from pool.acquire()), or an open transaction (&mut PoolTx from transaction_pool, or &mut *guard in a block). The statement runs on what you pass.

  • fetch_on, insert_on, save_on, delete_on, upsert_on
  • Model::bulk_insert_on, Model::bulk_upsert_on, Model::bulk_insert_or_ignore_on
  • rustango::sql::bulk_update_on
  • rustango::sql::fetch_dates_on, rustango::sql::fetch_datetimes_on
  • rustango::sql::fetch_with_prefetch_on
  • rustango::sql::model_shortcuts::increment_one_on
  • queryset updates and deletes: .update().execute_on(..), .delete_on(..)
use rustango::core::{BulkUpdateQuery, Model as _, SqlValue}; // `Model` brings `Account::SCHEMA` into scope
use rustango::sql::model_shortcuts::increment_one_on;
use rustango::sql::{bulk_update_on, transaction_pool};

let mut tx = transaction_pool(pool).await?;
let mut rows = vec![
    Account { id: Auto::Unset, email: "[email protected]".into(), balance: 0 },
    Account { id: Auto::Unset, email: "[email protected]".into(), balance: 0 },
];
Account::bulk_insert_on(&mut rows, &mut tx).await?;
// MySQL leaves `Auto` ids unset after a bulk insert: read them back.
let ids: Vec<i64> = Account::objects()
    .order_by(&[("id", false)])
    .fetch_on(&mut tx)
    .await?
    .iter()
    .filter_map(|a| a.id.get().copied())
    .collect();
let set_100 = BulkUpdateQuery::new(
    Account::SCHEMA,
    vec!["balance"],
    ids.iter()
        .map(|id| vec![SqlValue::from(*id), SqlValue::from(100_i64)])
        .collect(),
);
bulk_update_on(&mut tx, &set_100).await?;
increment_one_on::<Account, _>("id", SqlValue::from(ids[0]), "balance", 5, &mut tx).await?;
tx.commit().await?; // or drop `tx` to roll it all back

On an audited model the derived _on writes and increment_one_on return ExecError::AuditUnsupported: they cannot write the audit rows. Use the pool forms there.

DDL in a block

  • PostgreSQL and SQLite: DDL runs in the block's transaction and rolls back with it.
  • MySQL: DDL commits the transaction. run_ddl_idempotent (and the ensure_table helpers built on it) refuses inside a block with an sqlx::Error::Protocol whose message starts DDL on MySQL is refused inside an atomic block on its pool. Raw DDL through raw_execute_pool runs, commits the work so far, and the block returns ExecError::AtomicEndedEarly.
  • Any backend: a migration with "atomic": false returns MigrateError::Exec(ExecError::InAtomic { .. }).

Run DDL and migrations before or after the block.

Iterating in a block

Inside a block on the same pool, .iterator(n) runs each chunk on the block's transaction. It sees the block's writes, keeps row locks from select_for_update(), and pages by primary key (WHERE pk > last) instead of OFFSET. Rows the loop moves out of the filter are not skipped:

let p = pool.clone();
atomic(pool, move |_tx| {
    Box::pin(async move {
        let mut it = Account::objects()
            .filter("balance", 0_i64)
            .order_by(&[("id", false)]) // primary key only, or no order
            .iterator(500)?;
        while let Some(chunk) = it.next_chunk(&p).await? {
            for mut acc in chunk {
                acc.balance = 1; // leaves the filter; paging by key still reaches every row
                acc.save(&p).await?;
            }
        }
        Ok(())
    })
})
.await?;

The queryset must be ordered by the primary key (either direction) or not at all, select the primary key, and not be a union / intersection / difference. Otherwise next_chunk returns ExecError::InAtomic.

Cancellation

Dropping a block's future (a tokio::time::timeout, a client that goes away) rolls the block back, and its on_commit callbacks never run:

use std::time::Duration;

let p = pool.clone();
let slow = atomic(pool, move |_tx| {
    Box::pin(async move {
        Account { id: Auto::Unset, email: "[email protected]".into(), balance: 0 }
            .insert(&p)
            .await?;
        tokio::time::sleep(Duration::from_secs(5)).await; // stands in for slow work
        Ok(())
    })
});
// Dropping the block (here on timeout) rolls it back.
assert!(tokio::time::timeout(Duration::from_millis(100), slow).await.is_err());

If a statement was running when the block was dropped, the server does not stop it. The connection stays busy until the server finishes that statement; then the rollback runs and the connection goes back to the pool. Meanwhile a small pool can be short of connections. A dropped nested block is rolled back on the block's next use.

Errors

ErrorMeaning
ExecError::NestedAtomicA statement ran while a TxGuard was held, or two ran at once (join!)
ExecError::AtomicAbortedThe transaction was rolled back after a failed statement or savepoint
ExecError::AtomicEndedEarlyRaw SQL committed or ended the transaction; some writes may be committed
ExecError::InAtomicThe operation is refused inside a block on its pool
ExecError::InvalidTxSettingA TxSetting name is not a dotted prefix.name