From: | Alexandru Lazarev <alexandru(dot)lazarev(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Fwd: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |
Date: | 2016-03-09 19:39:12 |
Message-ID: | CAL93h0EBw_0TO__swrKW8K3rX8LbVG7Ebxb8e9j6g-pyYJ3CjA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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)
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-03-09 20:03:10 | Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |
Previous Message | Magnus Hagander | 2016-03-09 19:17:35 | Re: Email address VERP problems (was RE: Does a call to a language handler provide a context/session, and somewhere to keep session data? |
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-03-09 20:03:10 | Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |
Previous Message | Alexandru Lazarev | 2016-03-09 12:18:50 | How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |