On Wed, Mar 9, 2016 at 1:39 PM, Alexandru Lazarev <alexandru.lazarev@xxxxxxxxx> wrote: Jeff's answer is entirely correct; I'm just going to go into more detail -- just in case you're interested enough to work through it. > `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? Yes, tx1 does have a snapshot which will guarantee that it sees a repeatable set view of the data for this predicate. > --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? The snapshot tx1 has guarantees that overlapping changes won't change it's view of things, and there is no reason for anything to be blocked or canceled here. The insert creates what is called a read-write dependency (or rw-conflict for short) that establishes that in any serial ordering of a set of transactions which includes tx1 and tx2, tx1 must precede tx2 in the apparent order of execution. > --tx1 > mydb=# update foo set mynum = 20 where id < 100; > UPDATE 4 > -- Shouldn't here appear serialization fail or at least on tx1 commit? No, there is no cycle in the apparent order of execution. The snapshot for tx1 still limits it to the same set of rows, and there is nothing visible that is inconsistent with tx1 running before tx2. > --tx2 > mydb=# commit; > COMMIT > > --tx1 > mydb=# commit; > COMMIT > -- tx1 Commit is OK - no any error According to the SQL standard, and in the PostgreSQL implementation of SERIALIZABLE transactions, commit order does not, by itself, establish apparent order of execution. > -- implicit tx > mydb=# select * from foo; > id | mynum > ----+------- > 1 | 20 > 2 | 20 > 3 | 20 > 4 | 20 > 5 | 10 > (5 rows) As Jeff said, this is consistent with the implicit transaction running last, so tx1 -> tx2 -> implicit_tx. Now, you are pretty close to a situation which does need to trigger a serialization failure -- just switch the commit of tx1 and the implicit transaction. If tx2 has committed but tx1 has not yet committed: mydb=# select * from foo; id | mynum ----+------- 1 | 10 2 | 10 3 | 10 4 | 10 5 | 10 (5 rows) *Now* we have a problem -- this only makes sense if the implicit tx was run after tx2 and before tx1. So apparent order of execution is tx1 -> tx2 -> implicit_tx -> tx1. There is a cycle in the apparent order of execution, which causes anomalies which can ruin data integrity. Now, if the implicit transaction is not serializable, it is allowed to see such things, but if you make it serializable (and let's call it tx3 now) it sees a state where only tx2 ran; tx1 could not have run: -- tx3 mydb=# begin; BEGIN mydb=# set transaction isolation level serializable; SET mydb=# select * from foo; id | mynum ----+------- 1 | 10 2 | 10 3 | 10 4 | 10 5 | 10 (5 rows) mydb=# commit; COMMIT So now, tx1 is not allowed to commit, or for that matter do anything else -- it has been "doomed" by tx3: mydb=# select * from foo; ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking. HINT: The transaction might succeed if retried. Hopefully you are using some framework to automatically detect this SQLSTATE and retry the transaction from the start. So on retry, tx1 does this: -- tx1 (retry) mydb=# begin; BEGIN mydb=# set transaction isolation level serializable; SET mydb=# select * from foo where mynum < 100; id | mynum ----+------- 1 | 10 2 | 10 3 | 10 4 | 10 5 | 10 (5 rows) mydb=# update foo set mynum = 20 where id < 100; UPDATE 5 mydb=# select * from foo; id | mynum ----+------- 1 | 20 2 | 20 3 | 20 4 | 20 5 | 20 (5 rows) mydb=# commit; COMMIT Now the result of all successfully committed serializiable transactions is consistent with the order tx2 -> tx3 -> tx1. All is good. Kevin Grittner -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
