Re: User-facing aspects of serializable transactions - Mailing list pgsql-hackers

From Kevin Grittner
Subject Re: User-facing aspects of serializable transactions
Date
Msg-id 4A1EE22E.EE98.0025.1@wicourts.gov
Whole thread Raw
In response to Re: User-facing aspects of serializable transactions  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-hackers
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: 
> so as you haven't read other
> data, you would be safe in the particular case you cite.
Sorry, that's not true.  If you run your bulk data load at
serializable isolation level, you could still get rolled back in this
scenario, even if you're just writing:
(1)  A concurrent serializable transaction reads data from a table
into which your bulk load will subsequently insert, without there yet
being a conflict between that read and your bulk inserts.  It also
modifies data somewhere.  It commits.
(2)  A serializable transaction concurrent with that mentioned in (1)
reads data which conflicts with the modification of (1).  It may also
commit.
(3)  Your bulk data load eventually (after the commit of (1)) inserts
data which conflicts with the read from (1).  (1) is no longer
available for rollback.  Unless (2) is still active, and preferred for
termination based on the bias chosen, your bulk load will be rolled
back.
That would suck.
There are several protections available, like taking an explicit lock
to protect the process, running at a less rigorous transaction
isolation level, etc., but it could happen.
-Kevin


pgsql-hackers by date:

Previous
From: Greg Stark
Date:
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type
Next
From: Alvaro Herrera
Date:
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type