1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
# Daily Report of Receipts test.
#
# This test doesn't persist a bad state in the database; rather, it
# provides a view of the data which is not consistent with any
# order of execution of the serializable transactions. It
# demonstrates a situation where the deposit date for receipts could
# be changed and a report of the closed day's receipts subsequently
# run which will miss a receipt from the date which has been closed.
#
# There are only six permutations which must cause a serialization failure.
# Failure cases are where s1 overlaps both s2 and s3, but s2 commits before
# s3 executes its first SELECT.
#
# As long as s3 is declared READ ONLY there should be no false positives.
# If s3 were changed to READ WRITE, we would currently expect 42 false
# positives. Further work dealing with de facto READ ONLY transactions
# may be able to reduce or eliminate those false positives.
setup
{
CREATE TABLE ctl (k text NOT NULL PRIMARY KEY, deposit_date date NOT NULL);
INSERT INTO ctl VALUES ('receipt', DATE '2008-12-22');
CREATE TABLE receipt (receipt_no int NOT NULL PRIMARY KEY, deposit_date date NOT NULL, amount numeric(13,2));
INSERT INTO receipt VALUES (1, (SELECT deposit_date FROM ctl WHERE k = 'receipt'), 1.00);
INSERT INTO receipt VALUES (2, (SELECT deposit_date FROM ctl WHERE k = 'receipt'), 2.00);
}
teardown
{
DROP TABLE ctl, receipt;
}
session s1
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step rxwy1 { INSERT INTO receipt VALUES (3, (SELECT deposit_date FROM ctl WHERE k = 'receipt'), 4.00); }
step c1 { COMMIT; }
session s2
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step wx2 { UPDATE ctl SET deposit_date = DATE '2008-12-23' WHERE k = 'receipt'; }
step c2 { COMMIT; }
session s3
setup { BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY; }
step rx3 { SELECT * FROM ctl WHERE k = 'receipt'; }
step ry3 { SELECT * FROM receipt WHERE deposit_date = DATE '2008-12-22'; }
step c3 { COMMIT; }
|
