On Thu, Aug 23, 2012 at 02:02:41PM -0700, Evan Priestley wrote:
We solve this in Phabricator by using BEGIN (depth 0)
or SAVEPOINT
(depth 1+) when incrementing the counter, ROLLBACK TO SAVEPOINT (depth
1+) or ROLLBACK (depth 0) when decrementing it after a failure, and
nothing (depth 1) or COMMIT (depth 0) when decrementing it after a
success. Our experience with transaction stacks has generally been
good (no real surprises, doesn't feel magical, significantly reduces
the complexity of transactional code), although we don't support
anything but MySQL.
We do the same thing in our PostgreSQL-based app at my day job, although
for commit at depth > 0 we use RELEASE SAVEPOINT rather than doing
nothing. I don't think it makes much difference, though, beyond allowing
for the release of resources related to the savepoint itself.
FWIW, our savepoints are simply named along the lines of
"savepoint$depth". It's been working for us without issue for years.
On Thu, Aug 23, 2012 at 05:24:25PM -0400, Tyler Romeo wrote:
Also, as a matter of record, I just checked and the
SAVEPOINT command (or
an equivalent) is supported on SQLite, Postgresql, and mssql.
According to the PostgreSQL documentation (which is usually pretty good
about this sort of thing), it's standard SQL. So any sufficiently-new
(and sufficiently-good) SQL database should support it.
On Thu, Aug 23, 2012 at 11:30:20PM +0200, Platonides wrote:
However, I was told that it "might make us hold a lot of locks for much
too long". So with fear to cause magical db overload, nothing was
changed. :(
:(
Although it seems to me that avoiding that problem by making people have
to know whether the function they're calling is "safe" to call within a
transaction or not isn't the best idea.