Return HTTP Success After the Database Commit

Article · Interrupted execution

The insert succeeds, so the handler prepares a successful response. Then commit fails. The client has been told that an order exists even though the transaction did not make it durable.

Awaiting the insert is not enough. Some constraints are checked at commit. The database connection can also fail while the application waits for the commit reply. Construct the response after the operation it promises has completed, and keep an unresolved commit outcome distinct from a confirmed database rejection.

The reproduction uses a real Axum request and response path through Tower's in-process service interface, plus PostgreSQL 18 and SQLx 0.8.6. It tests handler ordering and stored state. It does not simulate a TCP connection closing halfway through a response.

Make a statement succeed and commit fail

The fixture creates customers and orders. The order's customer reference is a deferrable foreign key that is initially deferred. PostgreSQL accepts the order insert before checking that reference at commit.

One request names customer 999, which does not exist. The insert reaches the end of its statement. Commit then returns foreign-key violation 23503. This is a controlled database rejection, not an uncertain network outcome.

Here is the exact handler used by the test:

async fn create_order(State(pool): State<PgPool>, Path(id): Path<i32>) -> StatusCode {
    let result = async {
        let mut tx = pool.begin().await?;
        sqlx::query("INSERT INTO orders (id, customer_id) VALUES ($1, $1)")
            .bind(id)
            .execute(&mut *tx)
            .await?;
        tx.commit().await?;
        Ok::<(), sqlx::Error>(())
    }
    .await;
    match result {
        Ok(()) => StatusCode::CREATED,
        Err(error)
            if error.as_database_error().and_then(|e| e.code()).as_deref() == Some("23503") =>
        {
            StatusCode::CONFLICT
        }
        Err(_) => StatusCode::SERVICE_UNAVAILABLE,
    }
}

The function returns 201 Created only after awaiting commit. Its known foreign-key rejection becomes 409 Conflict under this small fixture's API contract. Other errors become 503 Service Unavailable. A production API needs a richer internal outcome model and a documented recovery response; the catch-all here must not be read as proof that every unavailable operation rolled back.

The complete HTTP commit fixture dispatches requests through the router and reads stored rows independently. The invalid request must return 409 and leave zero orders. A valid request must return 201 and leave its order visible.

Put the acknowledgement after the promised boundary

SQLx's transaction API makes commit asynchronous. Creating that future does not finish the operation. Spawning it in the background changes what the response can honestly promise.

For a synchronous order endpoint, the response should follow the durable order. Authorization, stock checks, and related writes belong in the same deliberate transaction. The MySQL checkout example explains that larger business operation. This new test uses PostgreSQL's deferred foreign key to expose the response-ordering mistake; it does not assert identical constraint timing in MySQL.

PostgreSQL documents deferred constraint timing. Checking one example with an immediate constraint would miss this particular failure point. Choose a failure that reaches the boundary you want to inspect.

Do not expose raw database diagnostics to callers. Keep the detailed category in controlled internal diagnostics, and return the API's stable error representation. A foreign-key violation can have several business meanings; the example's 409 mapping is a choice for this test, not a universal status rule.

A lost response does not undo a committed order

The test also creates another valid order, receives the response object, and deliberately discards it. A separate query still finds the order. Discarding a response is an application-level model of an unobserved result. It does not prove browser, proxy, or TCP behavior.

The important boundary is visible: response consumption and database commit are separate events. A caller can fail to observe a result that is already stored. An automatic retry with a fresh identity can therefore create another business operation.

Use a stable operation identity and a documented lookup or same-key recovery contract where the product needs one. The idempotency guide explains that API decision. The lost PostgreSQL commit reply investigation tests a different boundary with an actual controlled database-protocol relay.

This handler intentionally omits authentication, production key storage, and a complete operation-status endpoint. Its integer IDs are fixture identities. Add those product responsibilities before using the shape in a real checkout.

Returning early can be a valid different promise

An asynchronous endpoint may return before the final order work finishes. Persist its acceptance record first. Return a job identity and expose progress and a final result. A task stored only in the request process is not durable acceptance across a restart.

HTTP 202 Accepted describes processing that remains incomplete. A 202 response still needs a useful product contract. Explain whether the job was accepted, started, rejected, completed, or remains unresolved.

Test the response and the stored state together. Force a rejection at commit, a successful commit, and a result that the caller does not consume. Those cases answer different questions. The response becomes trustworthy when it describes the strongest fact the system has actually established.