Re: [GENERAL] Fwd: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) - Mailing list pgsql-sql

On Wed, Mar 9, 2016 at 1:39 PM, Alexandru Lazarev
<alexandru.lazarev@gmail.com> wrote:

Jeff's answer is entirely correct; I'm just going to go into more
detail -- just in case you're interested enough to work through it.

> `CREATE TABLE public.foo (id serial PRIMARY KEY, mynum integer);`
>
> and following data
>
>      id | mynum
>     ----+-------
>       1 |    10
>       2 |    10
>       3 |    10
>       4 |    10
>     (4 rows)
>
> I run 2 serialize transactions in parallel (2 `psql` consoles):
>
>     -- both transactions
>     mydb=# begin;
>     BEGIN
>     mydb=# set transaction isolation level serializable;
>     SET
>
>     -- tx1
>     mydb=# select * from foo where mynum < 100;
>     id | mynum
>     ----+-------
>       1 |    10
>       2 |    10
>       3 |    10
>       4 |    10
>     (4 rows)
>     --tx1: Shouldn't freeze data visible for tx1 select?

Yes, tx1 does have a snapshot which will guarantee that it sees a
repeatable set view of the data for this predicate.

>         --tx2
>         mydb=# insert into foo (mynum) values (10);
>         INSERT 0 1
>         -- tx2 will insert next row with id 5 in foo table
>         -- Shouldn't insert of tx2 broke data snapshot visible for tx1?

The snapshot tx1 has guarantees that overlapping changes won't
change it's view of things, and there is no reason for anything to
be blocked or canceled here.  The insert creates what is called a
read-write dependency (or rw-conflict for short) that establishes
that in any serial ordering of a set of transactions which includes
tx1 and tx2, tx1 must precede tx2 in the apparent order of
execution.

>     --tx1
>     mydb=# update foo set mynum = 20 where id < 100;
>     UPDATE 4
>     -- Shouldn't here appear serialization fail or at least on tx1 commit?

No, there is no cycle in the apparent order of execution.  The
snapshot for tx1 still limits it to the same set of rows, and there
is nothing visible that is inconsistent with tx1 running before
tx2.

>         --tx2
>         mydb=# commit;
>         COMMIT
>
>     --tx1
>     mydb=# commit;
>     COMMIT
>     -- tx1 Commit is OK - no any error

According to the SQL standard, and in the PostgreSQL implementation
of SERIALIZABLE transactions, commit order does not, by itself,
establish apparent  order of execution.

>     -- implicit tx
>     mydb=# select * from foo;
>     id | mynum
>     ----+-------
>       1 |    20
>       2 |    20
>       3 |    20
>       4 |    20
>       5 |    10
>     (5 rows)

As Jeff said, this is consistent with the implicit transaction
running last, so tx1 -> tx2 -> implicit_tx.

Now, you are pretty close to a situation which does need to trigger
a serialization failure -- just switch the commit of tx1 and the
implicit transaction.  If tx2 has committed but tx1 has not yet
committed:

mydb=# select * from foo;
 id | mynum
----+-------
  1 |    10
  2 |    10
  3 |    10
  4 |    10
  5 |    10
(5 rows)

*Now* we have a problem -- this only makes sense if the implicit tx
was run after tx2 and before tx1.  So apparent order of execution
is tx1 -> tx2 -> implicit_tx -> tx1.  There is a cycle in the
apparent order of execution, which causes anomalies which can ruin
data integrity.  Now, if the implicit transaction is not
serializable, it is allowed to see such things, but if you make it
serializable (and let's call it tx3 now) it sees a state where only
tx2 ran; tx1 could not have run:

-- tx3
mydb=# begin;
BEGIN
mydb=# set transaction isolation level serializable;
SET
mydb=# select * from foo;
 id | mynum
----+-------
  1 |    10
  2 |    10
  3 |    10
  4 |    10
  5 |    10
(5 rows)

mydb=# commit;
COMMIT

So now, tx1 is not allowed to commit, or for that matter do
anything else -- it has been "doomed" by tx3:

mydb=# select * from foo;
ERROR:  could not serialize access due to read/write dependencies
among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during
conflict out checking.
HINT:  The transaction might succeed if retried.

Hopefully you are using some framework to automatically detect this
SQLSTATE and retry the transaction from the start.  So on retry,
tx1 does this:

-- tx1 (retry)
mydb=# begin;
BEGIN
mydb=# set transaction isolation level serializable;
SET
mydb=# select * from foo where mynum < 100;
 id | mynum
----+-------
  1 |    10
  2 |    10
  3 |    10
  4 |    10
  5 |    10
(5 rows)

mydb=# update foo set mynum = 20 where id < 100;
UPDATE 5
mydb=# select * from foo;
 id | mynum
----+-------
  1 |    20
  2 |    20
  3 |    20
  4 |    20
  5 |    20
(5 rows)

mydb=# commit;
COMMIT

Now the result of all successfully committed serializiable
transactions is consistent with the order tx2 -> tx3 -> tx1.  All
is good.

Kevin Grittner