From 46651ce6fe013220ed397add242004d764fc0153 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:15:05 +0200 Subject: Adding upstream version 14.5. Signed-off-by: Daniel Baumann --- src/test/isolation/specs/read-only-anomaly-2.spec | 42 +++++++++++++++++++++++ 1 file changed, 42 insertions(+) create mode 100644 src/test/isolation/specs/read-only-anomaly-2.spec (limited to 'src/test/isolation/specs/read-only-anomaly-2.spec') diff --git a/src/test/isolation/specs/read-only-anomaly-2.spec b/src/test/isolation/specs/read-only-anomaly-2.spec new file mode 100644 index 0000000..6b579a6 --- /dev/null +++ b/src/test/isolation/specs/read-only-anomaly-2.spec @@ -0,0 +1,42 @@ +# The example from the paper "A read-only transaction anomaly under snapshot +# isolation"[1]. +# +# Here we test that serializable snapshot isolation (SERIALIZABLE) doesn't +# suffer from the anomaly, because s2 is aborted upon detection of a cycle. +# +# [1] http://www.cs.umb.edu/~poneil/ROAnom.pdf + +setup +{ + CREATE TABLE bank_account (id TEXT PRIMARY KEY, balance DECIMAL NOT NULL); + INSERT INTO bank_account (id, balance) VALUES ('X', 0), ('Y', 0); +} + +teardown +{ + DROP TABLE bank_account; +} + +session s1 +setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; } +step s1ry { SELECT balance FROM bank_account WHERE id = 'Y'; } +step s1wy { UPDATE bank_account SET balance = 20 WHERE id = 'Y'; } +step s1c { COMMIT; } + +session s2 +setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; } +step s2rx { SELECT balance FROM bank_account WHERE id = 'X'; } +step s2ry { SELECT balance FROM bank_account WHERE id = 'Y'; } +step s2wx { UPDATE bank_account SET balance = -11 WHERE id = 'X'; } +step s2c { COMMIT; } + +session s3 +setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; } +step s3r { SELECT id, balance FROM bank_account WHERE id IN ('X', 'Y') ORDER BY id; } +step s3c { COMMIT; } + +# without s3, s1 and s2 commit +permutation s2rx s2ry s1ry s1wy s1c s2wx s2c s3c + +# once s3 observes the data committed by s1, a cycle is created and s2 aborts +permutation s2rx s2ry s1ry s1wy s1c s3r s3c s2wx -- cgit v1.2.3