[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict)
From: "David G. Johnston" <david.g.johnston () gmail ! com>
Date: 2016-03-09 20:03:10
Message-ID: CAKFQuwbHFiXPxCksi9OKx4bZe5+X_noNLsvV589T-fDwUNMqrQ () mail ! gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif">On Wed, Mar 9, 2016 at 5:18 AM, Alexandru \
Lazarev </span><span dir="ltr" style="font-family:arial,sans-serif"><<a \
href="mailto:alexandru.lazarev@gmail.com" \
target="_blank">alexandru.lazarev@gmail.com</a>></span><span \
style="font-family:arial,sans-serif"> wrote:</span><br></div><div \
class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px \
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div \
dir="ltr">Transaction 1 operated on set of data (`WHERE` clause) on which 2nd \
transaction do an `INSERT`, which fit to clause from 1st \
transaction.<br>Shouldn't 1st transaction fail if 2nd commit first?<br><br>I have \
following table (in PostgreSQL 9.5 db)<br><br>`CREATE TABLE public.foo (id serial \
PRIMARY KEY, mynum integer);`<br><br>and following data<br><br> id | \
mynum<br> ----+-------<br> 1 | 10<br> 2 | \
10<br> 3 | 10<br> 4 | 10<br> (4 rows)<br><br>I \
run 2 serialize transactions in parallel (2 `psql` consoles):<br><br> -- both \
transactions<br> mydb=# begin;<br> BEGIN<br> mydb=# set transaction \
isolation level serializable;<br> SET<br> <br> -- tx1<br> \
mydb=# select * from foo where mynum < 100;<br> id | mynum<br> \
----+-------<br> 1 | 10<br> 2 | 10<br> 3 | \
10<br> 4 | 10<br> (4 rows)<br> --tx1: Shouldn't \
freeze data visible for tx1 select?<br> <br> --tx2<br> \
mydb=# insert into foo (mynum) values (10);<br> INSERT 0 1 <br> \
-- tx2 will insert next row with id 5 in foo table<br> -- Shouldn't \
insert of tx2 broke data snapshot visible for tx1?<br> <br> --tx1<br> \
mydb=# update foo set mynum = 20 where id < 100;<br> UPDATE 4<br> -- \
Shouldn't here appear serialization fail or at least on tx1 commit?<br> \
<br> --tx2 <br> mydb=# commit;<br> \
COMMIT<br> <br> --tx1 <br> mydb=# commit;<br> COMMIT<br> \
-- tx1 Commit is OK - no any error<br> <br> -- implicit tx<br> \
mydb=# select * from foo;<br> id | mynum<br> ----+-------<br> 1 \
| 20<br> 2 | 20<br> 3 | 20<br> 4 | \
20<br> 5 | 10<br> (4 rows)<br><br>I am wondering why it behave \
so, taking in consideration PostgreSQL documentation <br><br>> "To guarantee \
true serializability PostgreSQL uses predicate locking,<br>> which means that it \
keeps locks which allow it to determine when a<br>> write would have had an impact \
on the result of a previous read from a<br>> concurrent transaction, had it run \
first."<br>link: <a \
href="http://www.postgresql.org/docs/current/static/transaction-iso.html" \
target="_blank">http://www.postgresql.org/docs/current/static/transaction-iso.html</a><br><br></div></blockquote><div><br></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">Next \
paragraph:</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">>"</div>Predicate \
locks in PostgreSQL, like in most other database systems, are based on data actually \
accessed by a transaction<div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">."</div></div><div><span \
style="font-family:arial,helvetica,sans-serif"><br></span></div><div><span \
style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">i.e., the system \
doesn't keep a record of which where clauses are presently in effect but only \
which rows have been seen.</div></span></div><div><span \
style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></span></div><div><span \
style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"></div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">The promise of \
serializable is that the following will not occur:</div></span></div><div><span \
style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></span></div><div><span \
style="font-family:arial,helvetica,sans-serif"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">"</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"></div></span><font \
face="arial, helvetica, sans-serif">The result of successfully committing a group of \
transactions is inconsistent with all possible orderings of running those \
transactions one at a time.<div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">"</div></font><br></div></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">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.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">David J.</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic