Skip to content

Making Database Transactions Cancellation-Safe

Request handlers get cancelled — clients disconnect, timeouts fire, servers shut down — and some of them are in the middle of a database transaction when it happens. The good news, tested with asyncpg 0.31 and SQLAlchemy 2.1 against PostgreSQL 17: a task cancelled between two statements of a transfer left the balances unchanged (100 and 0), with no session left "idle in transaction"; a task cancelled during a 5-second query returned in 7 ms and the server stopped the query; 20 double-cancellations during SQLAlchemy cleanup left no stuck transactions. The dangerous moment is the commit. With a commit slowed to 0.5 s by a deferred trigger, cancelling the task during the commit rolled the transaction back; wrapping the commit in asyncio.shield made it complete — yet the caller still saw CancelledError, believing the order failed when it had succeeded. This guide makes transactions safe at every point a cancellation can land, including that one.

Prerequisites

1. Rely on the transaction context manager for rollback

A cancellation arrives as CancelledError at whatever await the task is suspended on. Inside async with conn.transaction(), that exception unwinds through the context manager, which rolls back:

async def transfer(pool, src: int, dst: int, amount: int) -> None:
    async with pool.acquire() as conn, conn.transaction():
        await conn.execute("update acct set bal = bal - $1 where id = $2", amount, src)
        await notify_audit(src, amount)                  # cancelled here
        await conn.execute("update acct set bal = bal + $1 where id = $2", amount, dst)

Tested: cancelled after the first update, the balances stayed at 100 and 0, and pg_stat_activity showed no session idle in a transaction. The pool's release also resets the connection, so the next user does not inherit a half-finished transaction. The same held for a raw connection outside a pool: after the cancellation, is_in_transaction() was False. Write every multi-statement change inside a transaction block and the database stays consistent no matter where a cancellation lands before the commit.

Verify: cancel transfers at random points in a test loop; the sum of balances never changes and no session is left idle in a transaction.

2. Know that cancelling a query stops it on the server

When a task is cancelled while a query is running, asyncpg sends PostgreSQL a cancel request for that query, so the server stops working too:

async def report(pool):
    async with pool.acquire() as conn:
        return await conn.fetch(EXPENSIVE_QUERY)      # cancellation interrupts this on the server

Tested with select pg_sleep(5): the cancelled await returned in 7 ms, and 0.2 s later pg_stat_activity showed no active pg_sleep — the server-side work had ended. That makes cancellation a real load-shedding tool: a client that disconnects from an expensive report stops the report, as in detecting client disconnects in ASGI handlers. psycopg 3 behaves the same way for async connections; drivers that do not send cancel requests leave the query running until it finishes.

Verify: cancel a long query in a test and confirm in pg_stat_activity that it disappears.

Where the cancellation landed, and what happened A grid of 4 rows by 3 columns. Where the cancellation landed, and what happened cancelled during database result caller sees between statements rolled back, no stuck session CancelledError a long query server stopped it CancelledError (7 ms) COMMIT, unshielded rolled back CancelledError COMMIT, shielded committed CancelledError (misleading) Only the commit is ambiguous; everything before it rolls back cleanly.

3. Decide what a cancelled commit should mean

The commit is the one point where the outcome is decided, and a cancellation there leaves two consistent but different results. Tested with a commit slowed to 0.5 s by a deferred constraint trigger and a cancellation 0.2 s in:

# Unshielded: asyncpg cancels the COMMIT -> transaction rolled back, caller gets CancelledError
await tx.commit()

# Shielded: COMMIT finishes -> row committed, but the caller STILL gets CancelledError
await asyncio.shield(tx.commit())

Both leave the database consistent. The shielded version is usually what you want for business operations — once the decision to commit is made, finish it — but it means CancelledError no longer implies "nothing happened". Code that catches the cancellation and reports "order failed" is now wrong in exactly the cases where the order succeeded. And in either version, if the connection drops at the wrong moment, the client cannot know whether the server committed before the failure.

Verify: a test that cancels during a slowed commit checks the database afterwards and asserts the outcome your code reports matches it.

4. Make the outcome discoverable with an idempotency key

The robust answer to "did it commit?" is to make the operation idempotent and checkable. Give each operation a key, store it in the same transaction, and look it up when the outcome is unknown:

