Debian Codegraph @

mysql-test/suite/engines/rr_trx/t/rr_u_10-19_nolimit.test

raw
1 ######################################################################################
2 #
3 # Using UPDATE statements in order to:
4 # - move a record outside the curent index extremities (min and max values).
5 # - move a record forward and backward in the index (add and subtract some number).
6 # - move a record into, out of and inside a locked index interval.
7 #
8 # This test is using FOR UPDATE to lock index ranges and to make sure we do not
9 # base new values on old record versions (in the face of concurrent updates).
10 #
11 # Need to handle any tx errors, ROLLBACK if needed to maintain table consistency.
12 #
13 # This test runs several transactions, each transaction executing one or more
14 # UPDATE statements and potentially other helping SELECT queries.
15 #
16 # If we pick a row (pk) by random that does not exist, it does not matter (will try
17 # again next time), but we should probably keep this to a minimum.
18 #
19 # We need a way to maintain table consistency when updating a field with an arbitrary
20 # number. Using this algorithm:
21 # * We need to know: How much does the table sum change with this update?
22 # * Change is: <new value> - <old value>.
23 # * We must then add back the negative of that to a different field in the table.
24 #
25 # Example: Columns a and b with values a = a1, b = b1
26 # We want to update a to a2. We need to figure out what b2 should be.
27 # - Save a1 (e.g. as user variable)
28 # - Update a to a2 and b to b2 = b - (a2 - a1)
29 # - In other words: a changed with a2 - a1.
30 # b changed with b2 - b1 = b1 - (a2 - a1) - b1 = -(a2 - a1)
31 # => Zero-sum change.
32 #
33 # NOTE: Consider splitting this up into multiple test files if we get too many
34 # skips due to locking errors (see check_for_error_rollback_skip.inc) .
35 ######################################################################################
36
37 SET autocommit = 0;
38
39 ###################
40 # Transaction 1
41 ###################
42
43 --echo
44 --echo *** Move record out of locked portion of index:
45 --echo
46 START TRANSACTION;
47
48 --echo *** Disabling result log (result will vary)
49 --disable_result_log
50 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
51 SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` FOR UPDATE;
52
53 # Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
54
55 # We (may) have locked some records (if any were found).
56 # Set an int1_key to a value outside of this range.
57 # First pick a pk. We may use this later in the transaction.
58 SELECT @pk:=`pk` FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` LIMIT 1;
59
60 --echo *** Enabling result log
61 --enable_result_log
62
63 # We should mark row as consistent if the row-sum is 0.
64 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
65 UPDATE t1 SET `int1_key` = `int1_key` + 50,
66 `int2_key` = `int2_key` - 50,
67 `id` = 10,
68 `connection_id` = CONNECTION_ID(),
69 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
70 `thread_id` = 0
71 WHERE `pk` = @pk;
72
73 COMMIT;
74
75 ###################
76 # Transaction 2
77 ###################
78
79 --echo
80 --echo *** Move record out of locked portion of UNIQUE index:
81 --echo
82 START TRANSACTION;
83
84 --echo *** Disabling result log (result will vary)
85 --disable_result_log
86 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
87 SELECT * FROM t1 WHERE `int1_unique` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_unique` FOR UPDATE;
88
89 # Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
90
91 # We (may) have locked some records (if any were found)
92 # Set an int1_unique to a value outside of this range.
93 # First pick a pk to use several times later in the transaction.
94 SELECT @pk:=`pk` FROM t1 WHERE `int1_unique` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_unique` LIMIT 1;
95
96 --echo *** Enabling result log
97 --enable_result_log
98
99 # We should mark row as consistent if the row-sum is 0.
100 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
101 UPDATE t1 SET `int1_unique` = `int1_unique` + 50 + CONNECTION_ID(),
102 `int2_unique` = `int2_unique` - 50 - CONNECTION_ID(),
103 `id` = 11,
104 `connection_id` = CONNECTION_ID(),
105 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
106 `thread_id` = 0
107 WHERE `pk` = @pk;
108
109 COMMIT;
110
111 ###################
112 # Transaction 3
113 ###################
114 # Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
115
116 --echo
117 --echo *** Move record into locked portion of index:
118 --echo
119 START TRANSACTION;
120
121 --echo *** Disabling result log (result will vary)
122 --disable_result_log
123 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
124 SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` FOR UPDATE;
125
126 # If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
127
128 # We (may) have locked some records (if any were found)
129 # Set an int1_key to a value outside of this range.
130 # Pick a pk to use later in the transaction. Select one that is outside of the locked range.
131 SELECT @pk:=`pk` FROM t1 WHERE `int1_key` > 1030 ORDER BY `int1_key`, `pk` LIMIT 1;
132
133 --echo *** Enabling result log
134 --enable_result_log
135
136 # We should mark row as consistent if the row-sum is 0.
137 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
138 UPDATE t1 SET `int1_key` = `int1_key` + 50,
139 `int2_key` = `int2_key` - 50,
140 `id` = 12,
141 `connection_id` = CONNECTION_ID(),
142 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
143 `thread_id` = 0
144 WHERE `pk` = @pk;
145
146 COMMIT;
147
148 ###################
149 # Transaction 4
150 ###################
151 # Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
152
153 --echo
154 --echo *** Move record inside locked portion of index (move it but stay inside the locked range):
155 --echo
156 START TRANSACTION;
157
158 --echo *** Disabling result log (result will vary)
159 --disable_result_log
160 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
161 SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` FOR UPDATE;
162
163 # If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
164
165 # We (may) have locked some records (if any were found)
166 # Set an int1_key to a value outside of this range.
167 # Pick a pk to use later in the transaction. Select one that is outside of the locked range.
168 SELECT @pk:=`pk` FROM t1 WHERE `int1_key` BETWEEN 981 + 10 + (CONNECTION_ID() MOD 15) AND 1019 ORDER BY `int1_key`, `pk` LIMIT 1;
169
170 --echo *** Enabling result log
171 --enable_result_log
172
173 # We should mark row as consistent if the row-sum is 0.
174 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
175 UPDATE t1 SET `int1_key` = `int1_key` - 10,
176 `int2_key` = `int2_key` + 10,
177 `id` = 13,
178 `connection_id` = CONNECTION_ID(),
179 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
180 `thread_id` = 0
181 WHERE `pk` = @pk;
182
183 COMMIT;
184
185 ###################
186 # Transaction 5
187 ###################
188
189 --echo
190 --echo *** Move record outside existing index boundary (max):
191 --echo
192 START TRANSACTION;
193
194 --echo *** Disabling result log (results will vary)
195 --disable_result_log
196
197 # Get the max value of `int2_key`.
198 # Pick a random pk value.
199 # The pk identifies a row that we want to update to move its int2_key value above the current MAX.
200 SELECT @max:=MAX(`int2_key`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
201
202 # Get the current value of `int2_key` of the row we are going to update.
203 # We need this to be able to calculate values for maintaining table consistency.
204 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
205 # Hence, we need to lock the row to avoid concurrent modifications.
206 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
207 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
208
209 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
210 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
211
212 SELECT @old:=`int2_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
213
214 --echo *** Enabling result log
215 --enable_result_log
216
217 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
218 UPDATE t1 SET `int2_key` = @max + 1,
219 `int2` = `int2` - (@max + 1 - @old),
220 `id` = 14,
221 `connection_id` = CONNECTION_ID(),
222 `is_consistent` = IF(@sum = 0, 1, 0),
223 `thread_id` = 0
224 WHERE `pk` = @pk;
225
226 COMMIT;
227
228 ## Do the same with a UNIQUE index
229
230 ###################
231 # Transaction 6
232 ###################
233
234 --echo
235 --echo *** Move record outside existing UNIQUE index boundary (max):
236 --echo
237 START TRANSACTION;
238
239 --echo *** Disabling result log (results will vary)
240 --disable_result_log
241
242 # Get the max value of `int2_unique`.
243 # Pick a random pk value.
244 # The pk identifies a row that we want to update to move its int2_key value above the current MAX.
245 SELECT @max:=MAX(`int2_unique`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
246
247 # Get the current value of `int2_key` of the row we are going to update.
248 # We need this to be able to calculate values for maintaining table consistency.
249 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
250 # We need to lock the row to avoid concurrent "silent" modifications.
251 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
252 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
253
254 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
255 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
256
257 SELECT @old:=`int2_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
258
259 --echo *** Enabling result log
260 --enable_result_log
261
262 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
263 UPDATE t1 SET `int2_unique` = @max + 1,
264 `int2` = `int2` - (@max + 1 - @old),
265 `id` = 15,
266 `connection_id` = CONNECTION_ID(),
267 `is_consistent` = IF(@sum = 0, 1, 0),
268 `thread_id` = 0
269 WHERE `pk` = @pk;
270
271 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
272
273 COMMIT;
274
275
276 ###################
277 # Transaction 7
278 ###################
279
280 --echo
281 --echo *** Move record outside existing index boundary (min):
282 --echo
283 START TRANSACTION;
284
285 --echo *** Disabling result log (results will vary)
286 --disable_result_log
287
288 # Get the min value of `int1_key`.
289 # Pick a random pk value.
290 # The pk identifies a row that we want to update to move its int1_key value below the current MIN.
291 SELECT @min:=MIN(`int1_key`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
292
293 # Get the current value of `int1_key` of the row we are going to update.
294 # We need this to be able to calculate values for maintaining table consistency.
295 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
296 # Hence, we need to lock the row to avoid concurrent modifications.
297 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
298 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
299
300 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
301 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
302
303 SELECT @old:=`int1_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
304
305 --echo *** Enabling result log
306 --enable_result_log
307
308 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
309 UPDATE t1 SET `int1_key` = @min - 1,
310 `int1` = `int1` - (@min - 1 - @old),
311 `id` = 16,
312 `connection_id` = CONNECTION_ID(),
313 `is_consistent` = IF(@sum = 0, 1, 0),
314 `thread_id` = 0
315 WHERE `pk` = @pk;
316
317 COMMIT;
318 ## Do the same with a UNIQUE index
319
320 ###################
321 # Transaction 8
322 ###################
323
324 --echo
325 --echo *** Move record outside existing UNIQUE index boundary (min):
326 --echo
327 START TRANSACTION;
328
329 --echo *** Disabling result log (results will vary)
330 --disable_result_log
331
332 # Get the max value of `int1_unique`.
333 # Pick a random pk value.
334 # The pk identifies a row that we want to update to move its int2_key value above the current MAX.
335 SELECT @min:=MIN(`int1_unique`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
336
337 # Get the current value of `int2_key` of the row we are going to update.
338 # We need this to be able to calculate values for maintaining table consistency.
339 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
340 # Hence, we need to lock the row to avoid concurrent modifications.
341 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
342 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
343
344 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
345 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
346
347 SELECT @old:=`int1_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
348
349 --echo *** Enabling result log
350 --enable_result_log
351
352 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
353 UPDATE t1 SET `int1_unique` = @min - 1,
354 `int1` = `int1` - (@min - 1 - @old),
355 `id` = 17,
356 `connection_id` = CONNECTION_ID(),
357 `is_consistent` = IF(@sum = 0, 1, 0),
358 `thread_id` = 0
359 WHERE `pk` = @pk;
360
361 COMMIT;
362
363
364 ###################
365 # Transaction 9
366 ###################
367
368 --echo
369 --echo *** Move record forward in index (add some number):
370 --echo
371 START TRANSACTION;
372
373 # Updating a "random" row.
374 # Subtract the same number from another field to maintain consistency.
375 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
376 UPDATE t1 SET `int2_key` = `int2_key` + 16,
377 `int2` = `int2` - 16,
378 `id` = 18,
379 `connection_id` = CONNECTION_ID(),
380 `thread_id` = 0
381 WHERE `pk` = CONNECTION_ID() MOD 1000;
382
383 ## Skip the same with a UNIQUE index (we need to update to > MAX or find some missing value in the middle). See MAX update in previous transactions.
384
385 --echo
386 --echo *** Move record backward in index (subtract some number):
387 --echo
388
389 # Updating a "random" row.
390 # Add the same number to another field to maintain consistency.
391 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
392 UPDATE t1 SET `int1_key` = `int1_key` - 16,
393 `int1` = `int1` + 16,
394 `id` = 18,
395 `connection_id` = CONNECTION_ID(),
396 `thread_id` = 0
397 WHERE `pk` = CONNECTION_ID() + 16 MOD 1000;
398
399 COMMIT;