Up until now, you've been running simple queries. But in the real world, you rarely just "update one thing." You usually need to update three things, and if the third one fails, you need the first two to act like they never happened. That's where transactions come in.

How do I actually wrap my database calls in a transaction?

In Exposed, everything that touches the database must happen inside a transaction block. If you try to query a table outside of one, Exposed will throw an exception and tell you that there's no transaction in context. I usually think of the transaction { ... } block as a safety container.

Let's say we're building a simple library system. We have a Books table and a Loans table. When a user borrows a book, we need to do two things: mark the book as unavailable and create a loan record. We can't have one without the other.

transaction {
    // 1. Mark the book as borrowed
    Books.update({ Books.id eq bookId }) {
        it[available] = false
    }

    // 2. Create the loan record
    Loans.insert {
        it[userId] = currentUserId
        it[bookId] = bookId
        it[loanDate] = LocalDate.now()
    }
}

Because both of these calls are inside the same transaction block, Exposed ensures they are treated as a single unit of work.

What happens if something crashes halfway through the block?

This is the "magic" of transactions: atomicity. If an exception is thrown anywhere inside the transaction block, Exposed automatically rolls back every single change made within that block. The database reverts to exactly how it looked before the block started.

I've seen a lot of developers try to wrap individual database calls in try-catch blocks inside a transaction. Be careful with that. If you catch the exception and don't re-throw it, Exposed thinks everything went fine and will commit the partial changes. If you want a rollback, let the exception bubble up or throw your own.

transaction {
    Books.update({ Books.id eq bookId }) {
        it[available] = false
    }

    // Imagine this throws a ConstraintViolationException because the user is banned
    Loans.insert {
        it[userId] = bannedUserId 
        it[bookId] = bookId
    }
    
    // If the line above fails, the Book update is automatically undone.
}

How do I handle transactions when I'm using Coroutines?

Here is where things get tricky. The standard transaction { ... } block is blocking. If you're using a framework like Ktor or any heavy coroutine-based architecture, calling transaction on the main thread will freeze your application. It's a common performance killer.

To fix this, Exposed provides newSuspendedTransaction. This allows the transaction to run in a way that doesn't block the thread, using a CoroutineDispatcher (usually Dispatchers.IO). I almost always use this in production apps.

suspend fun checkoutBook(bookId: Int, userId: Int) {
    newSuspendedTransaction(Dispatchers.IO) {
        // This is now a suspending block
        val book = Books.select { Books.id eq bookId }.single()
        
        if (book[Books.available]) {
            Books.update({ Books.id eq bookId }) { it[available] = false }
            Loans.insert {
                it[userId] = userId
                it[bookId] = bookId
            }
        } else {
            throw IllegalStateException("Book is already checked out!")
        }
    }
}

One quick tip: if you are already inside a newSuspendedTransaction and you call another function that also uses newSuspendedTransaction, Exposed will handle the nesting, but be mindful of how long you keep those transactions open. Long-running transactions lock rows in your database, which can lead to those dreaded deadlock errors.

📋 Practical Task

Exercise: Implement a Library Loan Rollback

You are tasked with implementing a "Safe Loan" feature for the library system. You need to write a function called processLoan that handles the following logic:

• It must take a userId and a bookId.
• It must occur within a single transaction.
• First, it should check if the user has more than 5 books currently loaned out in the Loans table.
• If the user has 5 or more books, it should throw a custom exception TooManyBooksException.
• If they have fewer than 5, it should mark the book as unavailable in the Books table and create the record in the Loans table.

Goal: Ensure that if the TooManyBooksException is thrown, no changes are made to the Books table, and the operation is fully rolled back.