i have a question about suspendable transactions ...

# exposed

[Tor Egil]

i have a question about suspendable transactions

the following scenario:
- prosess data for entity
- save prosessed data to the database
- further prosessing of the entity
- save final state of the entity to the database

i would like to create a suspendable transactions that covers the storing of the entity.
if an exception arises, none of the data is committed.
co-pilot keeps telling me that i cannot switch dispatchers within a transaction-boundary and i am wondering if this is a feature that is beeing worked on?

[spand]

Did you verify if this is true or not ?

[spand]

Seems like a simple test case

[Tor Egil]

i did not verify this, as we have not yet implemented exposed in my project. this is something i came over when investigating transactions and copilot gives me a very detailed explaination:

Why You Cannot Change Dispatchers Within a Suspendable Transaction Boundary
The core issue comes down to thread affinity in JDBC/database connections and how Kotlin coroutines interact with them.
Key reasons:
JDBC connections are thread-bound: Most JDBC drivers and connection pools (e.g., HikariCP) bind a connection to the thread that checked it out. The transaction state (BEGIN, COMMIT, ROLLBACK) lives on that specific connection, which is associated with that specific thread.
withContext(Dispatchers.IO) switches threads: When you call withContext(Dispatchers.IO) or any other dispatcher change inside a transaction block, the coroutine may resume on a different thread. This means:
The new thread has no knowledge of the active transaction
The original connection (with the open transaction) is on a different thread
Any database call on the new thread will either get a new connection (outside the transaction) or fail entirely
Transaction context is stored in ThreadLocal: Frameworks like Spring (@Transactional), Exposed, and JOOQ store the current transaction/connection in a ThreadLocal. When the dispatcher switches the thread, the ThreadLocal value is lost — the new thread sees null or a different value.
Coroutine suspension points are dangerous: Even without an explicit dispatcher change, a suspend function that yields could theoretically resume on a different thread (depending on the dispatcher). This is why transactional code using coroutines typically requires a single-threaded or confined dispatcher to guarantee thread affinity.
What happens in practice:
Data inconsistency: Part of your work executes inside the transaction, part outside — you lose atomicity.
Connection leaks: The original thread's connection may never get properly returned to the pool.
Deadlocks: The new thread might try to acquire a new connection from the pool while the original one is still held, potentially exhausting the pool.
The solution patterns:
Run all transactional work on a single confined dispatcher (e.g., Dispatchers.IO.limitedParallelism(1) or a dedicated single-thread dispatcher)
Use TransactionCoroutineContext if your framework provides one (e.g., Spring's ReactiveTransactionManager or Exposed's newSuspendedTransaction which propagates the connection via coroutine context instead of ThreadLocal)
Do all I/O and dispatcher-switching outside the transaction boundary, and only enter the transaction block when you're ready to do purely database work on a single thread