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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
Дата
Msg-id CAKFQuwbHFiXPxCksi9OKx4bZe5+X_noNLsvV589T-fDwUNMqrQ@mail.gmail.com
обсуждение исходный текст
Ответ на How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)  (Alexandru Lazarev <alexandru.lazarev@gmail.com>)
Список pgsql-sql
On Wed, Mar 9, 2016 at 5:18 AM, Alexandru Lazarev <alexandru.lazarev@gmail.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.

В списке pgsql-sql по дате отправления:

Предыдущее
От: Alexandru Lazarev
Дата:
Сообщение: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
Следующее
От: Alexandru Lazarev
Дата:
Сообщение: Re: [GENERAL] Fwd: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)