Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4deb413

Browse files
committed
Add isolation test for SERIALIZABLE READ ONLY DEFERRABLE.
This improves code coverage and lays a foundation for testingsimilar issues in a distributed environment.Author: Thomas Munro <thomas.munro@enterprisedb.com>Reviewed-by: Michael Paquier <michael.paquier@gmail.com>
1 parente59b74a commit4deb413

File tree

8 files changed

+225
-0
lines changed

8 files changed

+225
-0
lines changed
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
Parsed test spec with 3 sessions
2+
3+
starting permutation: s2rx s2ry s1ry s1wy s1c s2wx s2c s3c
4+
step s2rx: SELECT balance FROM bank_account WHERE id = 'X';
5+
balance
6+
7+
0
8+
step s2ry: SELECT balance FROM bank_account WHERE id = 'Y';
9+
balance
10+
11+
0
12+
step s1ry: SELECT balance FROM bank_account WHERE id = 'Y';
13+
balance
14+
15+
0
16+
step s1wy: UPDATE bank_account SET balance = 20 WHERE id = 'Y';
17+
step s1c: COMMIT;
18+
step s2wx: UPDATE bank_account SET balance = -11 WHERE id = 'X';
19+
step s2c: COMMIT;
20+
step s3c: COMMIT;
21+
22+
starting permutation: s2rx s2ry s1ry s1wy s1c s3r s3c s2wx
23+
step s2rx: SELECT balance FROM bank_account WHERE id = 'X';
24+
balance
25+
26+
0
27+
step s2ry: SELECT balance FROM bank_account WHERE id = 'Y';
28+
balance
29+
30+
0
31+
step s1ry: SELECT balance FROM bank_account WHERE id = 'Y';
32+
balance
33+
34+
0
35+
step s1wy: UPDATE bank_account SET balance = 20 WHERE id = 'Y';
36+
step s1c: COMMIT;
37+
step s3r: SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id;
38+
id balance
39+
40+
X 0
41+
Y 20
42+
step s3c: COMMIT;
43+
step s2wx: UPDATE bank_account SET balance = -11 WHERE id = 'X';
44+
ERROR: could not serialize access due to read/write dependencies among transactions
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
Parsed test spec with 3 sessions
2+
3+
starting permutation: s2rx s2ry s1ry s1wy s1c s3r s2wx s2c s3c
4+
step s2rx: SELECT balance FROM bank_account WHERE id = 'X';
5+
balance
6+
7+
0
8+
step s2ry: SELECT balance FROM bank_account WHERE id = 'Y';
9+
balance
10+
11+
0
12+
step s1ry: SELECT balance FROM bank_account WHERE id = 'Y';
13+
balance
14+
15+
0
16+
step s1wy: UPDATE bank_account SET balance = 20 WHERE id = 'Y';
17+
step s1c: COMMIT;
18+
step s3r: SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id; <waiting ...>
19+
step s2wx: UPDATE bank_account SET balance = -11 WHERE id = 'X';
20+
step s2c: COMMIT;
21+
step s3r: <... completed>
22+
id balance
23+
24+
X -11
25+
Y 20
26+
step s3c: COMMIT;
Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
Parsed test spec with 3 sessions
2+
3+
starting permutation: s2rx s2ry s1ry s1wy s1c s3r s2wx s2c s3c
4+
step s2rx: SELECT balance FROM bank_account WHERE id = 'X';
5+
balance
6+
7+
0
8+
step s2ry: SELECT balance FROM bank_account WHERE id = 'Y';
9+
balance
10+
11+
0
12+
step s1ry: SELECT balance FROM bank_account WHERE id = 'Y';
13+
balance
14+
15+
0
16+
step s1wy: UPDATE bank_account SET balance = 20 WHERE id = 'Y';
17+
step s1c: COMMIT;
18+
step s3r: SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id;
19+
id balance
20+
21+
X 0
22+
Y 20
23+
step s2wx: UPDATE bank_account SET balance = -11 WHERE id = 'X';
24+
step s2c: COMMIT;
25+
step s3c: COMMIT;

‎src/test/isolation/isolation_schedule‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,6 @@
1+
test: read-only-anomaly
2+
test: read-only-anomaly-2
3+
test: read-only-anomaly-3
14
test: read-write-unique
25
test: read-write-unique-2
36
test: read-write-unique-3

‎src/test/isolation/isolationtester.c‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -231,6 +231,14 @@ main(int argc, char **argv)
231231
appendPQExpBuffer(&wait_query,",%s",backend_pids[i]);
232232
appendPQExpBufferStr(&wait_query,"}'::integer[]");
233233