async def place_order(pool, order: OrderIn, key: str) -> int:
    async with pool.acquire() as conn:
        existing = await conn.fetchval("select order_id from idempotency where key = $1", key)
        if existing is not None:
            return existing                                   # already done: return the same result
        async with conn.transaction():
            order_id = await conn.fetchval("insert into orders(...) values (...) returning id", ...)
            await conn.execute("insert into idempotency(key, order_id) values ($1, $2)", key, order_id)
            # the commit at block exit makes both visible together
        return order_id


async def handler(request):
    key = request.headers["Idempotency-Key"]
    try:
        return await asyncio.shield(place_order(pool, parse(request), key))
    except asyncio.CancelledError:
        # outcome unknown to *this* caller; a retry with the same key will find out
        raise

A client that retries with the same key after a cancellation, timeout or network error gets the original result if the first attempt committed, and a new attempt if it did not — never a duplicate. A unique constraint on the key column makes concurrent retries safe too. This is the same pattern as in making background jobs idempotent.

Verify: cancel during commit, retry with the same key, and the order exists exactly once.

An order that survives cancellation at any point A flow of 5 stages. An order that survives cancellation at any point check key existing -> return it transaction order + key rows shielded commit finishes once started caller cancelled? outcome unknown here retry same key exactly one order Idempotency turns an ambiguous commit into a question the next attempt can answer.

5. Keep side effects outside the transaction ordered

Cancellation-safety also depends on what happens around the transaction. Effects outside the database — sending email, publishing a message, calling a payment API — cannot be rolled back:

async def complete_order(pool, producer, order_id: int) -> None:
    async with pool.acquire() as conn, conn.transaction():
        await conn.execute("update orders set state = 'paid' where id = $1", order_id)
        await conn.execute(
            "insert into outbox(topic, payload) values ('order.paid', $1)", json.dumps({"id": order_id}))
    # a separate relay publishes outbox rows after commit, retrying until delivered

Writing the event to an outbox table inside the transaction makes it commit or roll back with the state change; a relay publishes it afterwards, as in implementing the transactional outbox pattern in asyncio. Publishing directly inside the transaction is wrong in one direction (the message goes out, then a cancellation rolls back the data), and publishing directly after the commit is wrong in the other (the data commits, then a cancellation skips the message).

Verify: cancel at random points around order completion; every paid order has exactly one outbox event, and no event exists for an unpaid order.

How should this database write handle cancellation? A decision on What does the operation do with 4 outcomes. How should this database write handle cancellation? What does the operation do? several statements transaction block rollback on cancel must finish once committing shield commit + idempotency key retry learns outcome external side effects outbox in same transaction relay publishes long read query let it cancel server stops too Before the commit, cancellation is safe; at the commit, design for ambiguity.

Verification

Transactions are cancellation-safe when:

  • Every multi-statement change runs in a transaction block, and cancellations leave no stuck sessions.
  • Long queries are cancelled on the server when their task is cancelled.
  • Commits that must finish are shielded, and callers do not treat CancelledError as "nothing happened".
  • Idempotency keys and outboxes make outcomes discoverable and side effects consistent.

Diagnostic Hook: monitor sessions in idle in transaction state and their age, and count CancelledErrors during commit. Old idle-in-transaction sessions mean a code path holds a transaction across non-database awaits; cancellations during commit followed by duplicate submissions mean clients retry without idempotency keys.

Pitfalls & edge cases

  • Treating CancelledError as failure after a shielded commit. Tested: the row was committed.
  • Unshielded commits for operations that must complete. Tested: a cancellation rolled the commit back.
  • External side effects inside or after the transaction. Use an outbox.
  • Holding a transaction open across slow non-database awaits. It widens every window.

Frequently Asked Questions

What happens to a database transaction when an asyncio task is cancelled?

Inside async with conn.transaction(), the CancelledError unwinds the block and the transaction rolls back. In testing with asyncpg, balances were unchanged and no session was left idle in a transaction.

Does cancelling an asyncpg query stop it on the PostgreSQL server?

Yes. asyncpg sends a cancel request; a cancelled pg_sleep(5) disappeared from pg_stat_activity in testing, and the await returned in 7 ms.

Should I shield the commit with asyncio.shield?

For operations that must finish once committing, yes, but then CancelledError no longer means nothing happened: in testing the shielded commit completed while the caller saw CancelledError. Pair it with an idempotency key.

How do I know whether a cancelled commit succeeded?

Store an idempotency key in the same transaction and look it up on retry; the retry returns the original result if the first attempt committed.