On Wed, Mar 9, 2016 at 11:39 AM, Alexandru Lazarev <alexandru.lazarev@xxxxxxxxx> wrote: > > 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) What you are seeing here is exactly what you would see if tx1 started and ran to completion, and then tx2 started and ran to completion, and then the implicit tx started and ran to completion, in that order. Isn't it? If so, there is no serialization failure. Serializable means that there needs to be some serial ordering of the transactions which would result in the same overall outcome that actually occurred. It doesn't mean that the serial ordering which would produce that outcome has to be the same as the actual chronological commit order. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
