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.