Working with Transactions

CRUD operations in Exposed must be called from within a transaction. Transactions encapsulate a set of DSL operations.

Create a transaction

To create and execute a transaction with default parameters, simply pass a function block to the transaction() function:

import org.jetbrains.exposed.v1.jdbc.transactions.transaction

transaction {
    // DSL/DAO operations go here
}

Transactions are executed synchronously on the current thread. This means they might block other parts of your application if not managed carefully.

If you need to execute a transaction asynchronously or within a coroutine, use a suspend-based transaction instead.

Suspend-based transaction

Use suspendTransaction() from exposed-r2dbc to perform non-blocking operations in coroutine-based applications:

import org.jetbrains.exposed.v1.r2dbc.transactions.suspendTransaction

suspendTransaction {
    // DSL/DAO operations go here
}

For compatibility with JDBC drivers, a suspendTransaction() is also available to call suspend functions alongside blocking database operations.

The behavior of both these functions match that of transaction(), but with their statement parameter accepting suspend functions. To pass additional context to either suspendTransaction(), wrap it in a coroutine builder function, like withContext() or async().

Accessing returned values

Although you can modify variables from your code within the transaction block, it also supports returning a value directly, enabling immutability.

In the following example, jamesList is a List<ResultRow> containing UsersTable data:

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. {style="note"}

// 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 the .connect() function and provide it to the 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 a default database

To set the default database explicitly, use the TransactionManager.defaultDatabase property:

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

Retrieving this defaultDatabase property will return the set value, or null if no value was provided.

A transaction block without parameters uses the default database or the latest connected database. To retrieve and check the Database instance that would be used by a transaction block in this case, get the TransactionManager.primaryDatabase property.

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 the useNestedTransactions property on the Database instance to true:

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 SAVEPOINT may affect performance. For more details, refer to your database documentation.

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 or an R2dbcExposedConnection, which acts as a wrapper around the underlying JDBC or R2DBC connection respectively.

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

{src="exposed-transactions/src/main/kotlin/org/example/examples/SavepointExample.kt" include-lines="39,41-50"}

Advanced parameters and usage

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

db

The db parameter is optional and is used to select the database where the transaction should be settled. This is useful when working with multiple databases.

transactionIsolation

The transactionIsolation parameter specifies what is supposed to happen when multiple transactions execute concurrently on the database. This value is sent to the database where it is taken into account. By default, it is set to use the value provided to the database's transaction manager configuration.

The allowed values for JDBC connections are defined in java.sql.Connection and for R2DBC connections in io.r2dbc.spi.IsolationLevel.

TRANSACTION_NONE : Transactions are not supported.

TRANSACTION_READ_UNCOMMITTED : Allows uncommitted changes from one transaction to affect a read in another transaction (a "dirty read").

TRANSACTION_READ_COMMITTED (default, except for MySql and SQLite) : 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 (default for MySql) : 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 (default for SQLite) : Prevents dirty reads, non-repeatable reads, and phantom reads.

{type="wide"}

READ_UNCOMMITTED : Allows uncommitted changes from one transaction to affect a read in another transaction (a "dirty read").

READ_COMMITTED (default, except for MySql) : 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.

REPEATABLE_READ (default for MySql) : 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.

SERIALIZABLE : Prevents dirty reads, non-repeatable reads, and phantom reads.

{type="wide"}

readOnly

The readOnly parameter indicates whether any database connection used by the transaction is in read-only mode. By default, it is set to use the value provided to the database's transaction manager configuration. This value is not directly used by Exposed, but is relayed to the database.

maxAttempts

Use the maxAttempts property to set the maximum number of attempts 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 it 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.

If 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
}

queryTimeout

Use queryTimeout to set 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
    }
}

This value is not directly managed by Exposed, but is relayed to the JDBC or R2DBC driver.

Some drivers may not support implementing this limit. For more information, refer to the relevant driver documentation. {style="note"}