On Wed, Dec 14, 2016 at 9:05 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Robert Haas wrote: >> I have not read any database literature on the interaction of >> serializability with subtransactions. This seems very thorny. >> Suppose T1 reads A and B and updates A -> A' while concurrently T2 >> reads A and B and updates B -> B'. This is obviously not >> serializable; if either transaction had executed before the other in a >> serial schedule, the second transaction in the schedule would have had >> to have seen (A, B') or (A', B) rather than (A, B), but that's not >> what happened. But what if each of T1 and T2 did the reads in a >> subtransaction, rolled it back, and then did the write in the main >> transaction and committed? The database system has two options. >> First, it could assume that the toplevel transaction may have relied >> on the results of the aborted subtransaction. But if it does that, >> then any serialization failure which afflicts a subtransaction must >> necessarily also kill the main transaction, which seems pedantic and >> unhelpful. If you'd wanted the toplevel transaction to be killled, >> you wouldn't have used a subtransaction, right? Second, it could >> assume that the toplevel transaction in no way relied on or used the >> values obtained from the aborted subtransaction. However, that >> defeats the whole purpose of having subtransactions in the first >> place. What's the point of being able to start subtransactions if you >> can't roll them back and then decide to do something else instead? It >> seems to me that what the database system should do is make that >> second assumption, and what the user should do is understand that to >> the degree that transactions depend on the results of aborted >> subtransactions, there may be serialization anomalies that go >> undetected. > > Actually, Ian's sample transaction is even more malicious, because the > problem is not caused by reads within the aborted subtransaction -- the > cached-in-app reads occured *before* the subtransaction started in the > first place. I think saving a count(*) across a possibly-failed > insertion on the same table is wrong. I can't blame the database for > the behavior.
I don't see why it's wrong to cache a COUNT(*) across a possibly-failed substransaction; instead, I would argue that the problem is that by relying on the knowledge that the subtransaction failed while inserting A as a justification for inserting B, it's intrinsically doing something that it sensitive to concurrency. I understand that Ian --- and Kevin, and Thomas Munro --- would like that subtransaction to fail with serialization_failure rather than a unique_violation, and I previously argued that the change was fine but shouldn't be back-patched since it might break things for existing users. But people are continuing to show up and say "hey, this is broken", and now Kevin's chosen to back-patch, and I'm not going to kick up a fuss about that. After all, there's a growing number of people complaining about the same thing and saying it's a bug, so I guess it's a bug! But even after that fix, at the least, you'll still be able to demonstrate the same problem by trapping serialization_failure rather than unique_constraint. And even if you decree that trapping serialization_failure is off the table, I bet there are other ways for a supposedly-serializable transaction to sort of cheat, find out enough information about what's going on in the system to adjust its behavior, and then do something different based on that. And if that's possible and if a transaction finds a way to do it, then it's going to allow results not equivalent to any serial schedule. For example, imagine a transaction that queries pg_stat_activity or pg_locks and then makes decisions based on the contents thereof. That transaction is determined to behave different under concurrency than it does on an idle system, and even the ineluctable triumvirate of Kevin Grittner, Dan Ports, and Michael Cahill will not be able to prevent it from doing so. That's not a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers