How to use a transaction
Short answer: check one connection out of the pool with pool.begin().await, send every
statement of the group through &mut *tx, and finish with tx.commit().await. If the code never
reaches commit — an early ?, a panic, a Transaction that is dropped while still open — sqlx
sends ROLLBACK instead, and the database forgets everything the transaction wrote.
Two rows that must agree
Section titled “Two rows that must agree”The previous page ended with a promise to keep: a real checkout writes more than one row — the order and its payment, the payment needing the id the order just returned — and the two writes should land together or not at all. Half an order in the database is worse than no order. That guarantee is what a transaction is for, and it starts with a second table:
CREATE TABLE orders ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dish_id INT NOT NULL REFERENCES dishes(id), qty INT NOT NULL);
CREATE TABLE payments ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id), cents INT NOT NULL CHECK (cents > 0));The CHECK on cents is where the story bites: the database refuses to store a payment of zero,
whatever the program sending it believes. In a checked-in schema that rule arrives as a migration,
the way the migrations page sets schemas up. The
runnable examples on this page create and empty the two tables themselves first, so the output
comes out the same on every run — treat that block as the page’s reset switch.
begin, commit, and &mut *tx
Section titled “begin, commit, and &mut *tx”Placing an order reads the dish’s price and writes two rows, all inside one transaction:
use sqlx::postgres::PgPoolOptions;use sqlx::PgPool;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/shop") .await .expect("failed to connect to Postgres");
reset(&pool).await;
let mut tx = pool.begin().await.expect("failed to begin transaction");
let (dish_id, qty) = (2, 1); let order = sqlx::query!( "INSERT INTO orders (dish_id, qty) VALUES ($1, $2) RETURNING id", dish_id, qty ) .fetch_one(&mut *tx) .await .expect("insert order failed");
let dish = sqlx::query!("SELECT price_cents FROM dishes WHERE id = $1", dish_id) .fetch_one(&mut *tx) .await .expect("dish not found");
let payment = sqlx::query!( "INSERT INTO payments (order_id, cents) VALUES ($1, $2) RETURNING id", order.id, dish.price_cents * qty ) .fetch_one(&mut *tx) .await .expect("insert payment failed");
tx.commit().await.expect("commit failed");
println!( "order {} paid {} cents (payment {})", order.id, dish.price_cents, payment.id );}
async fn reset(pool: &PgPool) { sqlx::query("DROP TABLE IF EXISTS payments, orders CASCADE") .execute(pool) .await .expect("drop failed"); sqlx::query( "CREATE TABLE orders ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dish_id INT NOT NULL REFERENCES dishes(id), qty INT NOT NULL )", ) .execute(pool) .await .expect("create orders failed"); sqlx::query( "CREATE TABLE payments ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id), cents INT NOT NULL CHECK (cents > 0) )", ) .execute(pool) .await .expect("create payments failed");}order 1 paid 480 cents (payment 1)begin checks a connection out of the pool and hands it back wrapped in a Transaction. From
that point on, the pool is out of the picture: every statement takes &mut *tx — the one
connection the transaction holds — so the INSERT, the price SELECT, and the second INSERT all run
on the same session. That is the difference from the last two pages, where each statement borrowed
whatever connection happened to be free. Here the three statements are one unit on one connection,
and commit closes the unit: it consumes the transaction — the compiler will not let a committed
transaction be used again — and sends COMMIT to the server, making all three statements permanent
at once.
The amount is also computed inside the transaction: the price is read and the payment written in
the same unit, so the code never holds a price that belongs to a read outside the write it feeds.
And the second insert needs order.id, which the first insert just returned — the two rows are
linked by an id that only exists because the first statement ran.
The error path
Section titled “The error path”Now the bug everyone writes once: a discount code reduces the quantity to zero, and the checkout goes ahead anyway.
use sqlx::postgres::PgPoolOptions;use sqlx::PgPool;
#[tokio::main]async fn main() { let pool = PgPoolOptions::new() .connect("postgres://postgres:dev@localhost:5432/shop") .await .expect("failed to connect to Postgres");
reset(&pool).await;
match place_order(&pool, 2, 0).await { Ok(id) => println!("order {}", id), Err(e) => println!("order failed: {e}"), }
let count = sqlx::query!("SELECT count(*) AS n FROM orders") .fetch_one(&pool) .await .expect("count failed");
println!("{} orders in the table", count.n.unwrap_or(0));}
async fn place_order(pool: &PgPool, dish_id: i32, qty: i32) -> Result<i32, sqlx::Error> { let mut tx = pool.begin().await?;
let order = sqlx::query!( "INSERT INTO orders (dish_id, qty) VALUES ($1, $2) RETURNING id", dish_id, qty ) .fetch_one(&mut *tx) .await?;
let dish = sqlx::query!("SELECT price_cents FROM dishes WHERE id = $1", dish_id) .fetch_one(&mut *tx) .await?;
sqlx::query!( "INSERT INTO payments (order_id, cents) VALUES ($1, $2) RETURNING id", order.id, dish.price_cents * qty ) .fetch_one(&mut *tx) .await?;
tx.commit().await?; Ok(order.id)}
async fn reset(pool: &PgPool) { sqlx::query("DROP TABLE IF EXISTS payments, orders CASCADE") .execute(pool) .await .expect("drop failed"); sqlx::query( "CREATE TABLE orders ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dish_id INT NOT NULL REFERENCES dishes(id), qty INT NOT NULL )", ) .execute(pool) .await .expect("create orders failed"); sqlx::query( "CREATE TABLE payments ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id), cents INT NOT NULL CHECK (cents > 0) )", ) .execute(pool) .await .expect("create payments failed");}order failed: error returned from database: new row for relation "payments" violates check constraint "payments_cents_check" at line 21140 orders in the tableFollow the error through the code. The first insert had already succeeded — the order row was in
the table, its id returned and bound into the second statement. The second insert hit the CHECK
and came back as an Err; the ? returned from place_order immediately, and tx.commit()
never ran. When the function returned, the Transaction was dropped while still open — and that
drop is where sqlx keeps its side of the deal. An open transaction that is dropped is rolled back:
sqlx queues a ROLLBACK on the connection it still holds, and the database does the forgetting. The
order row the first insert wrote is gone, and the count at the bottom of the output is the proof.
rollback() exists for rejections the program decides on its own — validation the database never
sees, a business rule checked in Rust. Both commit and rollback consume the transaction, so
the compiler stops a rolled-back transaction from being committed afterwards: one ending per
transaction, chosen either by reaching a line of code or by the drop handler on the way out of a
failed call.
None of the mechanism here belongs to sqlx. BEGIN, COMMIT, and ROLLBACK are SQL, and every driver
in every language has some shape of this same bargain — sqlx’s contribution is that the rollback
is the default, and keeping data requires an explicit commit that the compiler makes sure only
runs when the whole group survived.