In the example below, client #1 and client #2 are not doing anything
that can possibly cause a inconsistent serial order of execution. You
can pick and choose whichever one you want to run first, the final
result will be 100% consistent and exactly the same, either way.
So why does postgres 9.3.6 give an error to the second client ? Unless I
am reading this docs wrong, this looks like a major bug.
Best,
-H
=============== Initial setup ==========
test# create table test (id int, value text);
CREATE TABLE
test# insert into test (id, value) values (1, 'a');
INSERT 0 1
=============== Client #1 ==========
test# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
test# select * from test where id = 1;
id | value
----+-------
1 | a
(1 row)
test=# insert into test (id, value) values (1, 'b');
INSERT 0 1
test=# commit;
COMMIT
========== Client #2 (concurrently with client #1) ==========
test# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
test# select * from test where id = 2;
id | value
----+-------
(0 rows)
test# insert into test (id, value) values (2, 'xxx');
INSERT 0 1
test# commit;
ERROR: could not serialize access due to read/write dependencies among
transactions
DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT: The transaction might succeed if retried.