How does SQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)

Question

Transaction#1 operates on set of data (WHERE clause) on which 2nd transaction do an INSERT, which fit to clause from 1st transaction. Shouldn't 1st transaction fail if 2nd commit first?

I have following table (in PostgreSQL 9.5 db)

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?

    --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?

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

    --tx2 
    mydb=# commit;
    COMMIT

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

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

I am wondering why it behave so, taking in consideration PostgreSQL documentation

"To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first." link: http://www.postgresql.org/docs/current/static/transaction-iso.html

Nick Barnes Nick Barnes · Accepted Answer · 2016-03-10T15:13:01

SERIALIZABLE guarantees that there is some serial execution order for concurrent transactions which would produce the same outcome, but this is not necessarily the same as the commit order. In your case, sequentially running tx1 followed by tx2 would give you the same result, so there is no reason for a serialisation failure.

Two overlapping transactions will never be allowed to see each other's data (the MVCC system ensures that new records are ignored by SELECT/UPDATE statements). Serialisation failures arise when they should have seen each other's data, i.e. when either serial ordering would cause the second transaction to see records written by the first.

The key point here is that this can only happen if both transactions read something and write something; otherwise, it will always be possible to serialise them by putting the read-only transaction first, or the write-only transaction last. So your lone insert will never conflict with anything (but if tx2 also contained SELECT * FROM foo, then you would receive an error).

Things are not so simple when more transactions are involved. There is a long list of examples here which might provide some more insight into when and where serialisation anomalies happen.

How does SQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)

1 Answers