I'm looking into using savepoints but what I see s...

# exposed

[Paul Higgins]

I'm looking into using savepoints but what I see says it only works with raw connections. This means if I'm using a datasource with pooling I can't do savepoints?

What I'm trying to implement is an "all or none" type functionality for updates using the DAO and not sure the best way to approach it. Let's say I'm writing three records to update a timestamp to the current time. If a write fails on any of them, I want to be able to rollback all three.

Since a write to the DB only happens at the end of a transaction, aside from savepoint is there a way I can confirm all writes were successful and then rollback if it failed?

[Bogdan]

in theory, there will be the same connections in the transaction

[Bogdan]

Exposed must have access to the connection inside the transaction

[Paul Higgins]

It looks like I can get to the connection in the context of a transaction, but the rollback itself doesn't seem to do anything. I do a very simple block like this and I still see that the field has been updated after the transaction ends. I would have expected it to have rolled back any changes made to that row. I'm using H2 which does support rollbacks

transaction {
            with(TransactionManager.current()) {
                val savepoint = connection.setSavepoint("savepoint")
                val thingToUpdate = Thing.find { Thing.id eq "anID" }.first()
                thingToUpdate.field = "newValue"            
                connection.rollback(savepoint)
            }
        }

[Bogdan]

maybe the problem is in Entity caches

[Bogdan]

PS with(TransactionManager.current()) is redundant

[Bogdan]

transaction {
        val savepoint = connection.setSavepoint("test")
        entityCache.flush()
        connection.rollback(savepoint)
    }

[Bogdan]

the problem is that manipulation with Entity does not affect the database in any way until flushCache. Which is executed at the end of the transaction

[Paul Higgins]

Flushing the cache didn't change the result unfortunately. I would have thought that because the manipulation doesn't happen until the end of the transaction the rollback would have worked if done before the end of the same transaction where the modification is happening.

I'm wondering if maybe it's because of how I set up my H2, but I would have expected to get an error if attempting to rollback on a DB that didn't support it. I can try using postgres instead to verify, but it seems like the code is so simple it should be clear why it's not working 😞

[Bogdan]

1. Get rid of DAO work for now
2. Remove the DataSource. Test on a simple connection

[Paul Higgins]

I'm testing with a simple DB connection like Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;IGNORECASE=true;")

It works when I do explicitly write it out, so it's only failing when I use the connection.setSavepoint and .rollback methods

exec("SAVEPOINT saveme")
exec("ROLLBACK TO SAVEPOINT saveme")

[Paul Higgins]

The DEBUG Exposed lines don't show any savepoint or rollback commands in the transaction. But when I do it explicitly it does. It's like Exposed is not calling the save/rollback at all

[Paul Higgins]

It looks like I can also do a TransactionManager.current().rollback() at the end of the transaction block to just roll back the whole thing, which might be enough for what I need to do. Just very strange the documented way doesn't work

[Bogdan]

did you disable autocommit ?

[Paul Higgins]

I didn't change the autocommit from whatever was the default. It looks like it's working now, although I'm not sure what I changed. I did restructure a bit and started using nested transactions, so maybe I had a transaction somewhere else that was causing problems.

I also notice that static mocking in some other tests is causing problems so it's possible my test class was somehow causing the rollbacks not to work as expected

[Bogdan]

I didn't change the autocommit from whatever was the default.

autocommit = true by default. In the examples, when working with savepoint, autocommit is set to false

[Bogdan]

https://www.ibm.com/docs/en/db2-for-zos/12?topic=sql-savepoints-in-jdbc-applications