<div dir="ltr">Transaction 1 operated on set of data (`WHERE` clause) on which 2nd transaction do an `INSERT`, which
fitto clause from 1st transaction.<br />Shouldn't 1st transaction fail if 2nd commit first?<br /><br />I have following
table(in PostgreSQL 9.5 db)<br /><br />`CREATE TABLE public.foo (id serial PRIMARY KEY, mynum integer);`<br /><br />and
followingdata<br /><br /> id | mynum<br /> ----+-------<br /> 1 | 10<br /> 2 | 10<br /> 3
| 10<br /> 4 | 10<br /> (4 rows)<br /><br />I run 2 serialize transactions in parallel (2 `psql`
consoles):<br/><br /> -- both transactions<br /> mydb=# begin;<br /> BEGIN<br /> mydb=# set transaction
isolationlevel serializable;<br /> SET<br /> <br /> -- tx1<br /> mydb=# select * from foo where mynum <
100;<br/> id | mynum<br /> ----+-------<br /> 1 | 10<br /> 2 | 10<br /> 3 | 10<br />
4| 10<br /> (4 rows)<br /> --tx1: Shouldn't freeze data visible for tx1 select?<br /> <br />
--tx2<br/> mydb=# insert into foo (mynum) values (10);<br /> INSERT 0 1 <br /> -- tx2 will insert
nextrow with id 5 in foo table<br /> -- Shouldn't insert of tx2 broke data snapshot visible for tx1?<br />
<br/> --tx1<br /> mydb=# update foo set mynum = 20 where id < 100;<br /> UPDATE 4<br /> -- Shouldn't
hereappear serialization fail or at least on tx1 commit?<br /> <br /> --tx2 <br /> mydb=# commit;<br
/> COMMIT<br /> <br /> --tx1 <br /> mydb=# commit;<br /> COMMIT<br /> -- tx1 Commit is OK - no any
error<br/> <br /> -- implicit tx<br /> mydb=# select * from foo;<br /> id | mynum<br /> ----+-------<br
/> 1 | 20<br /> 2 | 20<br /> 3 | 20<br /> 4 | 20<br /> 5 | 10<br /> (4
rows)<br/><br />I am wondering why it behave so, taking in consideration PostgreSQL documentation <br /><br />> "To
guaranteetrue serializability PostgreSQL uses predicate locking,<br />> which means that it keeps locks which allow
itto determine when a<br />> write would have had an impact on the result of a previous read from a<br />>
concurrenttransaction, had it run first."<br />link: <a
href="http://www.postgresql.org/docs/current/static/transaction-iso.html">http://www.postgresql.org/docs/current/static/transaction-iso.html</a><br
/><br/><br /><br /><br /></div>