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
9setup
10{
11	CREATE TABLE bank_account (id TEXT PRIMARY KEY, balance DECIMAL NOT NULL);
12	INSERT INTO bank_account (id, balance) VALUES ('X', 0), ('Y', 0);
13}
14
15teardown
16{
17	DROP TABLE bank_account;
18}
19
20session s1
21setup 		{ BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }
22step s1ry	{ SELECT balance FROM bank_account WHERE id = 'Y'; }
23step s1wy	{ UPDATE bank_account SET balance = 20 WHERE id = 'Y'; }
24step s1c 	{ COMMIT; }
25
26session s2
27setup		{ BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }
28step s2rx	{ SELECT balance FROM bank_account WHERE id = 'X'; }
29step s2ry	{ SELECT balance FROM bank_account WHERE id = 'Y'; }
30step s2wx	{ UPDATE bank_account SET balance = -11 WHERE id = 'X'; }
31step s2c	{ COMMIT; }
32
33session s3
34setup		{ BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }
35step s3r	{ SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id; }
36step s3c	{ COMMIT; }
37
38permutation s2rx s2ry s1ry s1wy s1c s3r s2wx s2c s3c
39