How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
От | Alexandru Lazarev |
---|---|
Тема | How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |
Дата | |
Msg-id | CAL93h0HEpaLHrYEr=p8dUnBnSR3k96eP+gQ4vS=psRRG1-1D-w@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: How does PostgreSQL serializable transaction works?
(SELECT/UPDATE vs INSERT conflict)
|
Список | 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 по дате отправления: