Re: Exposing the Xact commit order to the user

On May 25, 2010, at 0:42 , Dan Ports wrote:
> On Mon, May 24, 2010 at 10:24:07AM -0500, Kevin Grittner wrote:
>> Jan Wieck wrote:
>>
>>> In some systems (data warehousing, replication), the order of
>>> commits is important, since that is the order in which changes
>>> have become visible.
>>
>> This issue intersects with the serializable work I've been doing.
>> While in database transactions using S2PL the above is true, in
>> snapshot isolation and the SSI implementation of serializable
>> transactions, it's not. In particular, the snapshot anomalies which
>> can cause non-serializable behavior happen precisely because the
>> apparent order of execution doesn't match anything so linear as
>> order of commit.
>
> All true, but this doesn't pose a problem in snapshot isolation. Maybe
> this is obvious to everyone else, but just to be clear: a transaction's
> snapshot is determined entirely by which transactions committed before
> it snapshotted (and hence are visible to it). Thus, replaying update
> transactions in the sae order on a slave makes the same sequence of
> states visible to it.

The subtle point here is whether you consider the view from the "outside" (in the sense of what a read-only transaction started at an arbitrary time can or cannot observe), or from the "inside" (what updating transactions can observe and might base their updates on).

The former case is completely determined by the commit ordering of the transactions, while the latter is not - otherwise serializability wouldn't be such a hard problem.

For some problems, like replication, the former ("outside") view is what matters - if slave synthesizes transactions that insert/update/delete the very same tuples as the original transaction did, and commits them in the same order, no read-only transaction can observe the difference. But that is *not* a serial schedule of the original transactions, since the transactions are *not* the same - the merely touch the same tuples. In fact, if you try replaying the original SQL, you *will* get different results on the slave, and not only because of now() and the like.

best regards,
Florian Pflug