https://kotlinlang.org logo
#exposed
Title
# exposed
p

Paul Higgins

02/07/2022, 9:29 PM
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?
b

Bogdan

02/08/2022, 3:45 PM
in theory, there will be the same connections in the transaction
Exposed
must have access to the connection inside the transaction
p

Paul Higgins

02/09/2022, 12:18 AM
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
Copy code
transaction {
            with(TransactionManager.current()) {
                val savepoint = connection.setSavepoint("savepoint")
                val thingToUpdate = Thing.find { Thing.id eq "anID" }.first()
                thingToUpdate.field = "newValue"            
                connection.rollback(savepoint)
            }
        }
b

Bogdan

02/10/2022, 7:05 PM
maybe the problem is in Entity caches
PS
with(TransactionManager.current())
is redundant
Copy code
transaction {
        val savepoint = connection.setSavepoint("test")
        entityCache.flush()
        connection.rollback(savepoint)
    }
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
p

Paul Higgins

02/10/2022, 7:30 PM
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 😞
b

Bogdan

02/11/2022, 2:26 PM
1. Get rid of DAO work for now 2. Remove the DataSource. Test on a simple connection
p

Paul Higgins

02/11/2022, 7:48 PM
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")
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
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
b

Bogdan

02/12/2022, 3:38 PM
did you disable
autocommit
?
p

Paul Higgins

02/15/2022, 8:50 PM
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
b

Bogdan

02/17/2022, 4:29 PM
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
3 Views