8000 Back-patch fcff8a575198478023ada8a48e13b50f70054766 as a bug fix. · iakio/postgres@60314e2 · GitHub
[go: up one dir, main page]
Skip to content

Commit 60314e2

Browse files
kgrittnkgrittn
committed
Back-patch fcff8a5 as a bug fix.
When there is both a serialization failure and a unique violation, throw the former rather than the latter. When initially pushed, this was viewed as a feature to assist application framework developers, so that they could more accurately determine when to retry a failed transaction, but a test case presented by Ian Jackson has shown that this patch can prevent serialization anomalies in some cases where a unique violation is caught within a subtransaction, the work of that subtransaction is discarded, and no error is thrown. That makes this a bug fix, so it is being back-patched to all supported branches where it is not already present (i.e., 9.2 to 9.5). Discussion: https://postgr.es/m/1481307991-16971-1-git-send-email-ian.jackson@eu.citrix.com Discussion: https://postgr.es/m/22607.56276.807567.924144@mariner.uk.xensource.com
1 parent 2d48131 commit 60314e2

File tree

11 files changed

+307
-7
lines changed

11 files changed

+307
-7
lines changed

‎doc/src/sgml/mvcc.sgml

Lines changed: 28 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -567,7 +567,7 @@ ERROR: could not serialize access due to read/write dependencies among transact
567567
first. In <productname>PostgreSQL</productname> these locks do not
568568
cause any blocking and therefore can <emphasis>not</> play any part in
569569
causing a deadlock. They are used to identify and flag dependencies
570-
among concurrent serializable transactions which in certain combinations
570+
among concurrent Serializable transactions which in certain combinations
571571
can lead to serialization anomalies. In contrast, a Read Committed or
572572
Repeatable Read transaction which wants to ensure data consistency may
573573
need to take out a lock on an entire table, which could block other
@@ -602,12 +602,13 @@ ERROR: could not serialize access due to read/write dependencies among transact
602602

603603
<para>
604604
Consistent use of Serializable transactions can simplify development.
605-
The guarantee that any set of concurrent serializable transactions will
606-
have the same effect as if they were run one at a time means that if
607-
you can demonstrate that a single transaction, as written, will do the
608-
right thing when run by itself, you can have confidence that it will
609-
do the right thing in any mix of serializable transactions, even without
610-
any information about what those other transactions might do. It is
605+
The guarantee that any set of successfully committed concurrent
606+
Serializable transactions will have the same effect as if they were run
607+
one at a time means that if you can demonstrate that a single transaction,
608+
as written, will do the right thing when run by itself, you can have
609+
confidence that it will do the right thing in any mix of Serializable
610+
transactions, even without any information about what those other
611+
transactions might do, or it will not successfully commit. It is
611612
important that an environment which uses this technique have a
612613
generalized way of handling serialization failures (which always return
613614
with a SQLSTATE value of '40001'), because it will be very hard to
@@ -621,6 +622,26 @@ ERROR: could not serialize access due to read/write dependencies among transact
621622
for some environments.
622623
</para>
623624

625+
<para>
626+
While <productname>PostgreSQL</>'s Serializable transaction isolation
627+
level only allows concurrent transactions to commit if it can prove there
628+
is a serial order of execution that would produce the same effect, it
629+
doesn't always prevent errors from being raised that would not occur in
630+
true serial execution. In particular, it is possible to see unique
631+
constraint violations caused by conflicts with overlapping Serializable
632+
transactions even after explicitly checking that the key isn't present
633+
before attempting to insert it. This can be avoided by making sure
634+
that <emphasis>all</> Serializable transactions that insert potentially
635+
conflicting keys explicitly check if they can do so first. For example,
636+
imagine an application that asks the user for a new key and then checks
637+
that it doesn't exist already by trying to select it first, or generates
638+
a new key by selecting the maximum existing key and adding one. If some
639+
Serializable transactions insert new keys directly without following this
640+
protocol, unique constraints violations might be reported even in cases
641+
where they could not occur in a serial execution of the concurrent
642+
transactions.
643+
</para>
644+
624645
<para>
625646
For optimal performance when relying on Serializable transactions for
626647
concurrency control, these issues should be considered:

‎src/backend/access/nbtree/nbtinsert.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -369,6 +369,14 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
369369
break;
370370
}
371371

