Ants Aasma <a...@cybertec.at> wrote: > Serializable transactions > ------------------------- > > I won't pretend to be familiar with SSI code, but as far as I can > tell serializable transactions don't need any modifications to > work with the CSN based snapshot scheme. There actually already > is a commit sequence number in the SSI code that could be > replaced by the actual CSN.
That seems quite likely to work, and may be good for performance. > IIRC one of the problems with serializable transactions on hot > standby was that transaction visibility order on the standby is > different from the master. Pretty much. Technically what SSI does is to ensure that every serializable transaction's view of the data is consistent with some serial (one-at-a time) exection of serializable transactions. That "apparent order of execution" does not always match commit order. The problem is that without further work a hot standby query could see a state which would not have been possible for it to see on the master. For example, a batch is closed but a transaction has not yet committed which is part of that batch. For an example, see: http://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions As that example demonstrates, as long as no serializable transaction *sees* the incomplete batch with knowledge (or at least potential knowledge) of the batch being closed, the pending transaction affecting the batch is allowed to commit. If the conflicting state is exposed by even a read-only query, the transaction with the pending change to the batch is canceled. A hot standby can't cancel the pending transaction -- at least not without adding additional communications channels and latency. The ideas which have been bandied about have had to do with allowing serializable transactions on a hot standby to use snapshots which are known to be "safe" -- that is, they cannot expose any such state. It might be possible to identify known safe points in the commit stream on the master and pass that information along in the WAL stream. The down side is that the hot standby would need to either remember the last such safe snapshot or wait for the next one, and while these usually come up fairly quickly in most workloads, there is no actual bounding on how long that could take. A nicer solution, if we can manage it, is to allow snapshots on the hot standby which are not based exclusively on the commit order, but use the apparent order of execution from the master. It seems non-trivial to get that right. > If we use CSNs for visibility on the slave then we can actually > provide identical visibility order. As the above indicates, that's not really true without using "apparent order of execution" instead of "commit order". In the absence of serializable transactions those are always the same (I think), but to provide a way to allow serializable transactions on the hot standby the order would need to be subject to rearrangement based on read-write conflicts among transactions on the master, or snapshots which could expose serialization anomalies would need to be prohibited. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
