Heikki Linnakangas wrote:
> It's not the order in which the xid was assigned that matters, but
> the order the transactions started and got their snapshots. The
> xids might be assigned a lot later, after the transactions have
> already read data.
From the "Apparent Serial Order of Execution" sub-section of the
Overview section of the Wiki page:
http://wiki.postgresql.org/wiki/Serializable#Apparent_Serial_Order_of_Execution
| The order in which transactions appear to have executed is
| determined by something more subtle than in S2PL: read/write
| dependencies. If a transaction attempts to read data which is not
| visible to it because the transaction which wrote it (or will later
| write it) is concurrent (one of them was running when the other
| acquired its snapshot), then the reading transaction appears to
| have executed first, regardless of the actual sequence of
| transaction starts or commits (since it sees a database state prior
| to that in which the other transaction leaves it).
It is the fact that it generates results consistent with some serial
order of execution which makes this truly serializable. With SSI
that order isn't tied to transaction start or transaction commit when
transactions overlap -- the one which *appears* to have run first
might have started first *and* committed first. And as Dan pointed
out with a repeatable example, a read only transaction can contribute
to a cycle in teh apparent order of execution, even if it commits
ahead of one of the writers.
There really are only two ways a read only transaction can be
guaranteed to see consistent data under SSI:
(1) A read write transaction might need to be cancelled to prevent
the view of the data a committed read only transaction has already
seen from becoming inconsistent. (Dan's example)
(2) The read only transaction needs to get a snapshot which the
master has determined is safe.
There really aren't any other alternatives under SSI. The question
is which we support on hot standbys and how we achieve it. Or
whether we just dont, I guess; but that doesn't seem very
satisfactory as a long term solution.
-Kevin