Re: Repeatable read and serializable transactions see data committed after tx start

On Mon, Nov 3, 2014 at 2:14 PM, Álvaro Hernández Tortosa <aht(at)8kdata(dot)com> wrote:
> Given a transaction started with "BEGIN.... (REPEATABLE READ |
> SERIALIZABLE)", if a concurrent session commits some data before *any* query
> within the first transaction, that committed data is seen by the
> transaction. This is not what I'd expect.

I think the problem is with your expectation, not the behavior.
Serializable means that the transactions execute in such a fashion
that their parallel execution is equivalent to some serial order of
execution. It doesn't say that it must be equivalent to any
particular order that you might imagine, such as the order in which
the transactions commit, or, as you propose, the order in which they
begin. Generally, I think that's a good thing, because transaction
isolation is expensive: even at repeatable read, but for the need to
provide transaction isolation, there would be no such thing as bloat.
The repeatable read and serializable levels add further overhead of
various kinds. We could provide a super-duper serializable level that
provides even tighter guarantees, but (1) I can't imagine many people
are keen to make the cost of serialization even higher than it already
is and (2) if you really want that behavior, just do some trivial
operation sufficient to cause a snapshot to be taken immediately after
the BEGIN.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company