Rolling back transactions with clojure.java.jdbc

[Mark]

I've been working on a small utility script to clean up a very large table (~1 billion rows). Because the table is so large, I want to go through and delete it chunk at a time. I've written a simple script that does this, but when I was testing it against our dev instance, I found that it wasn't rolling back, as I'd hoped it would.

You can see a simplified version of the script here:

https://gist.github.com/anonymous/14ed57085709a2772ee0

It's using an Oracle database (11GR1, I think) and clojure.java.jdbc version 0.3.0-alpha5. I've dug through the clojure.java.jdbc docs and code, but I don't see an obvious problem with what I'm doing, although I presume that to someone who knows this library better, my problem is quite simple.

Does anyone have any idea what I'm doing wrong? Can you point to an example that issues deletes (or inserts, or updates) that rollback?

Thanks!
Mark

[Sean Corfield]

Remove :transaction? true from the delete! call.

You're telling delete! to run inside its own transaction - you don't
want that: that's why your deletes do not rollback.

Sean

> --
> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with your
> first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
> ---
> You received this message because you are subscribed to the Google Groups
> "Clojure" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to clojure+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.



--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Mark]

That doesn't seem to work. Neither does explicitly setting :transaction? to false.

Looking at the source of execute!, it looks like the statement is running within a "with-open" on the java.sql.Connection, which is where a commit or rollback would occur. I presume that the with-open is closing the connection, which is resulting in a commit. Is there some way I can set autocommit to false on the connection through the db-spec?

Unrelated to this, I had earlier been trying to use a DataSource provider that we have and wrap it with {:datasource ds}, which worked fine for executing queries, but db-set-rollback-only! would throw a NullPointerException (from reset!) if I created the data source that way. I dug around in the source code a bit more, but didn't see immediately what was going on - that being said, I'm not terribly familiar with how atoms work in Clojure.

Thanks for your help!

Mark

[Mark]

Scratch that aside, I think I have that part working. It was a little gnarly because the datasource provider was written in Scala and posed a few potentially confusing interop problems, and I must have gotten it wrong on at least one account.

However, I'm still seeming to commit automatically.

[Sean Corfield]

Second bug in your code: (delete-scores-for-column db cid)

Should be: (delete-scores-for-column t cid)

Sorry I didn't see that (additional) bug first time around when I
suggested removing :transaction? from delete!

execute! does not call with-open if it can find an active connection
in the passed in db-spec. Inside a transaction, there is an active
connection. But if you pass the original db-spec instead of the
transaction-aware one, it will do what you ask (create a new
connection) instead of what you want (use the same connection).

Most of the bugs I see in java.jdbc usage code are the result of
overthinking and not just letting the library do its thing :)

Sean

[Mark Tomko]

Thanks Sean. I'll give it a try!

You received this message because you are subscribed to a topic in the Google Groups "Clojure" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/clojure/D2ccLAw7jaQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to clojure+u...@googlegroups.com.

[Mark Tomko]

Looks like that was a success. Thanks for the help!