SQL: Durable State · 25 min · free lesson

Commit Related Changes Atomically

Roll back a failed multi-write operation.

Core idea

All related writes belong to one transaction with an observable failure path.

Diagram for Commit Related Changes Atomically: a Rust code example flows to the lesson rule and the key observation.
This Rust reading prints a SQL statement. The independent compiler lab executes it against real SQLite.

Understand it

A transfer debits one account and credits another. If the debit succeeds while the credit fails, the database has lost value. Enclose both writes in one explicit transaction. Constrain balances to remain nonnegative and make a rejected debit fail before any durable credit occurs.

The exercise transfers an integer amount from account 1 to account 2. A zero transfer leaves balances unchanged. A transfer larger than the source balance triggers the database CHECK and must leave both accounts unchanged. The local SQL host rolls back any open transaction when a statement fails, and refuses a script that ends with an open transaction. SQLx on desktop normally uses a transaction value with explicit commit and rollback behavior. Concurrency isolation, lock timeouts and retry policy need separate tests; this single-connection lab proves only the finite atomicity cases it executes.

The route project is Task Ledger: first keep a reproducible source history, then expose its data through a CLI and HTTP boundary, persist it in SQL, diagnose failures and prepare a release. This lesson owns one contract in that project. Run the worked example before changing the starter and explain which input makes the tempting shortcut fail.

Terminal practice: Run sqlite3 ledger.db .read migrations/accounts.sql. Execute a valid BEGIN..COMMIT transfer and an overdraw script, then SELECT both balances.

The compiler lab is independent of the reading question. Edit solution.rs; the app supplies a separate caller with normal, empty and adversarial inputs. A successful unchanged demonstration does not prove your implementation. After the lab, apply the same contract to the route companion project. Its README separates the on-device adapter from the desktop Cargo/TCP profile.

Read the code

pub fn transfer_sql(amount: u32) -> String {
    format!(
        "BEGIN IMMEDIATE; UPDATE accounts SET balance=balance-{amount} WHERE id=1; UPDATE accounts SET balance=balance+{amount} WHERE id=2; COMMIT;"
    )
}

fn main() {
    println!("{}", transfer_sql(10));
}

This Rust reading prints a SQL statement. The independent compiler lab executes it against real SQLite.

Practice starter

pub fn transfer_sql(amount: u32) -> String {
    todo!("implement the contract")
}

Open local example in Playground ↗

Try it

Return a complete BEGIN..COMMIT SQLite script transferring amount from account 1 to 2. accounts(id,balance) enforces balance>=0. Debit and credit must be atomic, including overdraw failure. Amount is a trusted u32 numeric value. Keep the public signature. Open the compiler lab and edit solution.rs; do not edit the private verification caller.

What success looks like: The separate caller accepts the changed solution and rejects unchanged TODO, a wrong implementation and a printed success message.

Check your understanding

What should remain after a rejected overdraw?

Reveal the answer
  1. The credit only
  2. Both original balances
  3. The debit only

Atomicity means the failed transfer cannot leave one half committed.

Go deeper

Common misconception: The debit only

All related writes belong to one transaction with an observable failure path.

  1. Identify the boundary: Roll back a failed multi-write operation. Write down its accepted inputs and observable output.
  2. Exercise the failure: Atomicity means the failed transfer cannot leave one half committed.
  3. Check the independent caller: Return a complete BEGIN..COMMIT SQLite script transferring amount from account 1 to 2. accounts(id,balance) enforces balance>=0. Debit and credit must be atomic, including overdraw failure. Amount is a trusted u32 numeric value.

Transfer challenge: Apply this contract to Task Ledger, introduce the failure described in the lesson, and show the failing check before fixing it.

Connections

  • Bind Values Instead of SQL Syntax: Keep untrusted text in a parameter slot
  • Add Context Without Leaking Secrets: Redact sensitive diagnostic fields