title: How does SQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
body: <p>Transaction#1 operates on set of data (<code>WHERE</code> clause) on which 2nd transaction do an <code>INSERT</code>, which fit to clause from 1st transaction.
  Shouldn't 1st transaction fail if 2nd commit first?</p>
  
  <p>I have following table (in PostgreSQL 9.5 db)</p>
  
  <p><code>CREATE TABLE public.foo (id serial PRIMARY KEY, mynum integer);</code></p>
  
  <p>and following data</p>
  
  <pre><code> id | mynum
  ----+-------
    1 |    10
    2 |    10
    3 |    10
    4 |    10
  (4 rows)
  </code></pre>
  
  <p>I run 2 serialize transactions in parallel (2 <code>psql</code> consoles):</p>
  
  <pre><code>-- both transactions
  mydb=# begin;
  BEGIN
  mydb=# set transaction isolation level serializable;
  SET
  
  -- tx1
  mydb=# select * from foo where mynum &lt; 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 &lt; 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)
  </code></pre>
  
  <p>I am wondering why it behave so, taking in consideration PostgreSQL documentation </p>
  
  <blockquote>
    <p>"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: <a href="http://www.postgresql.org/docs/current/static/transaction-iso.html" rel="nofollow">http://www.postgresql.org/docs/current/static/transaction-iso.html</a></p>
  </blockquote>