Transactions & Connections
Manage database connections and understand when transactions, jobs, and cache operations take effect.
Connection scope
ConnectionManager lives in the coroutine context. It borrows a PostgreSQL connection lazily when SQL first runs. Outside a transaction, a statement can release the physical connection; the manager can borrow another for a later statement.
A transaction uses one connection until commit or rollback. Execute SQL sequentially within that scope. Do not launch sibling coroutines to run queries on the same manager.
Use a transaction for a related set of writes
bosca.db.transaction { } commits when the block returns normally. If the block throws an exception, it rolls back and rethrows. The example below assumes an injected CategoryRepository and an existing connection context.
Transaction begin, commit, and rollback run in a NonCancellable context so cleanup can finish during coroutine cancellation.
Nested transactions
A nested transaction { } creates a SQL savepoint. Inner success completes that nested scope without committing the outer transaction. Inner failure rolls back to the savepoint. If the exception propagates out of the outer block, the outer transaction also rolls back.
GraphQL and HTTP requests
Suspended GraphQL field resolvers and batch loaders establish their own connection-manager scopes. A request-level ConnectionLimiter defaults to five concurrent connections. A mutation's transaction covers that resolver's work; it does not span the entire GraphQL document.
HTTP Route, APIRoute, and SSERoute handlers establish a connection manager and request cache around their execution and release the connection afterward.
Side effects and commit timing
- Jobs: NATS and Redis job queues defer enqueueing during an active transaction until commit. Call generated event
dispatch()directly inside the transaction. - Pub/sub: direct
PubSubService.publish(), including publication from generated event dispatch, sends immediately. A notification can arrive before commit. - Cache operations: request-cache puts and removes are queued for flushing on commit or connection release. A whole-cache
clear()executes immediately. Cache updates are not a substitute for database transactions.
For a separate side effect that specifically must follow a successful commit, use bosca.db.afterCommit { }. It runs immediately when no transaction is active. It is not a durable delivery mechanism.
Code outside an HTTP or GraphQL request
Establish both contexts when invoking services that use SQL and ServiceCache. Import bosca.db.withConnectionManager and bosca.cache.withRequestCache:
Apply the same rule to background listeners that call services. See Caching and Messaging & Events.