1 ######################################################################################
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.
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).
11 # Need to handle any tx errors, ROLLBACK if needed to maintain table consistency.
13 # This test runs several transactions, each transaction executing one or more
14 # UPDATE statements and potentially other helping SELECT queries.
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.
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.
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)
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 ######################################################################################
44 --echo *** Move record out of locked portion of index:
48 --echo *** Disabling result log (result will vary)
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` LIMIT 10 FOR UPDATE;
53 # Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
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;
60 --echo *** Enabling result log
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,
68 `connection_id` = CONNECTION_ID(),
69 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
80 --echo *** Move record out of locked portion of UNIQUE index:
84 --echo *** Disabling result log (result will vary)
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` LIMIT 10 FOR UPDATE;
89 # Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
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;
96 --echo *** Enabling result log
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(),
104 `connection_id` = CONNECTION_ID(),
105 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
114 # Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
117 --echo *** Move record into locked portion of index:
121 --echo *** Disabling result log (result will vary)
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` LIMIT 10 FOR UPDATE;
126 # If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
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;
133 --echo *** Enabling result log
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,
141 `connection_id` = CONNECTION_ID(),
142 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
151 # Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
154 --echo *** Move record inside locked portion of index (move it but stay inside the locked range):
158 --echo *** Disabling result log (result will vary)
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` LIMIT 10 FOR UPDATE;
163 # If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
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;
170 --echo *** Enabling result log
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,
178 `connection_id` = CONNECTION_ID(),
179 `is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
190 --echo *** Move record outside existing index boundary (max):
194 --echo *** Disabling result log (results will vary)
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;
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;
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
212 SELECT @old:=`int2_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
214 --echo *** Enabling result log
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),
221 `connection_id` = CONNECTION_ID(),
222 `is_consistent` = IF(@sum = 0, 1, 0),
228 ## Do the same with a UNIQUE index
235 --echo *** Move record outside existing UNIQUE index boundary (max):
239 --echo *** Disabling result log (results will vary)
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;
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;
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
257 SELECT @old:=`int2_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
259 --echo *** Enabling result log
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),
266 `connection_id` = CONNECTION_ID(),
267 `is_consistent` = IF(@sum = 0, 1, 0),
271 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
273 # Verify sum after update:
274 if(`SELECT IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` <> 0 AND `is_consistent` = 1, 1, 0) WHERE `pk` = @pk`)
276 --echo FAIL - updated row, set is_consistent = 1 but sum is not 0!
277 SELECT `pk`, `int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` WHERE `pk` = @pk;
288 --echo *** Move record outside existing index boundary (min):
292 --echo *** Disabling result log (results will vary)
295 # Get the min value of `int1_key`.
296 # Pick a random pk value.
297 # The pk identifies a row that we want to update to move its int1_key value below the current MIN.
298 SELECT @min:=MIN(`int1_key`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
300 # Get the current value of `int1_key` of the row we are going to update.
301 # We need this to be able to calculate values for maintaining table consistency.
302 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
303 # Hence, we need to lock the row to avoid concurrent modifications.
304 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
305 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
307 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
308 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
310 SELECT @old:=`int1_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
312 --echo *** Enabling result log
315 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
316 UPDATE t1 SET `int1_key` = @min - 1,
317 `int1` = `int1` - (@min - 1 - @old),
319 `connection_id` = CONNECTION_ID(),
320 `is_consistent` = IF(@sum = 0, 1, 0),
325 ## Do the same with a UNIQUE index
332 --echo *** Move record outside existing UNIQUE index boundary (min):
336 --echo *** Disabling result log (results will vary)
339 # Get the max value of `int1_unique`.
340 # Pick a random pk value.
341 # The pk identifies a row that we want to update to move its int2_key value above the current MAX.
342 SELECT @min:=MIN(`int1_unique`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
344 # Get the current value of `int2_key` of the row we are going to update.
345 # We need this to be able to calculate values for maintaining table consistency.
346 # Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
347 # Hence, we need to lock the row to avoid concurrent modifications.
348 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
349 SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
351 # Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
352 --source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
354 SELECT @old:=`int1_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
356 --echo *** Enabling result log
359 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
360 UPDATE t1 SET `int1_unique` = @min - 1,
361 `int1` = `int1` - (@min - 1 - @old),
363 `connection_id` = CONNECTION_ID(),
364 `is_consistent` = IF(@sum = 0, 1, 0),
376 --echo *** Move record forward in index (add some number):
380 # Updating a "random" row.
381 # Subtract the same number from another field to maintain consistency.
382 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
383 UPDATE t1 SET `int2_key` = `int2_key` + 16,
384 `int2` = `int2` - 16,
386 `connection_id` = CONNECTION_ID(),
388 WHERE `pk` = CONNECTION_ID() MOD 1000;
390 ## 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.
393 --echo *** Move record backward in index (subtract some number):
396 # Updating a "random" row.
397 # Add the same number to another field to maintain consistency.
398 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
399 UPDATE t1 SET `int1_key` = `int1_key` - 16,
400 `int1` = `int1` + 16,
402 `connection_id` = CONNECTION_ID(),
404 WHERE `pk` = CONNECTION_ID() + 16 MOD 1000;