Transaction 1 operated 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