Transactions Basics

Use transactions to keep multi-step database changes consistent, but keep transaction boundaries small to avoid lock contention. Never do slow or uncertain work inside a transaction; do the minimum DB work and commit fast.

On this page

Why transactions are a production tool

Transactions are not a feature you add everywhere. They are a tool you use when you need atomicity: either all steps succeed or none do. In production, transactions also create locks and hold resources. The production skill is choosing correct boundaries so you get consistency without turning your database into a bottleneck.

Production goals at this level

Atomicity: multi-step changes either fully apply or roll back.
Small boundaries: keep transactions short to reduce lock time.
Clear error mapping: failures are logged with context and returned as safe API errors.
Predictable behavior: avoid long waits and deadlocks by design, not by luck.

When you need a transaction

Use a transaction when correctness depends on multiple writes being consistent:

Create an order and order items together.
Reserve inventory and record reservation.
Insert a row and update a related aggregate counter.

If a single statement is enough, you often do not need a transaction.

Transaction boundaries: what not to do inside

In production, the most common transaction mistake is doing slow or uncertain work while locks are held. Avoid these inside transactions:

Network calls (HTTP requests, calling other services).
Long computations or heavy data processing.
Waiting on user input or external queues.
Logging that blocks (usually rare, but do not do heavy formatting or I/O).

Keep the transaction focused on database statements only. Do everything else before or after.

SQLx transaction basics

SQLx supports explicit transactions. The core pattern is: begin, execute statements using the transaction handle, commit. If an error happens, return early and the transaction will roll back when dropped.

use sqlx::mysql::MySqlPool;

pub async fn create_user(pool: &MySqlPool, name: &str, email: &str) -> Result<i64, sqlx::Error> {
    let mut tx = pool.begin().await?;

    // Step 1: insert the user
    let result = sqlx::query(
        "INSERT INTO users (name, email) VALUES (?, ?)"
    )
    .bind(name)
    .bind(email)
    .execute(&mut *tx)
    .await?;

    let user_id = result.last_insert_id() as i64;

    // Step 2: optional related write (example)
    sqlx::query(
        "INSERT INTO user_audit (user_id, action) VALUES (?, ?)"
    )
    .bind(user_id)
    .bind("created")
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(user_id)
}

Rollback behavior: be explicit in your mental model

If any awaited statement returns an error, return it and let the transaction roll back. In production, the important point is that your code should not continue after a failed DB statement inside a transaction.

pub async fn do_two_writes(pool: &sqlx::MySqlPool) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;

    sqlx::query("UPDATE accounts SET balance = balance - 10 WHERE id = ?")
        .bind(1_i64)
        .execute(&mut *tx)
        .await?;

    // If this fails, the function returns Err and tx is rolled back.
    sqlx::query("UPDATE accounts SET balance = balance + 10 WHERE id = ?")
        .bind(2_i64)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

Lock time and latency: the hidden cost

Transactions can increase lock time. Lock time increases queueing. Queueing increases latency. The simplest production habit is: keep the transaction short and avoid extra round-trips. If you see increased latency under load, long transactions are a common suspect.

Idempotency mindset

Even with transactions, clients may retry requests (timeouts, network flakiness). Your API should tolerate retries where possible. A simple pattern is using a unique key for a request or operation so repeated attempts do not create duplicates.

At this level, the concept is enough: do not assume requests are executed exactly once.

Error mapping: safe response, rich logs

When a transaction fails, log the internal error with request id and operation name, but return a safe error shape to the client. This keeps the service debuggable without leaking database details.

pub async fn create_user_safe(pool: &sqlx::MySqlPool, name: &str, email: &str, request_id: &str)
    -> Result<i64, crate::errors::ApiError>
{
    create_user(pool, name, email).await.map_err(|e| {
        tracing::warn!(request_id = %request_id, op = "create_user", err = ?e, "transaction failed");
        crate::errors::ApiError::Internal { context: "db transaction failed".to_string() }
    })
}

Operational checklist

Transactions only wrap necessary DB statements.
No network calls inside transactions.
Transaction failures are logged with request id and operation label.
Client-facing errors remain stable and safe.

What comes next

Even with good pooling and short transactions, slow queries can still pin your service. Next, we will apply query timeouts and define how to fail fast when the database is slow.

← Migrations Basics

Query Timeouts →