Outer Transaction will create entities from failed inner transaction

[simboel]

Problem

When an inner (as in nested) transaction will throw an exception and the outer transaction code will catch this exception, all entities from the inner transaction will be created.

This is especially problematic in tests where the test method will open a transaction (by default via Interceptor) and then call a Service which internally creates a transaction, too.

Context

Spring Boot (2.7.5) application with Exposed Starter (0.41.1).

Example

object StarWarsFilms : IntIdTable() {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", 50)
}

class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms)

    var sequelId by StarWarsFilms.sequelId
    var name by StarWarsFilms.name
    var director by StarWarsFilms.director
}

/** Basic assertThrows function implementation, so we don't need other dependencies for this example. */
inline fun <reified T : Exception> assertThrows(block: () -> Unit) {
    var exceptionThrown = false
    try { block() } catch (ex: Exception) {
        exceptionThrown = true
        if (ex !is IllegalStateException)
            throw RuntimeException("Expected ${T::class.simpleName} to be thrown, but ${ex::class.simpleName} was thrown instead.")
    }
    if (!exceptionThrown) throw RuntimeException("Expected ${T::class.simpleName} to be thrown, but nothing was thrown")
}

fun test() {

    // This will work without any problem and as expected.
    // The entity will **not** be created because there was an exception in the transaction.
    // Creating the entity would've resulted in an BatchDataInconsistentException as we didn't set the director.
    println("Test case 1")
    assertThrows<IllegalStateException> {
        transaction {
            addLogger(StdOutSqlLogger)
            StarWarsFilm.new {
                name = "Film 1"
                sequelId = 1
            }
            // some validation function
            throw IllegalStateException("Some problem...")
        }
    }


    // This will not work!
    // Even though the inner transaction exited with an exception,
    // the outer transaction will now try to create the entity from
    // the inner transaction.
    println("Test case 2")
    transaction {
        // Test case 2 outer transaction
        addLogger(StdOutSqlLogger)
        assertThrows<IllegalStateException> {
            transaction {
                // Test case 2 inner transaction
                StarWarsFilm.new {
                    name = "Film 2"
                    sequelId = 2
                }
                // some validation function will now throw an exception
                throw IllegalStateException("Some problem...")
            }
        }
    }

    // This will work as expected
    // The inner transaction will throw an exception.
    // This will be given to the outer transaction, too.
    // This way, no entity will be created in the database.
    println("Test case 3")
    assertThrows<IllegalStateException> {
        transaction {
            // Test case 3 outer transaction
            addLogger(StdOutSqlLogger)
            transaction {
                // Test case 3 inner transaction
                StarWarsFilm.new {
                    name = "Film 3"
                    sequelId = 3
                }
                // some validation function will now throw an exception
                throw IllegalStateException("Some problem...")
            }
        }
    }
}

The result of the execution is

[...]
Test case 1
Test case 2
SQL: INSERT INTO STARWARSFILMS ("NAME", SEQUEL_ID) VALUES ('Film 2', 2)
Test case 3
[...]

The SQL: INSERT INTO STARWARSFILMS ("NAME", SEQUEL_ID) VALUES ('Film 2', 2) shouldn't be there as you can clearly see that the Test case 2 inner transaction has been terminated with an IllegalStateException

When looking into the database (H2 in my case) you can see that Film 2 was created:

Expected behavior

The outer transaction of Test case 2 should not create the entity Film 2 as the inner transaction (where the entity was created) encountered an exception. In general: An outer transaction should never create entities from an inner transaction if this transaction exited with an exception.

useNestedTransactions = true didn't help, either.

[AlexeySoshin]

Thank you for providing the details to reproduce that. Will take a look.

[AlexeySoshin]

I didn't manage to reproduce this behaviour, but maybe that's because I'm running my tests on H2 database.

[simboel]

I was using H2, too. I'll create a sample project and upload that later today.

[simboel]

Sorry @AlexeySoshin , there were a few errors in the example from above (missing StdOutLogger, director column, ...). I've fixed the example code above.

Here is the project file using Spring Boot 2.7.5 and Exposed 0.41.1
demo.zip

Just execute ./gradlew bootRun
This will result in

[...]
Test case 1
Test case 2
SQL: INSERT INTO STARWARSFILMS ("NAME", SEQUEL_ID) VALUES ('Film 2', 2)
Test case 3
[...]

Inside the archive you'll find the h2 db file from a single run including the db state after executing the application. This contains "Film 2" as mentioned from the logs (which is not correct behavior).

Expected behavior

An outer transaction should never create entities from an inner transaction, when that inner transaction didn't return without an exception.