With per-DB-interaction checkin/checkout, it doesn’t appear to be possible to reliably manipulate these variables, as the connection used in the first statement may not match the one used in the second. More amusing / worrisome, somebody ELSE gets a connection with an altered SQL_MODE…
I would personally actually prefer to have to open and close a connection around variables like this, if only because it would serve as a reminder to set, say, SQL_MODE back to what it was; it only feels slightly harder in the current system to hand somebody else a connection with an altered SQL_MODE.
Other than the mental image that you’re using less resources (at the price of contention), I am not sure there is going to be any significant
practical win. It could be, I am not saying it wouldn’t (I have not done your study), but at first sight the cost/benefit is not clear to me
in practical terms.
It’s not so much the mental image that you’re using less resources as the actual ability to use less resources. The practical benefit is that for applications which don’t use the database to do all
their grunt work (which seems to be most of them; rendering seems to take at least as long as a DB round trip for at least most things), you don’t have to saturate your threads with database connections, which means you can scale your app further.
I guess my theory was that there would be negligibly more contention, and my research seems to
have backed that up, especially given the below. Happy to be proven wrong!
Regarding transactions, #execute is public AR interface, and
is valid AR code, I am not sure if drivers know the connection is in a transaction and have API to check, but #transaction_open? returns false as of this writing. Why would anybody do that? I don’t know, maybe because they are writing a heavy SQL oriented script and doing that manually feels natural… it doesn’t matter, it can be done.
This is certainly an issue; I’m not sure how to get around it without either connection-per-thread or #transaction_open?. It does feel like a bad thing to do, though; is keeping support for this really that important?