Where transactions begin and end in code

One transaction per business operation: everything that must succeed or fail together, and nothing else. Open it as late as possible, close it as early as possible, and keep it in one place, usually the service layer, not scattered across repositories. Never do slow or irreversible things inside it: HTTP calls, sending email, publishing to a broker, or waiting for user input.

psycopg 3: with conn.transaction(): commits on success and rolls back on exception.

Reads that drive a decision belong inside the transaction, at a suitable isolation level.

Publishing events reliably from a transaction is what the transactional outbox pattern is for.

Autocommit mode is fine for single statements; it makes each statement its own transaction.

import psycopg

with psycopg.connect(autocommit=True) as conn:
    conn.execute(
        "CREATE TEMP TABLE orders (id serial PRIMARY KEY, total int)"
    )
    conn.execute(
        "CREATE TEMP TABLE outbox"
        " (id serial PRIMARY KEY, topic text, payload jsonb)"
    )
    try:
        with conn.transaction():
            conn.execute("INSERT INTO orders (total) VALUES (100)")
            conn.execute(
                "INSERT INTO outbox (topic, payload)"
                " VALUES ('order.placed', '{\"id\": 1}')"
            )
            raise RuntimeError("payment check failed")
    except RuntimeError:
        pass
    row = conn.execute(
        "SELECT (SELECT count(*) FROM orders),"
        " (SELECT count(*) FROM outbox)"
    ).fetchone()
    print(row)

Common mistakes

• Opening a transaction per HTTP request by default, middleware style, including slow external calls.
• Committing in the middle of a repository method, so callers can't compose operations.
• Publishing to Kafka before the commit, so consumers can see events for rolled-back data.

Tie boundaries to business operations, keep them short, and push side effects to after commit.

Follow-up questions

Think of your answer first, then open the hint.

1. How do you send an event only if the transaction commits?

   Show hint

   Write it to an outbox table in the same transaction; a relay publishes it.

2. Who owns the transaction in a layered app?

   Show hint

   The use-case or service layer that knows the business operation.