Imagine completing a workout requires updating its status, storing summary metrics, and recording an achievement candidate. If the second write fails, keeping only the first may leave the product in an impossible state.
The transaction should surround the business operation, which usually makes the service its owner.
async def complete_workout(
pool: asyncpg.Pool,
user_id: str,
session_id: UUID,
) -> WorkoutResponse:
async with pool.acquire() as connection:
async with connection.transaction():
session = await repository.lock_session(
connection,
user_id,
session_id,
)
ensure_can_complete(session)
await repository.mark_completed(connection, session_id)
await repository.save_summary(connection, calculate_summary(session))
return await get_workout(pool, user_id, session_id)
The service understands which writes form one invariant. The repository understands how each write is expressed in SQL.
Why the router should not own it#
HTTP is only one entry point. A background worker may perform the same operation. Putting transactions in routes couples consistency to transport and makes the workflow harder to reuse.
When repositories use local transactions#
A repository operation may need several SQL statements to implement one persistence primitive-for example, atomically replacing the children of a template. A private transaction can be appropriate if callers always see it as one operation.
Do not hide a broad business workflow inside a repository named save.
External calls need another strategy#
A PostgreSQL transaction cannot roll back an email or provider API call. Avoid holding database locks while waiting on slow external services.
Commit durable state and an outbox record together, then deliver the external effect asynchronously. If immediate delivery is required, define compensation and retry behavior explicitly.
Pass the connection deliberately#
Repository functions participating in one transaction must use the same connection. If they independently acquire from the pool, the transaction does not include their work.
Transaction boundaries should match invariants. Start from the question “which state changes must succeed together?” and place ownership where that complete use case is understood.