Re: Re: How to reproduce serialization failure for a read only transaction.

AK <alkuzo(at)gmail(dot)com> wrote:

> Session 1. Setting up:
>
> CREATE TABLE cars(
>   license_plate VARCHAR NOT NULL,
>   reserved_by VARCHAR NULL
> );
> INSERT INTO cars(license_plate)
> VALUES ('SUPRUSR'),('MIDLYPH');
>
> Session 2: W1
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
>
> UPDATE cars SET reserved_by = 'Julia'
>   WHERE license_plate = 'SUPRUSR'
>   AND reserved_by IS NULL;
>
> SELECT * FROM Cars
> WHERE license_plate IN('SUPRUSR','MIDLYPH');
>
> Session 3: W2
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
>
> UPDATE cars SET reserved_by = 'Ryan'
>   WHERE license_plate = 'MIDLYPH'
>   AND reserved_by IS NULL;
>
> COMMIT;
>
> Session 4: R
>
> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY;
>
> SELECT * FROM Cars
> WHERE license_plate IN('SUPRUSR','MIDLYPH');
>
> Session 2: W1
>
> COMMIT;
>
> ERROR:  could not serialize access due to read/write dependencies
>         among transactions
>
> What am I doing wrong?

Even without the read only transaction the W1 and W2 transactions
are a classic case of write skew.  It looks like it might actually
be benign, since neither transaction is updating license_plate, but
serializable logic works at the row level, not the column level.
After both transactions update the table there is write skew which
must be resolved by cancelling one of the transactions.  The first
to commit "wins" and the other one will be cancelled when it
attempts to run its next statement, which may or may not be a
COMMIT.

If, for purposes of demonstration, you add a unique index on
license_plate and set enable_seqscan = off, you eliminate the
simple write skew and get into more complex ways of breaking
things.  With that tweak you can run all of those transactions if
W1 skips the SELECT.  You can let W1 do the SELECT as long as you
don't run R.  The problem is that the SELECT in W1 sees the work of
W1 but not W2 and the SELECT in R sees the work of W2 but not W1.
We can't allow that.

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