Fork me on GitHub
#sql
<
2023-09-24
>
Ben09:09:20

Hi. @seancorfield replied to a thread earlier this year about functions knowing whether they are participating in transactions (or starting their own if not) and said you can’t rely on auto commit being set to false to indicate whether you’re in a transaction “as there are sometimes other reasons to set it to false”. Could anyone expand on this? I’m not that familiar with JDBC and haven’t been able to find any examples by searching. Thanks in advance!

seancorfield16:09:06

One situation is when you're streaming large result sets: some databases will only actually stream results if auto commit is set false.

Ben16:09:34

Thanks for the example. Gives me something to read up on!

seancorfield17:09:23

Transactions are kind of weird since they don't really exist, per se. Once you turn off auto-commit on a single Connection, you can commit/rollback or savepoint/restore as much as you want but they don't nest, and they don't really have any concept of inherent "scope". I think they also give people a false sense of security in that they may have a block of code with a "transaction" but any other side effects created in that block are still auto-committed, so in a lot of real world situations, where you're working across multiple services (or even just multiple databases), they don't help. So I very rarely use them. I think about what side effects are happening in any piece of code and consider how to reverse those if possible or how to retry/fix the database operation that a transaction would otherwise rollback in the case of an error. Which means, to me, when you feel you need to ask "am I in a transaction?" you already have a code smell.

Ben18:09:29

I see your point. Coming from an http://ADO.NET world where transactions are “their own thing” (at least in so far as they are a separate object that controls commit/rollback) it obviously feels quite different. I think my usage of transactions will be limited (do as much in a single statement as possible and have code “over there” take care of the rest).