I m looking into using savepoints but what I see says it onl

Question

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 · Answer

in theory there will be the same connections in the transaction

Bogdan · Answer

`Exposed` must have access to the connection inside the transaction

Paul Higgins · Answer

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 · Answer

maybe the problem is in Entity caches

Bogdan · Answer

PS `with TransactionManager current ` is redundant

Bogdan · Answer

``` transaction val savepoint = connection setSavepoint test entityCache flush connection rollback savepoint ```

Bogdan · Answer

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 · Answer

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 disappointed

Bogdan · Answer

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

Paul Higgins · Answer

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 · Answer

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 · Answer

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 · Answer

did you disable `autocommit`

Paul Higgins · Answer

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 · Answer

gt 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 · Answer