1# Read-write-unique test.
2# Implementing a gapless sequence of ID numbers for each year.
3
4setup
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
15teardown
16{
17  DROP TABLE invoice;
18}
19
20session s1
21setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
22step r1 { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
23step w1 { INSERT INTO invoice VALUES (2016, 3); }
24step c1 { COMMIT; }
25
26session s2
27setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
28step r2 { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
29step w2 { INSERT INTO invoice VALUES (2016, 3); }
30step c2 { COMMIT; }
31
32# if they both read first then there should be an SSI conflict
33permutation 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
40permutation 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
48permutation r2 w1 w2 c1 c2
49