234+
/* Also detect certain wait events. */
235+
appendPQExpBufferStr(&wait_query,
236+
" OR EXISTS ("
237+
" SELECT * "
238+
" FROM pg_catalog.pg_stat_activity "
239+
" WHERE pid = $1 "
240+
" AND wait_event IN ('SafeSnapshot'))");
241+
234242
res=PQprepare(conns[0],PREP_WAITING,wait_query.data,0,NULL);
235243
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
236244
{
Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
# The example from the paper "A read-only transaction anomaly under snapshot
2+
# isolation"[1].
3+
#
4+
# Here we test that serializable snapshot isolation (SERIALIZABLE) doesn't
5+
# suffer from the anomaly, because s2 is aborted upon detection of a cycle.
6+
#
7+
# [1] http://www.cs.umb.edu/~poneil/ROAnom.pdf
8+
9+
setup
10+
{
11+
CREATETABLEbank_account (idTEXTPRIMARYKEY,balanceDECIMALNOTNULL);
12+
INSERTINTObank_account (id,balance)VALUES ('X',0), ('Y',0);
13+
}
14+
15+
teardown
16+
{
17+
DROPTABLEbank_account;
18+
}
19+
20+
session"s1"
21+
setup {BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLE; }
22+
step"s1ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
23+
step"s1wy"{UPDATEbank_accountSETbalance=20WHEREid='Y'; }
24+
step"s1c" {COMMIT; }
25+
26+
session"s2"
27+
setup{BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLE; }
28+
step"s2rx"{SELECTbalanceFROMbank_accountWHEREid='X'; }
29+
step"s2ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
30+
step"s2wx"{UPDATEbank_accountSETbalance=-11WHEREid='X'; }
31+
step"s2c"{COMMIT; }
32+
33+
session"s3"
34+
setup{BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLE; }
35+
step"s3r"{SELECTid,balanceFROMbank_accountWHEREidIN ('X','Y')ORDERBYid; }
36+
step"s3c"{COMMIT; }
37+
38+
# without s3, s1 and s2 commit
39+
permutation"s2rx""s2ry""s1ry""s1wy""s1c""s2wx""s2c""s3c"
40+
41+
# once s3 observes the data committed by s1, a cycle is created and s2 aborts
42+
permutation"s2rx""s2ry""s1ry""s1wy""s1c""s3r""s3c""s2wx"
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
# The example from the paper "A read-only transaction anomaly under snapshot
2+
# isolation"[1].
3+
#
4+
# Here we test that serializable snapshot isolation can avoid the anomaly
5+
# without aborting any tranasctions, by instead causing s3 to be deferred
6+
# until a safe snapshot can be taken.
7+
#
8+
# [1] http://www.cs.umb.edu/~poneil/ROAnom.pdf
9+
10+
setup
11+
{
12+
CREATETABLEbank_account (idTEXTPRIMARYKEY,balanceDECIMALNOTNULL);
13+
INSERTINTObank_account (id,balance)VALUES ('X',0), ('Y',0);
14+
}
15+
16+
teardown
17+
{
18+
DROPTABLEbank_account;
19+
}
20+
21+
session"s1"
22+
setup {BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLE; }
23+
step"s1ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
24+
step"s1wy"{UPDATEbank_accountSETbalance=20WHEREid='Y'; }
25+
step"s1c" {COMMIT; }
26+
27+
session"s2"
28+
setup{BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLE; }
29+
step"s2rx"{SELECTbalanceFROMbank_accountWHEREid='X'; }
30+
step"s2ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
31+
step"s2wx"{UPDATEbank_accountSETbalance=-11WHEREid='X'; }
32+
step"s2c"{COMMIT; }
33+
34+
session"s3"
35+
setup{BEGINTRANSACTIONISOLATIONLEVELSERIALIZABLEREADONLYDEFERRABLE; }
36+
step"s3r"{SELECTid,balanceFROMbank_accountWHEREidIN ('X','Y')ORDERBYid; }
37+
step"s3c"{COMMIT; }
38+
39+
permutation"s2rx""s2ry""s1ry""s1wy""s1c""s3r""s2wx""s2c""s3c"
Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
# The example from the paper "A read-only transaction anomaly under snapshot
2+
# isolation"[1].
3+
#
4+
# Here we use snapshot isolation (REPEATABLE READ), so that s3 sees a state of
5+
# afairs that is not consistent with any serial ordering of s1 and s2.
6+
#
7+
# [1] http://www.cs.umb.edu/~poneil/ROAnom.pdf
8+
9+
setup
10+
{
11+
CREATETABLEbank_account (idTEXTPRIMARYKEY,balanceDECIMALNOTNULL);
12+
INSERTINTObank_account (id,balance)VALUES ('X',0), ('Y',0);
13+
}
14+
15+
teardown
16+
{
17+
DROPTABLEbank_account;
18+
}
19+
20+
session"s1"
21+
setup {BEGINTRANSACTIONISOLATIONLEVELREPEATABLEREAD; }
22+
step"s1ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
23+
step"s1wy"{UPDATEbank_accountSETbalance=20WHEREid='Y'; }
24+
step"s1c" {COMMIT; }
25+
26+
session"s2"
27+
setup{BEGINTRANSACTIONISOLATIONLEVELREPEATABLEREAD; }
28+
step"s2rx"{SELECTbalanceFROMbank_accountWHEREid='X'; }
29+
step"s2ry"{SELECTbalanceFROMbank_accountWHEREid='Y'; }
30+
step"s2wx"{UPDATEbank_accountSETbalance=-11WHEREid='X'; }
31+
step"s2c"{COMMIT; }
32+
33+
session"s3"
34+
setup{BEGINTRANSACTIONISOLATIONLEVELREPEATABLEREAD; }
35+
step"s3r"{SELECTid,balanceFROMbank_accountWHEREidIN ('X','Y')ORDERBYid; }
36+
step"s3c"{COMMIT; }
37+
38+
permutation"s2rx""s2ry""s1ry""s1wy""s1c""s3r""s2wx""s2c""s3c"

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp