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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
Parsed test spec with 3 sessions
starting permutation: wx1 wx2 c1 c2 read
step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking';
step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking'; <waiting ...>
step c1: COMMIT;
step wx2: <... completed>
step c2: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
checking 850
savings 600
starting permutation: wy1 wy2 c1 c2 read
step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking';
step wy2: UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000; <waiting ...>
step c1: COMMIT;
step wy2: <... completed>
step c2: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
checking 1100
savings 600
starting permutation: upsert1 upsert2 c1 c2 read
step upsert1:
WITH upsert AS
(UPDATE accounts SET balance = balance + 500
WHERE accountid = 'savings'
RETURNING accountid)
INSERT INTO accounts SELECT 'savings', 500
WHERE NOT EXISTS (SELECT 1 FROM upsert);
step upsert2:
WITH upsert AS
(UPDATE accounts SET balance = balance + 1234
WHERE accountid = 'savings'
RETURNING accountid)
INSERT INTO accounts SELECT 'savings', 1234
WHERE NOT EXISTS (SELECT 1 FROM upsert);
<waiting ...>
step c1: COMMIT;
step upsert2: <... completed>
step c2: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
checking 600
savings 2334
starting permutation: readp1 writep1 readp2 c1 c2
step readp1: SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE;
tableoid ctid a b c
c1 (0,1) 0 0 0
c1 (0,4) 0 1 0
c2 (0,1) 1 0 0
c2 (0,4) 1 1 0
c3 (0,1) 2 0 0
c3 (0,4) 2 1 0
step writep1: UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0;
step readp2: SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; <waiting ...>
step c1: COMMIT;
step readp2: <... completed>
tableoid ctid a b c
c1 (0,1) 0 0 0
c1 (0,4) 0 1 0
c2 (0,1) 1 0 0
c3 (0,1) 2 0 0
c3 (0,4) 2 1 0
step c2: COMMIT;
starting permutation: writep2 returningp1 c1 c2
step writep2: UPDATE p SET b = -b WHERE a = 1 AND c = 0;
step returningp1:
WITH u AS ( UPDATE p SET b = b WHERE a > 0 RETURNING * )
SELECT * FROM u;
<waiting ...>
step c1: COMMIT;
step returningp1: <... completed>
a b c
1 0 0
1 0 1
1 0 2
1 -1 0
1 1 1
1 1 2
1 -2 0
1 2 1
1 2 2
1 -3 0
2 0 0
2 0 1
2 0 2
2 1 0
2 1 1
2 1 2
2 2 0
2 2 1
2 2 2
2 3 0
step c2: COMMIT;
starting permutation: wx2 partiallock c2 c1 read
step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking';
step partiallock:
SELECT * FROM accounts a1, accounts a2
WHERE a1.accountid = a2.accountid
FOR UPDATE OF a1;
<waiting ...>
step c2: COMMIT;
step partiallock: <... completed>
accountid balance accountid balance
checking 1050 checking 600
savings 600 savings 600
step c1: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
checking 1050
savings 600
starting permutation: wx2 lockwithvalues c2 c1 read
step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking';
step lockwithvalues:
SELECT * FROM accounts a1, (values('checking'),('savings')) v(id)
WHERE a1.accountid = v.id
FOR UPDATE OF a1;
<waiting ...>
step c2: COMMIT;
step lockwithvalues: <... completed>
accountid balance id
checking 1050 checking
savings 600 savings
step c1: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
checking 1050
savings 600
starting permutation: updateforss readforss c1 c2
step updateforss:
UPDATE table_a SET value = 'newTableAValue' WHERE id = 1;
UPDATE table_b SET value = 'newTableBValue' WHERE id = 1;
step readforss:
SELECT ta.id AS ta_id, ta.value AS ta_value,
(SELECT ROW(tb.id, tb.value)
FROM table_b tb WHERE ta.id = tb.id) AS tb_row
FROM table_a ta
WHERE ta.id = 1 FOR UPDATE OF ta;
<waiting ...>
step c1: COMMIT;
step readforss: <... completed>
ta_id ta_value tb_row
1 newTableAValue (1,tableBValue)
step c2: COMMIT;
starting permutation: wrtwcte readwcte c1 c2
step wrtwcte: UPDATE table_a SET value = 'tableAValue2' WHERE id = 1;
step readwcte:
WITH
cte1 AS (
SELECT id FROM table_b WHERE value = 'tableBValue'
),
cte2 AS (
SELECT * FROM table_a
WHERE id = (SELECT id FROM cte1)
FOR UPDATE
)
SELECT * FROM cte2;
<waiting ...>
step c1: COMMIT;
step c2: COMMIT;
step readwcte: <... completed>
id value
1 tableAValue2
|
