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 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 SERIALIZABLE; } 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 SERIALIZABLE; } 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 SERIALIZABLE; } 35step s3r { SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id; } 36step s3c { COMMIT; } 37 38# without s3, s1 and s2 commit 39permutation 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 42permutation s2rx s2ry s1ry s1wy s1c s3r s3c s2wx 43