Nested transactions and rollbacks.

[spand]

After working a bit with manual rollbacks and nested transaction {} blocks it seems that a call to rollback() will rollback the current db transaction but otherwise let db work continue in a transaction {} block.

This isnt quite what I expected. I think I would expect a transaction to be either in an open or closed state and any call to either commit() or rollback() would close it requiring a new top level transaction block to start db work again (or maybe let it continue if in a top level transaction). Not let the "sub transaction" continue doing db work and certainly not the top level transaction {} block after a nested one has triggered a rollback.

Thoughts ?

[antanas-arvasevicius]

I've encountered with the same issue, had made PR to fix this: #544

[swistaczek]

Hey guys, I encountered the same error.

[Tapac]

Fixed in master will be released soon.

[spand]

Not that I dont appreciate actual nested transaction support but it is a bit unrelated to my issue.. which is what should happen with nested exposed transaction blocks in the case of db.useNestedTransactions = false.

What I suggested something like this:

    @Test(expected = ExposedSQLException::class)
    fun rollbackClosesTransactionBlock() {
        withTables(DMLTestsData.Cities) {
            rollback()
            DMLTestsData.Cities.selectAll() // throws exception because connection is now closed
            fail()
        }
    }

    @Test(expected = ExposedSQLException::class)
    fun nestedRollbackClosesOuterTransactionBlock() {
        withTables(DMLTestsData.Cities) {
            transaction {
                rollback()
            }
            DMLTestsData.Cities.selectAll() // throws exception because connection is now closed
            fail()
        }
    }

[Tapac]

@spand, there are cases when you want to make commit to flush changes to the database and continue to work with your code, same for a rollback.

Maybe additional functions like rollbackAndClose should be introduced?