Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages]

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