Working with Transactions

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

// 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. This means that any changes within the nested transaction affect the outer transaction. If a rollback occurs inside the nested block, it will roll back changes in the parent transaction as well:

val db = Database.connect()
db.useNestedTransactions = false // Default setting

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
}

Independent nested transactions

To allow nested transactions to function independently, set useNestedTransactions = true on the Database instance:

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
}

With this, any rollback or exception inside a nested transaction {} affects only that block, without rolling back the outer transaction.

Exposed achieves this by using SQL SAVEPOINT to mark transaction states at the beginning of each transaction {} block, releasing them on exit.

Using savepoints

To roll back to a specific point without affecting the entire transaction, you can set a savepoint through the transaction's connection property. The connection property provides access to an ExposedConnection, which acts as a wrapper around the underlying JDBC connection.

To manually create a savepoint within a transaction, use the .setSavepoint() method:

        transaction {
            TestTable.insert { it[amount] = 99 }
            val firstInsert = connection.setSavepoint("first_insert")
            TestTable.insert { it[amount] = 100 }
            connection.rollback(firstInsert)
        }

        transaction {
            // If the savepoint was not set in above tx, all inserts would roll back, returning an empty list
            println(TestTable.selectAll().map { it[TestTable.amount] }) // [99]
        }

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.

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
}

`transactionIsolation`

The transactionIsolation 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.

`readOnly`

The readOnly parameter indicates whether any database connection used by the transaction is in read-only mode, and is set to false by default. Much like with transactionIsolation, this value is not directly used by Exposed, but is simply relayed to the database.

`db`

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

`maxAttempts`

Transactions also provide a property, maxAttempts, which sets the maximum number of attempts that should be made to perform a transaction block. If this value is set to 1 and an SQLException occurs inside the transaction block, the exception will be thrown without performing a retry. If this property is not set, any default value provided in DatabaseConfig will be used instead:

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
}

If this property is set to a value greater than 1, minRetryDelay and maxRetryDelay can also be set in the transaction block to indicate the minimum and maximum number of milliseconds to wait before retrying.

`queryTimeout`

Another advanced property available in a transaction block is queryTimeout. This sets the number of seconds to wait for each statement in the block to execute before timing out:

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

Last modified: 27 May 2025