Re: Question About Serializable

Aaron Carlisle <aaron(dot)carlisle(at)gmail(dot)com> wrote:

> The documentation states that "concurrent execution of a set of
> Serializable transactions is guaranteed to produce the same
> effect as running them one at a time in some order."
>
> I'm not sure how the following behavior fits that definition.
> (Note that this is just an experiment, not a use case. Purely
> academic.) I run these transactions sequentially, and I get a
> different result than if I run them concurrently.

> It seems like one of these should error out and not commit, so I
> must be missing some stipulation.

> [ write skew in pg_class with table creation and check for table
> existence in two concurrent queries ]

You are correct, and not missing anything -- when serializable
behavior was added (and through 9.3) the system catalogs were not
fully transactional using the MVCC semantics.  I'm afraid there
were many strange things that could happen with the system
catalogs, including having an object which was being updated be
missed or seen twice by a concurrent transaction, none of which was
documented.  Since the serializable techniques in PostgreSQL are
based on the MVCC snapshot isolation techniques, it was not
possible to cover DDL in the serializable implementation using
Serializable Snapshot Isolation.

I assume you can't create such problems in a stable schema; but
only when serializable transactions include DDL?

On the 9.4 development branch access to the system catalogs has
recently been made to use MVCC techniques, so it may be possible to
extend serializable behavior to the catalogs in 9.4 or later.

I don't suppose you want to help develop a patch for that?  :-)

Out of curiosity, is there a particular academic effort this
question came out of that you can talk about?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company