Working with Transactions

Accessing returned values

Although you can modify variables from your code within the transaction block, transaction() supports returning a value directly, enabling immutability:

val jamesList = transaction {
    Users.selectAll().where { Users.firstName eq "James" }.toList()
}
// jamesList is now a List<ResultRow> containing Users data

note

Blob and text fields won't be available outside a transaction if you don't load them directly. For text fields you can also use the eagerLoading param when defining the Table to make the text fields available outside the transaction.

// without eagerLoading
val idsAndContent = transaction {
   Documents.selectAll().limit(10).map { it[Documents.id] to it[Documents.content] }
}

// with eagerLoading for text fields
object Documents : Table() {
  //...
  val content = text("content", eagerLoading = true)
}

val documentsWithContent = transaction {
   Documents.selectAll().limit(10)
}

Working with multiple databases

If you want to work with different databases, you have to store the database reference returned by Database.connect() and provide it to transaction() function as the first parameter. The transaction() block without parameters will work with the latest connected database.

val db1 = connect("jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;", "org.h2.Driver", "root", "")
val db2 = connect("jdbc:h2:mem:db2;DB_CLOSE_DELAY=-1;", "org.h2.Driver", "root", "")
transaction(db1) {
   //...
   val result = transaction(db2) {
      Table1.selectAll().where { }.map { it[Table1.name] }
   }

   val count = Table2.selectAll().where { Table2.name inList result }.count()
}

Entities stick to a transaction that was used to load that entity. That means that all changes persist to the same database and what cross-database references are prohibited and will throw exceptions.

Setting default database

A transaction() block without parameters will use the default database. As before 0.10.1 this will be the latest connected database. It is also possible to set the default database explicitly.

val db = Database.connect()
TransactionManager.defaultDatabase = db

Using nested transactions

By default, a nested transaction() block shares the transaction resources of its parent transaction() block, so any effect on the child affects the parent:

val db = Database.connect()
db.useNestedTransactions = false // set by default

transaction {
    println("Transaction # ${this.id}") // Transaction # 1
    FooTable.insert{ it[id] = 1 }
    println(FooTable.selectAll().count()) // 1

    transaction {
        println("Transaction # ${this.id}") // Transaction # 1
        FooTable.insert{ it[id] = 2 }
        println(FooTable.selectAll().count()) // 2

        rollback()
    }

    println(FooTable.selectAll().count()) // 0
}

Since Exposed 0.16.1 it is possible to use nested transactions as separate transactions by setting useNestedTransactions = true on the desired Database instance.

After that any exception or rollback operation that happens within a transaction block will not roll back the whole transaction but only the code inside the current transaction. Exposed uses SQL SAVEPOINT functionality to mark the current transaction at the beginning of a transaction() block and release it on exit.

Using SAVEPOINT could affect performance, so please read the documentation of the DBMS you use for more details.

val db = Database.connect(
    // connection parameters
)
db.useNestedTransactions = true

transaction {
    println("Transaction # ${this.id}") // Transaction # 1
    FooTable.insert{ it[id] = 1 }
    println(FooTable.selectAll().count()) // 1

    transaction {
        println("Transaction # ${this.id}") // Transaction # 2
        FooTable.insert{ it[id] = 2 }
        println(FooTable.selectAll().count()) // 2

        rollback()
    }

    println(FooTable.selectAll().count()) // 1
}

Working with Coroutines

In the modern world, non-blocking and asynchronous code is popular. Kotlin has Coroutines, which provide an imperative way to write asynchronous code. Most Kotlin frameworks (like Ktor) have built-in support for coroutines, while Exposed is mostly blocking.

Why?

Because Exposed interacts with databases using JDBC API, which was designed in an era of blocking APIs. Additionally, Exposed stores some values in thread-local variables while coroutines could (and will) be executed in different threads.

Starting from Exposed 0.15.1, bridge functions are available that give you a safe way to interact with Exposed within suspend blocks: newSuspendedTransaction() and Transaction.withSuspendTransaction(). These have the same parameters as a blocking transaction() but allow you to provide a CoroutineContext argument that explicitly specifies the CoroutineDispatcher in which the function will be executed. If context is not provided your code will be executed within the current CoroutineContext.

Here is an example that uses these three types of transactions:

transaction {
    println("Transaction # ${this.id}") // Transaction # 1
    SchemaUtils.create(FooTable) // Table will be created on a current thread

    runBlocking {
        newSuspendedTransaction(Dispatchers.Default) {
            println("Transaction # ${this.id}") // Transaction # 2
            FooTable.insert { it[id] = 1 }  // This insert will be executed in one of the Default dispatcher threads

            withSuspendTransaction {
                println("Transaction # ${this.id}") // Transaction # 2
                // This select also will be executed on some thread from Default dispatcher using the same transaction as its parent
                FooTable.selectAll().where { FooTable.id eq 1 }.single()[FooTable.id]
            }
        }
    }

    transaction {
        println("Transaction # ${this.id}") // Transaction # 1
    }

    runBlocking {
        val result = newSuspendedTransaction(Dispatchers.IO) {
            println("Transaction # ${this.id}") // Transaction # 3
            FooTable.selectAll().where { FooTable.id eq 1 }.single()[FooTable.id] // This select will be executed on some thread from IO dispatcher
        }
        println("Result: $result") // Result: 1
    }

    SchemaUtils.drop(Testing)
}

Please note that such code remains blocking (as it still uses JDBC) and you should not try to share a transaction between multiple threads as it may lead to undefined behavior.

If you desire to execute some code asynchronously and use the result later, take a look at suspendedTransactionAsync():

runBlocking {
    val launchResult = suspendedTransactionAsync(Dispatchers.IO) {
        FooTable.insert { it[id] = 2 }

        FooTable.selectAll().where { FooTable.id eq 2 }.singleOrNull()?.getOrNull(FooTable.id)
    }

    println("Async result: " + (launchResult.await() ?: -1)) // Async result: 2
}

This function will accept the same parameters as newSuspendedTransaction() above but returns its future result as an implementation of Deferred, which you can await on to achieve your result.

note

newSuspendedTransaction and suspendedTransactionAsync are always executed in a new transaction to prevent concurrency issues when query execution order could be changed by the CoroutineDispatcher. This means that nesting these suspend transactions may not result in the same behavior as nested transactions (when useNestedTransactions = false), as was shown in the previous section.

Advanced parameters and usage

For specific functionality, transactions can be created with the additional parameters: transactionIsolation, readOnly, and db:

transaction (Connection.TRANSACTION_SERIALIZABLE, true, db = db) {
    // DSL/DAO operations go here
}

val db = Database.connect(
    datasource = datasource,
    databaseConfig = DatabaseConfig {
        defaultMaxAttempts = 3
    }
)

// property set in transaction block overrides default DatabaseConfig
transaction(db = db) {
    maxAttempts = 25
    // operation that may need multiple attempts
}

transaction {
    queryTimeout = 3
    try {
        // operation that may run for more than 3 seconds
    } catch (cause: ExposedSQLException) {
        // logic to perform if execution timed out
    }
}