Re: Exposing the Xact commit order to the user - Mailing list pgsql-hackers

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.

Of course (as in your example) some of these states could expose
snapshot isolation anomalies. But that's true on a single-replica
system too.


Now, stepping into the SSI world...

> Replicating or recreating the whole predicate locking and conflict
> detection on slaves is not feasible for performance reasons. (I
> won't elaborate unless someone feels that's not intuitively
> obvious.) The only sane way I can see to have a slave database allow
> serializable behavior is to WAL-log the acquisition of a snapshot by
> a serializable transaction, and the rollback or commit, on the
> master, and to have the serializable snapshot build on a slave
> exclude any serializable transactions for which there are still
> concurrent serializable transactions. Yes, that does mean WAL-
> logging the snapshot acquisition even if the transaction doesn't yet
> have an xid, and WAL-logging the commit or rollback even if it never
> acquires an xid.

One important observation is that any anomaly that occurs on the slave
can be resolved by aborting a local read-only transaction. This is a
good thing, because the alternatives are too horrible to consider.

You could possibly cut the costs of predicate locking by having the
master ship with each transaction the list of predicate locks it
acquired. But you'd still have to track locks for read-only
transactions, so maybe that's not a significant cost improvement. On
the other hand, if you're willing to pay the price of serializability
on the master, why not the slaves too?

Dan

-- 
Dan R. K. Ports              MIT CSAIL                http://drkp.net/