Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)

On Wed, Mar 9, 2016 at 5:18 AM, Alexandru Lazarev <
alexandru(dot)lazarev(at)gmail(dot)com> 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
> (4 rows)
>
> I am wondering why it behave so, taking in consideration PostgreSQL
> documentation
>
> > "To guarantee true serializability PostgreSQL uses predicate locking,
> > which means that it keeps locks which allow it to determine when a
> > write would have had an impact on the result of a previous read from a
> > concurrent transaction, had it run first."
> link: http://www.postgresql.org/docs/current/static/transaction-iso.html
>
>
​Next paragraph:

>"​
Predicate locks in PostgreSQL, like in most other database systems, are
based on data actually accessed by a transaction
​."

​i.e., the system doesn't keep a record of which where clauses are
presently in effect but only which rows have been seen.

​
​
​The promise of serializable is that the following will not occur:

"​
​
The result of successfully committing a group of transactions is
inconsistent with all possible orderings of running those transactions one
at a time.
​"​

​
But as long as at least a single possible serial ordering is consistent we
are fine - and since executing tx1 to completion and then executing tx2 to
completion will result in exactly the outcome you describe (5 rows, four of
which have been incremented) there is no violation.

​David J.