(No significant experience with either SQLAlchemy or ActiveRecord, but work in Java at a shop that really strongly emphasizes the unit-of-work pattern.)
"give up connection after every query" is, IMO, not _less_ of a footgun, but a _more subtle_ footgun. That is to say, sure, for the most part, it will work ok.
_But when it doesn't_... like when your database goes down for a couple hours, or your rack loses network, or your proxy to whatever cloud provider is running your database, or maybe some other dependency had an outage between the queries... you end up not really knowing what executed and what didn't, with some fraction of your 10k req/sec leaving behind some dangling state -- a partially processed $whatsit -- and no clear/easy way to fix it or even necessarily identify how many $whatsits were affected. At least a few times, I've needed to either wait until records make their way into some OLAP system for offline analysis, or deploy some fixup code, or emulate either/both to fix stuff up after something weird happens.
At least that's what I am constantly afraid of. Do you also worry about this kind of stuff? If not, what prevents it from being a problem in your environment?
Signed,
The guy constantly badgering seemingly everyone about minding their transaction boundaries.
From my experience, there’s always one developer in every enterprise dev shop who understands transaction boundaries, isolation levels, and concurrency/reliability scenarios that do happen....and 10 other devs constantly instilling fear in him/her
But he's not saying "don't use transactions." He's saying "Don't keep your transaction open for your entire user connection for no reason. Close your completed transactions as soon as you can."
Fun story: A coworker and I are in the process of tracking down exactly violations of this in a misbehaving app, due to an accumulation of RPC within transactions across the entire app. (I used to be responsible for it as a greenhorn ~5years ago, but walked away for the last several years to work in a different part of the org.)
The approach is actually kinda interesting: we add an interceptor in the RPC layer for every outgoing call that asks the DB machinery “am I currently in a transaction?”, and emits a warning (containing at least the RPC being made, perhaps a (partial) stack trace) if so. We had so many occurrences of those warnings that our logging framework proactively started dropping log lines! So the next step is to only emit the warnings once per $duration per message.
Just to hopefully further contribute, one of the top-deck features of the new Rails 6.0 release is its really thoughtful engineering work around multiple database support. At least, that's how it's named or described; the actual net result is a lot of smart people exhausting themselves making n-instance DB stack reliability and performance better on every chart... including setups where the value of n is 1.
What this translates to is that things should be impressively stable when your DB nodes go down, the backhoe shows up to party, or your BGP is routed to North Korea.
"give up connection after every query" is, IMO, not _less_ of a footgun, but a _more subtle_ footgun. That is to say, sure, for the most part, it will work ok.
_But when it doesn't_... like when your database goes down for a couple hours, or your rack loses network, or your proxy to whatever cloud provider is running your database, or maybe some other dependency had an outage between the queries... you end up not really knowing what executed and what didn't, with some fraction of your 10k req/sec leaving behind some dangling state -- a partially processed $whatsit -- and no clear/easy way to fix it or even necessarily identify how many $whatsits were affected. At least a few times, I've needed to either wait until records make their way into some OLAP system for offline analysis, or deploy some fixup code, or emulate either/both to fix stuff up after something weird happens.
At least that's what I am constantly afraid of. Do you also worry about this kind of stuff? If not, what prevents it from being a problem in your environment?
Signed,
The guy constantly badgering seemingly everyone about minding their transaction boundaries.