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. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers