🎓 A1Learner
🔍

Rust SQLite Transactions

A transaction is a group of changes that lands all together or not at all. Half a transfer is worse than none.

Start a transaction

transaction() borrows the connection mutably. Nothing else can use the connection until the transaction ends.

let mut conn = Connection::open_in_memory()?;
let tx = conn.transaction()?;

Do the work on tx

Every statement runs on tx, not on conn. Both updates belong to the same transaction.

tx.execute(
    "UPDATE account SET balance = balance - 30 WHERE name = ?1",
    params!["Ada"],
)?;
tx.execute(
    "UPDATE account SET balance = balance + 30 WHERE name = ?1",
    params!["Grace"],
)?;

Commit makes it real

Before commit, the changes live only inside the transaction. commit publishes them all at once.

tx.commit()?;

No commit means rollback

This transaction takes 30 from Ada. Then it drops without commit. Ada keeps her money.

let tx = conn.transaction().unwrap();
tx.execute(
    "UPDATE account SET balance = balance - 30 WHERE name = 'Ada'",
    (),
).unwrap();
drop(tx);
let balance: i64 = conn.query_row(
    "SELECT balance FROM account WHERE name = 'Ada'",
    (),
    |row| row.get(0),
).unwrap();
println!("balance = {balance}");

A savepoint guards one part

savepoint starts a smaller guard inside the transaction. Its commit folds the part into the outer transaction.

let sp = tx.savepoint()?;
sp.execute(
    "INSERT INTO log (note) VALUES (?1)",
    params!["transfer sent"],
)?;
sp.commit()?;

Example

// Cargo.toml: rusqlite = { version = "0.40", features = ["bundled"] }
use rusqlite::{params, Connection, Result};

fn balance(conn: &Connection, name: &str) -> Result<i64> {
    conn.query_row(
        "SELECT balance FROM account WHERE name = ?1",
        params![name],
        |row| row.get(0),
    )
}

fn transfer(conn: &mut Connection, from: &str, to: &str, amount: i64) -> Result<()> {
    let tx = conn.transaction()?;
    tx.execute(
        "UPDATE account SET balance = balance - ?1 WHERE name = ?2",
        params![amount, from],
    )?;
    tx.execute(
        "UPDATE account SET balance = balance + ?1 WHERE name = ?2",
        params![amount, to],
    )?;
    let _: i64 = tx.query_row(
        "SELECT balance FROM account WHERE name = ?1",
        params![to],
        |row| row.get(0),
    )?;
    tx.commit()
}

fn main() -> Result<()> {
    let mut conn = Connection::open_in_memory()?;
    conn.execute(
        "CREATE TABLE account (name TEXT PRIMARY KEY, balance INTEGER NOT NULL)",
        (),
    )?;
    conn.execute("INSERT INTO account (name, balance) VALUES (?1, ?2)", params!["Ada", 100])?;
    conn.execute("INSERT INTO account (name, balance) VALUES (?1, ?2)", params!["Grace", 40])?;
    transfer(&mut conn, "Ada", "Grace", 30)?;
    println!("Ada = {}", balance(&conn, "Ada")?);
    println!("Grace = {}", balance(&conn, "Grace")?);
    let failed = transfer(&mut conn, "Ada", "Nobody", 10);
    println!("second transfer ok = {}", failed.is_ok());
    println!("Ada still = {}", balance(&conn, "Ada")?);
    Ok(())
}

Key points

  • Use conn.transaction() to start a transaction.
  • It needs a mutable connection.
  • Your function signature shows it as &mut Connection.
  • The transaction value works like a connection.
  • execute, prepare and query_row all exist on it.
  • Use tx.commit() to make the changes permanent.
  • commit consumes the transaction value.
  • Drop a transaction without commit and its changes roll back.
  • An early return with ? drops it in exactly this way.
  • Use a savepoint for one part of a transaction.
  • A savepoint can roll back its own part.
  • The rest of the transaction survives.
  • SQLite writes the file once at commit.
  • It does not write once per row.
  • A transaction lands as one whole change or not at all.

In this example, two accounts start with 100 and 40. The transfer function opens a transaction. It takes 30 from Ada. It adds 30 to Grace. A read inside the same transaction checks that Grace exists. commit then makes both updates permanent. The balances print as 70 and 70. The second transfer names a receiver who does not exist. The read fails there. The ? operator leaves the function early. The transaction drops without commit. Ada keeps her 70. The subtraction rolled back with everything else.

Note

A transaction also makes bulk inserts far faster.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Rust Playground

Exercise

A transaction value drops before commit ever runs. What happens to its changes?