Обсуждение: BUG #13524: Not Getting expected Serialization error

The following bug has been logged on the website:

Bug reference:      13524
Logged by:          Robert Rullo
Email address:      bobby.rullo@coreos.com
PostgreSQL version: 9.4.1
Operating system:   Mac OS X
Description:

Because this requires concurrent transactions, we will consider two
different sequences of SQL statements, each to be executed in PSQL against
the same database.

Sequence 1:
=========
drop table if exists "t1";
create table if not exists "t1" ("id" text not null primary key, "name"
text, "email" text) ;
begin;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select count(*)  from "t1" where email = 'bill@example.com';
insert into "t1" ("id","name","email") values
('1','bill','bill@example.com');
-- EXECUTE sequence1.txt HERE
commit;

Sequence 2:
==========
begin;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--select count(*)  from "t1" where email = 'bill@example.com'; --UNCOMMENT
ME LATER
insert into "t1" ("id","name","email") values
('2','william','bill@example.com');
commit;


To reproduce:
1) execute statements in sequence 1 on a connection, stopping at the
"--EXECUTE" statement.
2) In another connection, execute all the statements in Sequence 2.
3) Back in the first connection execute the remaining statement (the
"commit;")

Expected:
error when trying to commit, eg. "ERROR: could not serialize"

Actual:
Both commits succeed.

Interesting tidbit:
If you do the exact steps as above but uncomment the "select count(*)" in
sequence 2, you get the expected error.

bobby.rullo@coreos.com writes:
> To reproduce:
> 1) execute statements in sequence 1 on a connection, stopping at the
> "--EXECUTE" statement.
> 2) In another connection, execute all the statements in Sequence 2.
> 3) Back in the first connection execute the remaining statement (the
> "commit;")

> Expected:
> error when trying to commit, eg. "ERROR: could not serialize"

> Actual:
> Both commits succeed.

> Interesting tidbit:
> If you do the exact steps as above but uncomment the "select count(*)" in
> sequence 2, you get the expected error.

This seems perfectly fine to me.  Without the "select count(*)" in
transaction 2, there is a valid serialization of the transactions,
ie t1 before t2.  Serializability is *not* defined as "the transactions
appear to have executed in the order they were started (or committed)".
It is defined as "there is some consistent order in which they appear
to have executed".

            regards, tom lane

That took me a while to wrap my head around but it makes sense now. Thanks
for the succinct explanation!

Bobby

On Wed, Jul 29, 2015 at 8:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> bobby.rullo@coreos.com writes:
> > To reproduce:
> > 1) execute statements in sequence 1 on a connection, stopping at the
> > "--EXECUTE" statement.
> > 2) In another connection, execute all the statements in Sequence 2.
> > 3) Back in the first connection execute the remaining statement (the
> > "commit;")
>
> > Expected:
> > error when trying to commit, eg. "ERROR: could not serialize"
>
> > Actual:
> > Both commits succeed.
>
> > Interesting tidbit:
> > If you do the exact steps as above but uncomment the "select count(*)" in
> > sequence 2, you get the expected error.
>
> This seems perfectly fine to me.  Without the "select count(*)" in
> transaction 2, there is a valid serialization of the transactions,
> ie t1 before t2.  Serializability is *not* defined as "the transactions
> appear to have executed in the order they were started (or committed)".
> It is defined as "there is some consistent order in which they appear
> to have executed".
>
>                         regards, tom lane
>