Transactions.md

Overview

CRUD operations in Exposed must be called from within a transaction. Transactions encapsulate a set of DSL operations. To create and execute a transaction with default parameters, simply pass a function block to the transaction function:

transaction {
    // DSL/DAO operations go here
}

Transactions are executed synchronously on the current thread, so they will block other parts of your application! If you need to execute a transaction asynchronously, consider running it on a separate Thread.

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.select { Users.firstName eq "James" }.toList()
}
// jamesList is now a List<ResultRow> containing Users data

Note: Blob and text fields won't be available outside of 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 of 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 a multiple databases

This functionality supported since 0.10.1 version

When you want to work with different databases then you have to store database reference returned by Database.connect() and provide it to transaction function as first parameter.

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.select{ }.map { it[Table1.name] }
   }
   
   val count = Table2.select { Table2.name inList result }.count()
}

Entities (see [[DAO API|DAO]] page) 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

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

Since Exposed 0.16.1 it is possible to use nested transactions. To enable this feature you should set useNestedTransactions on desire Database instance to true.

After that any exception that happens within transaction block will not rollback the whole transaction but only the code inside current transaction. Exposed uses SQL SAVEPOINT functionality to mark current transaction at the begining of transaction block and release it on exit from it.

Using savepoint could affect performance, so please read documentation on DBMS you use for more details.

val db = Database.connect()
db.useNestedTransactions = true

transaction {
    FooTable.insert{ it[id] = 1 }
    
    var idToInsert = 0
    transaction { // nested transaction
        idToInsert++
        // On the first insert it will fail with unique constraint exception and will rollback to the `nested transaction` and then insert a new record with id = 2
        FooTable.insert{ it[id] = idToInsert } 
    }
}

Working with Coroutines

In the modern world non-blocking and asynchronous code is popular. Kotlin has Coroutines that give you an imperative way of asynchronous code writing. Most of Kotlin frameworks (like ktor) have built-in support for Coroutines while Exposed is mostly blocking.

Why?

Because Exposed uses JDBC-api to interact with databases that was designed in an era of blocking apis. What's more, Exposed store some values in thread-local variables while Coroutines could (and will) be executed in different threads.

Since Exposed 0.15.1 there are bridge functions that will give you a safe way to interact with Exposed within suspend blocks: newSuspendedTransaction/Transaction.suspendedTransaction have same parameters as a blocking transaction function but will allow you to provide CoroutineDispatcher in which function will be executed. If context is not provided your code will be executed within current coroutineContext.

Sample usage looks like:

runBlocking {
    transaction {    
        SchemaUtils.create(FooTable) // Table will be created on a current thread
    
        newSuspendedTransaction(Dispatchers.Default) {
            FooTable.insert { it[id] = 1 } // This insert will be executed in one of Default dispatcher threads
    
            suspendedTransaction {
                val id = FooTable.select { FooTable.id eq 1 }.single()()[FooTable.id] // This select also will be executed on some thread from Default dispatcher using the same transaction
            }
        }
    
        val result = newSuspendedTransaction(Dispatchers.IO) {
            FooTable.select { FooTable.id eq 1 }.single()[H2Tests.Testing.id] // This select will be executed on some thread from IO dispatcher using the same transaction
        }
    }
}

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

If you desire to execute some code asynchronously and use the result later in your code take a look at suspendedTransactionAsync function.

val launchResult = suspendedTransactionAsync(Dispatchers.IO, db = db) {
    FooTable.insert{}

    FooTable.select { FooTable.id eq 1 }.singleOrNull()?.getOrNull(Testing.id)
}

println("Result: " + (launchResult.await() ?: -1))

This function will accept the same parameters as newSuspendedTransaction above but returns Deferred which you could await on to achieve your result.

suspendedTransactionAsync is always executed in new transaction to prevent concurrency issues when queries execution order could be changed by CoroutineDispatcher.

Advanced parameters and usage

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

transaction (Connection.TRANSACTION_SERIALIZABLE, 2) {
    // DSL/DAO operations go here
}

Transaction Isolation: This parameter, defined in the SQL standard, specifies what is supposed to happen when multiple transactions execute concurrently on the database. This value does NOT affect Exposed operation directly, but is sent to the database, where it is expected to be obeyed. Allowable values are defined in java.sql.Connection and are as follows:

• TRANSACTION_NONE: Transactions are not supported.
• TRANSACTION_READ_UNCOMMITTED: The most lenient setting. Allows uncommitted changes from one transaction to affect a read in another transaction (a "dirty read").
• TRANSACTION_READ_COMMITTED: This setting prevents dirty reads from occurring, but still allows non-repeatable reads to occur. A non-repeatable read is when a transaction ("Transaction A") reads a row from the database, another transaction ("Transaction B") changes the row, and Transaction A reads the row again, resulting in an inconsistency.
• TRANSACTION_REPEATABLE_READ: The default setting for Exposed transactions. Prevents both dirty and non-repeatable reads, but still allows for phantom reads. A phantom read is when a transaction ("Transaction A") selects a list of rows through a WHERE clause, another transaction ("Transaction B") performs an INSERT or DELETE with a row that satisfies Transaction A's WHERE clause, and Transaction A selects using the same WHERE clause again, resulting in an inconsistency.
• TRANSACTION_SERIALIZABLE: The strictest setting. Prevents dirty reads, non-repeatable reads, and phantom reads.

db parameter is optional and used to select database where transaction should be settled (see section above).