Обсуждение: 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 >