372+
/*
373+
* Check for a conflict-in as we would if we were going to
374+
* write to this page. We aren't actually going to write,
375+
* but we want a chance to report SSI conflicts that would
376+
* otherwise be masked by this unique constraint violation.
377+
*/
378+
CheckForSerializableConflictIn(rel, NULL, buf);
379+
372380
/*
373381
* This is a definite conflict. Break the tuple down into
374382
* datums and report the error. But first, make sure we

‎src/test/isolation/expected/read-write-unique-2.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: r1 r2 w1 w2 c1 c2
4+
step r1: SELECT * FROM test WHERE i = 42;
5+
i
6+
7+
step r2: SELECT * FROM test WHERE i = 42;
8+
i
9+
10+
step w1: INSERT INTO test VALUES (42);
11+
step w2: INSERT INTO test VALUES (42); <waiting ...>
12+
step c1: COMMIT;
13+
step w2: <... completed>
14+
error in steps c1 w2: ERROR: could not serialize access due to read/write dependencies among transactions
15+
step c2: COMMIT;
16+
17+
starting permutation: r1 w1 c1 r2 w2 c2
18+
step r1: SELECT * FROM test WHERE i = 42;
19+
i
20+
21+
step w1: INSERT INTO test VALUES (42);
22+
step c1: COMMIT;
23+
step r2: SELECT * FROM test WHERE i = 42;
24+
i
25+
26+
42
27+
step w2: INSERT INTO test VALUES (42);
28+
ERROR: duplicate key value violates unique constraint "test_pkey"
29+
step c2: COMMIT;

‎src/test/isolation/expected/read-write-unique-3.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: rw1 rw2 c1 c2
4+
step rw1: SELECT insert_unique(1, '1');
5+
insert_unique
6+
7+
8+
step rw2: SELECT insert_unique(1, '2'); <waiting ...>
9+
step c1: COMMIT;
10+
step rw2: <... completed>
11+
error in steps c1 rw2: ERROR: could not serialize access due to read/write dependencies among transactions
12+
step c2: COMMIT;

‎src/test/isolation/expected/read-write-unique-4.out

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: r1 r2 w1 w2 c1 c2
4+
step r1: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
5+
coalesce
6+
7+
3
8+
step r2: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
9+
coalesce 10000
10+
11+
3
12+
step w1: INSERT INTO invoice VALUES (2016, 3);
13+
step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
14+
step c1: COMMIT;
15+
step w2: <... completed>
16+
error in steps c1 w2: ERROR: could not serialize access due to read/write dependencies among transactions
17+
step c2: COMMIT;
18+
19+
starting permutation: r1 w1 w2 c1 c2
20+
step r1: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
21+
coalesce
22+
23+
3
24+
step w1: INSERT INTO invoice VALUES (2016, 3);
25+
step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
26+
step c1: COMMIT;
27+
step w2: <... completed>
28+
error in steps c1 w2: ERROR: duplicate key value violates unique constraint "invoice_pkey"
29+
step c2: COMMIT;
30+
31+
starting permutation: r2 w1 w2 c1 c2
32+
step r2: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
33+
coalesce
34+
35+
3
36+
step w1: INSERT INTO invoice VALUES (2016, 3);
37+
step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
38+
step c1: COMMIT;
39+
step w2: <... completed>
40+
error in steps c1 w2: ERROR: duplicate key value violates unique constraint "invoice_pkey"
41+
step c2: COMMIT;

‎src/test/isolation/expected/read-write-unique.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: r1 r2 w1 w2 c1 c2
4+
step r1: SELECT * FROM test;
5+
i
6+
7+
step r2: SELECT * FROM test;
8+
i
9+
10+
step w1: INSERT INTO test VALUES (42);
11+
step w2: INSERT INTO test VALUES (42); <waiting ...>
12+
step c1: COMMIT;
13+
step w2: <... completed>
14+
error in steps c1 w2: ERROR: could not serialize access due to read/write dependencies among transactions
15+
step c2: COMMIT;
16+
17+
starting permutation: r1 w1 c1 r2 w2 c2
18+
step r1: SELECT * FROM test;
19+
i
20+
21+
step w1: INSERT INTO test VALUES (42);
22+
step c1: COMMIT;
23+
step r2: SELECT * FROM test;
24+
i
25+
26+
42
27+
step w2: INSERT INTO test VALUES (42);
28+
ERROR: duplicate key value violates unique constraint "test_pkey"
29+
step c2: COMMIT;

‎src/test/isolation/isolation_schedule

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,7 @@
1+
test: read-write-unique
2+
test: read-write-unique-2
3+
test: read-write-unique-3
4+
test: read-write-unique-4
15
test: simple-write-skew
26
test: receipt-report
37
test: temporal-range-integrity

‎src/test/isolation/specs/read-write-unique-2.spec

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
# Read-write-unique test.
2+
3+
setup
4+
{
5+
CREATE TABLE test (i integer PRIMARY KEY);
6+
}
7+
8+
teardown
9+
{
10+
DROP TABLE test;
11+
}
12+
13+
session "s1"
14+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
15+
step "r1" { SELECT * FROM test WHERE i = 42; }
16+
step "w1" { INSERT INTO test VALUES (42); }
17+
step "c1" { COMMIT; }
18+
19+
session "s2"
20+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
21+
step "r2" { SELECT * FROM test WHERE i = 42; }
22+
step "w2" { INSERT INTO test VALUES (42); }
23+
step "c2" { COMMIT; }
24+
25+
# Two SSI transactions see that there is no row with value 42
26+
# in the table, then try to insert that value; T1 inserts,
27+
# and then T2 blocks waiting for T1 to commit. Finally,
28+
# T2 reports a serialization failure.
29+
30+
permutation "r1" "r2" "w1" "w2" "c1" "c2"
31+
32+
# If the value is already visible before T2 begins, then a
33+
# regular unique constraint violation should still be raised
34+
# by T2.
35+
36+
permutation "r1" "w1" "c1" "r2" "w2" "c2"

‎src/test/isolation/specs/read-write-unique-3.spec

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
# Read-write-unique test.
2+
# From bug report 9301.
3+
4+
setup
5+
{
6+
CREATE TABLE test (
7+
key integer UNIQUE,
8+
val text
9+
);
10+
11+
CREATE OR REPLACE FUNCTION insert_unique(k integer, v text) RETURNS void
12+
LANGUAGE SQL AS $$
13+
INSERT INTO test (key, val) SELECT k, v WHERE NOT EXISTS (SELECT key FROM test WHERE key = k);
14+
$$;
15+
}
16+
17+
teardown
18+
{
19+
DROP FUNCTION insert_unique(integer, text);
20+
DROP TABLE test;
21+
}
22+
23+
session "s1"
24+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
25+
step "rw1" { SELECT insert_unique(1, '1'); }
26+
step "c1" { COMMIT; }
27+
28+
session "s2"
29+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
30+
step "rw2" { SELECT insert_unique(1, '2'); }
31+
step "c2" { COMMIT; }
32+
33+
permutation "rw1" "rw2" "c1" "c2"

‎src/test/isolation/specs/read-write-unique-4.spec

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
# Read-write-unique test.
2+
# Implementing a gapless sequence of ID numbers for each year.
3+
4+
setup
5+
{
6+
CREATE TABLE invoice (
7+
year int,
8+
invoice_number int,
9+
PRIMARY KEY (year, invoice_number)
10+
);
11+
12+
INSERT INTO invoice VALUES (2016, 1), (2016, 2);
13+
}
14+
15+
teardown
16+
{
17+
DROP TABLE invoice;
18+
}
19+
20+
session "s1"
21+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
22+
step "r1" { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
23+
step "w1" { INSERT INTO invoice VALUES (2016, 3); }
24+
step "c1" { COMMIT; }
25+
26+
session "s2"
27+
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
28+
step "r2" { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
29+
step "w2" { INSERT INTO invoice VALUES (2016, 3); }
30+
step "c2" { COMMIT; }
31+
32+
# if they both read first then there should be an SSI conflict
33+
permutation "r1" "r2" "w1" "w2" "c1" "c2"
34+
35+
# cases where one session doesn't explicitly read before writing:
36+
37+
# if s2 doesn't explicitly read, then trying to insert the value
38+
# generates a unique constraint violation after s1 commits, as if s2
39+
# ran after s1
40+
permutation "r1" "w1" "w2" "c1" "c2"
41+
42+
# if s1 doesn't explicitly read, but s2 does, then s1 inserts and
43+
# commits first, should s2 experience an SSI failure instead of a
44+
# unique constraint violation? there is no serial order of operations
45+
# (s1, s2) or (s2, s1) where s1 succeeds, and s2 doesn't see the row
46+
# in an explicit select but then fails to insert due to unique
47+
# constraint violation
48+
permutation "r2" "w1" "w2" "c1" "c2"

0 commit comments

Comments
 (0)
0