Transactions¶

A transaction groups several statements so that they either all take effect or none does. This page explains how turso-orm binds a transaction to a connection, what the four modes mean, how nesting works through savepoints, what the closure form does for you, and what happens when a transaction is dropped unfinished.

Why a transaction pins a connection¶

In SQLite a transaction is a property of a connection: BEGIN on one connection says nothing about another. A transaction handle must therefore keep the connection it started on and send every statement through it; a statement that went to a second connection would see a different snapshot, or block on the first connection's lock.

That is what db.begin() does. It borrows one connection from the pool, issues BEGIN, and returns a Transaction that holds that connection until commit or rollback. The Transaction implements the same ConnectionTrait as Database, so every query builder and every active model method accepts &txn where it accepted &db:

let txn = db.begin().await?;
post::ActiveModel { /* ... */ }.insert(&txn).await?;
post::Entity::update_many()
    .col(post::Column::Views, 0)
    .exec(&txn)
    .await?;
txn.commit().await?;

Until commit, nothing written through txn is visible to statements that go through db, because those run on other connections.

Modes¶

db.begin() opens a DEFERRED transaction. db.begin_with_mode(mode) picks another one:

When the database is locked, BEGIN does not fail at once: the driver retries with a backoff for the duration of busy_timeout, five seconds by default. Only when that budget is exhausted does the error reach you, and e.is_busy() identifies it.

Over HTTP (ConnectOptions::remote), a transaction is a server-side session that spans several requests. The four modes are sent as they are; the busy timeout and MVCC are properties of a local engine and do not apply.

Nesting with savepoints¶

Calling begin() on a transaction, rather than on the database, opens a nested transaction. SQLite has no nested BEGIN; the nested handle is a SAVEPOINT on the same connection. Rolling it back undoes only the statements made since the savepoint; committing it releases the savepoint and leaves the outer transaction open, still uncommitted:

let txn = db.begin().await?;
tag::ActiveModel { /* ... */ }.insert(&txn).await?;     // kept
{
    let sp = txn.begin().await?;                         // SAVEPOINT sp1
    tag::ActiveModel { /* ... */ }.insert(&sp).await?;   // undone below
    sp.rollback().await?;                                // ROLLBACK TO sp1
}
txn.commit().await?;                                     // commits the first insert

This is how a service can attempt an optional step inside a larger unit of work and discard just that step on failure. Savepoints nest to any depth; Transaction::depth() tells how deep a handle is.

The closure form¶

Most transactions follow the same script: begin, run some statements, commit if everything succeeded, roll back otherwise. db.transaction does that script for you:

/// A multi-row insert with `RETURNING`, a bulk update and a closure transaction.
async fn bulk_and_transaction(db: &Database) -> Result<(), DbErr> {
    let bakery = Bakery::find().one(db).await?.expect("seeded above");
    let cakes = Cake::insert_many([
        cake::ActiveModel {
            name: Set("Chocolate Forest".to_owned()),
            price: Set(8.5),
            gluten_free: Set(false),
            bakery_id: Set(bakery.id),
            ..Default::default()
        },
        cake::ActiveModel {
            name: Set("Lemon Drizzle".to_owned()),
            price: Set(6.0),
            gluten_free: Set(true),
            bakery_id: Set(bakery.id),
            ..Default::default()
        },
    ])
    .exec_with_returning(db)
    .await?;
    println!("inserted {} cakes", cakes.len());

    let fruits = [
        ("Blueberry", Some(1)),
        ("Raspberry", Some(1)),
        ("Strawberry", Some(2)),
        ("Apple", None),
        ("Cherry", None),
    ];
    let inserted = Fruit::insert_many(fruits.map(|(name, cake_id)| fruit::ActiveModel {
        name: Set(name.to_owned()),
        cake_id: Set(cake_id),
        ..Default::default()
    }))
    .exec(db)
    .await?;
    println!("inserted {inserted} fruits");

    let bumped = Cake::update_many()
        .col(cake::Column::Price, 7.0)
        .filter(cake::Column::GlutenFree.eq(true))
        .exec(db)
        .await?;
    println!("bulk update touched {} rows", bumped.rows_affected);

    // The closure form commits on `Ok` and rolls back on `Err`. Here the
    // insert is undone on purpose, so the fruit count is unchanged after.
    let attempted: Result<(), DbErr> = db
        .transaction(|txn| {
            Box::pin(async move {
                fruit::ActiveModel {
                    name: Set("Ghost".to_owned()),
                    ..Default::default()
                }
                .insert(txn)
                .await?;
                Err(DbErr::Custom("abort on purpose".to_owned()))
            })
        })
        .await;
    println!("transaction result: {attempted:?}");
    println!("fruits after rollback: {}", Fruit::find().count(db).await?);
    Ok(())
}

The closure receives &Transaction and returns a pinned, boxed future, hence the Box::pin(async move { ... }) wrapper. If the future resolves to Ok(value), the transaction is committed and value is returned; if it resolves to Err(e), the transaction is rolled back and e is returned. The example above returns an error on purpose to show that the insert made inside the closure is gone afterwards.

The error type is yours to choose, as long as it can be built from the driver's error with From; DbErr qualifies, and so does an application error enum that wraps it. That lets business validation inside the closure abort the transaction with its own error.

What happens on drop¶

Drop cannot await, so a transaction dropped without commit or rollback cannot clean up synchronously. turso-orm makes that safe in two ways:

• A dropped top-level transaction discards its connection instead of returning it to the pool. The engine rolls back whatever that connection held when it closes, and the pool opens a fresh connection on the next acquire. Nothing leaks into another borrower.
• A dropped nested transaction records its depth. The parent runs ROLLBACK TO SAVEPOINT before its next statement, so the abandoned work is undone before anything else happens on that connection.

These are safety nets, not the API: call commit or rollback explicitly, or use the closure form, so that the outcome is decided where the reader can see it.

Reading under a transaction¶

Reads through &txn see the transaction's own writes and a consistent snapshot of everything else. Reads through &db while a transaction is open run on other connections and see the committed state only. When a request handler must read what it just wrote, run both through the same transaction.