Today I had to implement a simple DB transactional call.

A transaction is a way to make few SQL queries executed together, and make sure they are either all executed or rollback in case one they failed. The SQL syntax for that is BEGIN and COMMIT / ROLLBACK .

The function to implement accepts a lambda f() with multiple SQL queries and execute the lambda inside a transaction scope. After the transaction was committed a value from the queries is returned. This value is of the generic type A in our case.

Why not just use JDBC and blocking code?

We are implementing an async driver to access MySQL and PostgreSQL called jasync-sql. With the driver, all calls are async and non-blocking. For example, sendQuery() method has two flavors, both of them async. One return CompleteableFuture of the result and the other one is suspending. You pick the flavor based on your personal preference.

Coroutines

With coroutines that is pretty straight-forward: BEGIN transaction, execute the SQL query and then COMMIT . In case of failure, we need to call ROLLBACK .

Here is the code:

suspend fun <A> inTransaction(

f: suspend (Connection) -> A): A {

try {

this.sendQuery("BEGIN")

val result = f(this)

this.sendQuery("COMMIT")

return result

} catch (e: Throwable) {

this.sendQuery("ROLLBACK")

throw e

}

}