Re: Serializable Snapshot Isolation

[ Forgot the list, resending. ]

2010/9/25 Greg Stark <gsstark(at)mit(dot)edu>:

> On Thu, Sep 23, 2010 at 4:08 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> One place I'm particularly interested in using such a feature is in
>> pg_dump. Without it we have the choice of using a SERIALIZABLE
>> transaction, which might fail or cause failures (which doesn't seem
>> good for a backup program) or using REPEATABLE READ (to get current
>> snapshot isolation behavior), which might capture a view of the data
>> which contains serialization anomalies.
>
> I'm puzzled how pg_dump could possibly have serialization anomalies.
> Snapshot isolation gives pg_dump a view of the database containing all
> modifications committed before it started and no modifications which
> committed after it started. Since pg_dump makes no database
> modifications itself it can always just be taken to occur
> instantaneously before any transaction which committed after it
> started.

I guess that Kevin is referring to [1], where the dump would take the
role of T3. That would mean that the dump itself must be aborted
because it read inconsistent data.

AFAICS, whether that reasoning means that a dump can produce an
"inconsistent" backup is debatable. After restoring, all transactions
that would have been in-flight at the moment the dump took its
snapshot are gone, so none of their effects "happened". We would be in
exactly the same situation as if all running transactions would be
forcibly aborted at the moment that the dump would have started.

OTOH, if one would compare the backup with what really happened,
things may look inconsistent. The dump would show what T3 witnessed
(i.e., the current date is incremented and the receipts table is
empty), although the current state of the database system shows
otherwise (i.e., the current date is incremented and the receipts
table has an entry for the previous date).

IOW, one could say that the backup is consistent only if it were never
compared against the system as it continued running after the dump
took place.

This stuff will probably confuse the hell out of most DBAs :-).

Nicolas

[1] <URL:http://archives.postgresql.org/pgsql-hackers/2010-05/msg01360.php>