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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
|
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: wx2_ext partiallock_ext c2 c1 read_ext
step wx2_ext: UPDATE accounts_ext SET balance = balance + 450;
step partiallock_ext:
SELECT * FROM accounts_ext a1, accounts_ext a2
WHERE a1.accountid = a2.accountid
FOR UPDATE OF a1;
<waiting ...>
step c2: COMMIT;
step partiallock_ext: <... completed>
accountid balance other newcol newcol2 accountid balance other newcol newcol2
checking 1050 other 42 checking 600 other 42
savings 1150 42 savings 700 42
step c1: COMMIT;
step read_ext: SELECT * FROM accounts_ext ORDER BY accountid;
accountid balance other newcol newcol2
checking 1050 other 42
savings 1150 42
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: updateforcip updateforcip2 c1 c2 read_a
step updateforcip:
UPDATE table_a SET value = NULL WHERE id = 1;
step updateforcip2:
UPDATE table_a SET value = COALESCE(value, (SELECT text 'newValue')) WHERE id = 1;
<waiting ...>
step c1: COMMIT;
step updateforcip2: <... completed>
step c2: COMMIT;
step read_a: SELECT * FROM table_a ORDER BY id;
id value
1 newValue
starting permutation: updateforcip updateforcip3 c1 c2 read_a
step updateforcip:
UPDATE table_a SET value = NULL WHERE id = 1;
step updateforcip3:
WITH d(val) AS (SELECT text 'newValue' FROM generate_series(1,1))
UPDATE table_a SET value = COALESCE(value, (SELECT val FROM d)) WHERE id = 1;
<waiting ...>
step c1: COMMIT;
step updateforcip3: <... completed>
step c2: COMMIT;
step read_a: SELECT * FROM table_a ORDER BY id;
id value
1 newValue
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
starting permutation: wrjt selectjoinforupdate c2 c1
step wrjt: UPDATE jointest SET data = 42 WHERE id = 7;
step selectjoinforupdate:
set local enable_nestloop to 0;
set local enable_hashjoin to 0;
set local enable_seqscan to 0;
explain (costs off)
select * from jointest a join jointest b on a.id=b.id for update;
select * from jointest a join jointest b on a.id=b.id for update;
<waiting ...>
step c2: COMMIT;
step selectjoinforupdate: <... completed>
QUERY PLAN
LockRows
-> Merge Join
Merge Cond: (a.id = b.id)
-> Index Scan using jointest_id_idx on jointest a
-> Index Scan using jointest_id_idx on jointest b
id data id data
1 0 1 0
2 0 2 0
3 0 3 0
4 0 4 0
5 0 5 0
6 0 6 0
7 42 7 42
8 0 8 0
9 0 9 0
10 0 10 0
step c1: COMMIT;
starting permutation: wrjt selectresultforupdate c2 c1
step wrjt: UPDATE jointest SET data = 42 WHERE id = 7;
step selectresultforupdate:
select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
left join table_a a on a.id = x, jointest jt
where jt.id = y;
explain (verbose, costs off)
select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
left join table_a a on a.id = x, jointest jt
where jt.id = y for update of jt, ss1, ss2;
select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
left join table_a a on a.id = x, jointest jt
where jt.id = y for update of jt, ss1, ss2;
<waiting ...>
step c2: COMMIT;
step selectresultforupdate: <... completed>
x y id value id data
1 7 1 tableAValue 7 0
QUERY PLAN
LockRows
Output: 1, 7, a.id, a.value, jt.id, jt.data, jt.ctid, a.ctid
-> Nested Loop Left Join
Output: 1, 7, a.id, a.value, jt.id, jt.data, jt.ctid, a.ctid
-> Nested Loop
Output: jt.id, jt.data, jt.ctid
-> Seq Scan on public.jointest jt
Output: jt.id, jt.data, jt.ctid
Filter: (jt.id = 7)
-> Result
-> Seq Scan on public.table_a a
Output: a.id, a.value, a.ctid
Filter: (a.id = 1)
x y id value id data
1 7 1 tableAValue 7 42
step c1: COMMIT;
starting permutation: wrtwcte multireadwcte c1 c2
step wrtwcte: UPDATE table_a SET value = 'tableAValue2' WHERE id = 1;
step multireadwcte:
WITH updated AS (
UPDATE table_a SET value = 'tableAValue3' WHERE id = 1 RETURNING id
)
SELECT (SELECT id FROM updated) AS subid, * FROM updated;
<waiting ...>
step c1: COMMIT;
step c2: COMMIT;
step multireadwcte: <... completed>
subid id
1 1
|
