Re: Serializable Snapshot Isolation

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

> Great work! A year ago I thought it would be impossible to have a
> true serializable mode in PostgreSQL because of the way we do
> MVCC, and now we have a patch.
>
> At a quick read-through, the code looks very tidy and clear now.
> Some comments:
>
> Should add a citation to Cahill's work this is based on.
> Preferably with a hyperlink.

I'm planning on drawing from the current Wiki page:

http://wiki.postgresql.org/wiki/Serializable

to put together a README file; do you think the references should go
in the README file, the source code, or both?

> A short description of how the predicate locks help to implement
> serializable mode would be nice too. I haven't read Cahill's
> papers, and I'm left wondering what the RW conflicts and
> dependencies are, when you're supposed to grab predicate locks
> etc.

Again, I summarize that in the Wiki page, and was planning on
putting it into the README. If you've read the Wiki page and it's
not clear, then I definitely have some work to do there.

> If a page- or relation level SILOCK is taken, is it possible to
> get false conflicts?

Yes. This technique will generate some false positive rollbacks.
Software will need to be prepared to retry any database transaction
which fails with a serialization failure SQLSTATE. I expect that
proper connection pooling will be particularly important when using
SSI, and flagging transactions which don't write to permanent tables
as READ ONLY transactions will help reduce the rollback rate, too.

Some of the optimizations we have sketched out will definitely
reduce the rate of false positives; however, we don't want to
implement them without a better performance baseline because the
cost of tracking the required information and the contention for LW
locks to maintain the information may hurt performance more than the
restart of transactions which experience serialization failure.

I don't want to steal Dan's thunder after all the hard work he's
done to get good numbers from the DBT-2 benchmark, but suffice it to
say that I've been quite pleased with the performance on that
benchmark. He's pulling together the data for a post on the topic.

> Ie. a transaction is rolled back because it modified a tuple on a
> page where some other transaction modified another tuple, even
> though there's no dependency between the two.

Well, no, because this patch doesn't do anything new with write
conflicts. It's all about the apparent order of execution, based on
one transaction not being able to read what was written by a
concurrent transaction. The reading transaction must be considered
to have run first in that case (Hey, now you know what a rw-conflict
is!) -- but such references can create a cycle -- which is the
source of all serialization anomalies in snapshot isolation.

-Kevin