SQLite Transaction

---

The default behavior of SQLite is that statement that change the database (e.g. INSERT, UPDATE, DELETE) only prepare the action, but the user still needs to issue COMMIT command to have the statement take effect.

The default connection of the Rust SQLite client uses autocommit, meaning that using this crate we don't need issue the COMMITcommend. This is easier, however it raises another issue.

What if we would like to have 2 or more statements to be atomit. That is either all of them succeed to all of them fail. For example if we implement a bank a money transfer from user Mary to use Jane would need 2 statement: deduct the money from Mary and add the money to Jane. What if there is some error after the deduction (e.g. a panic, or someone reboots the computer, or there is a power failure?) Then the system lost the money that was deducted from Mary.

The solution is to use transactions. A transaction starts with the BEGIN statement and ends with a COMMIT statement. In between the two you can have any number of actions that would change the database. If the code does not reach the COMMIT statement (e.g. because of the said panic, then none of the actions will take place. The money will stay in its original location.

The BEGIN statement turns off the autocommit mode and the COMMIT turns it on again.

In this example we tried to imitate the issue.

examples/sqlite/transaction/Cargo.toml

[package]
name = "transaction"
version = "0.1.0"
edition = "2021"

[dependencies]
clap = { version = "4.5.16", features = ["derive"] }
sqlite = "0.36.1"

examples/sqlite/transaction/src/main.rs

use sqlite::{Connection, State};

use clap::Parser;
use clap::ValueEnum;

#[derive(Debug, Clone, Copy, ValueEnum)]
enum Action {
    Plain,
    Panic,
    Transaction,
    Show,
}

#[derive(Parser, Debug)]
struct Cli {
    action: Action,
}

fn main() {
    let args = Cli::parse();

    let filename = "bank.db";
    let exists = std::path::PathBuf::from(filename).exists();
    let connection = sqlite::open(filename).unwrap();

    if !exists {
        setup_bank(&connection);
    }

    match args.action {
        Action::Plain => transfer(&connection, "Mary", "Jane", 100, false, false),
        Action::Panic => transfer(&connection, "Mary", "Jane", 100, true, false),
        Action::Transaction => transfer(&connection, "Mary", "Jane", 100, true, true),
        Action::Show => (),
    };
    show(&connection);
    std::fs::remove_file(filename).unwrap();
}

fn setup_bank(connection: &Connection) {
    connection
        .execute(
            r#"
        CREATE TABLE bank (
            name TEXT PRIMARY KEY,
            balance INTEGER NOT NULL
        );
    "#,
        )
        .unwrap();
    connection
        .execute("INSERT INTO bank (name, balance) VALUES ('Jane', 0);")
        .unwrap();
    connection
        .execute("INSERT INTO bank (name, balance) VALUES ('Mary', 1000);")
        .unwrap();
    connection
        .execute("INSERT INTO bank (name, balance) VALUES ('Ann', 1000);")
        .unwrap();
}

fn transfer(
    connection: &Connection,
    from: &str,
    to: &str,
    amount: i64,
    fail: bool,
    transaction: bool,
) {
    let sql = r#"UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = :name) + :amount WHERE name = :name;"#;

    if transaction {
        println!("BEGIN");
        connection.prepare("BEGIN").unwrap().next().unwrap();
    }
    let mut statement = connection.prepare(sql).unwrap();
    statement.bind((":name", from)).unwrap();
    statement.bind((":amount", -amount)).unwrap();
    statement.next().unwrap();
    if fail {
        panic!("Problem");
    }

    statement.reset().unwrap();
    statement.bind((":name", to)).unwrap();
    statement.bind((":amount", amount)).unwrap();
    statement.next().unwrap();

    if transaction {
        println!("COMMIT");
        connection.prepare("COMMIT").unwrap().next().unwrap();
    }
}

fn show(connection: &Connection) {
    let mut statement = connection.prepare("SELECT * FROM bank;").unwrap();
    while let Ok(State::Row) = statement.next() {
        let name = statement.read::<String, _>("name").unwrap();
        let balance = statement.read::<i64, _>("balance").unwrap();
        println!("{name:5}: {:>4}", balance);
    }

    let mut statement = connection
        .prepare("SELECT SUM(balance) AS total FROM bank;")
        .unwrap();
    if let Ok(State::Row) = statement.next() {
        let total = statement.read::<i64, _>("total").unwrap();
        println!("Total: {:>4}", total);
    }
    //
    println!("-----");
}

$ cargo run -q show
Jane :    0
Mary : 1000
Ann  : 1000
Total: 2000
-----

$ cargo run -q plain
Jane :  100
Mary :  900
Ann  : 1000
Total: 2000
-----

$ cargo run -q panic
thread 'main' panicked at src/main.rs:81:9:
Problem
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

$ cargo run -q show
Jane :    0
Mary :  900
Ann  : 1000
Total: 1900
-----

$ cargo run -q transaction
BEGIN
thread 'main' panicked at src/main.rs:81:9:
Problem
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

$ cargo run -q show
Jane :    0
Mary : 1000
Ann  : 1000
Total: 2000
-----