How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) - Mailing list pgsql-sql

From Alexandru Lazarev
Subject How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
Date
Msg-id CAL93h0HEpaLHrYEr=p8dUnBnSR3k96eP+gQ4vS=psRRG1-1D-w@mail.gmail.com
Whole thread Raw
Responses Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
List pgsql-sql
<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> 

pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Query about foreign key details for php framework
Next
From: "David G. Johnston"
Date:
Subject: Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)