407 lines
15 KiB
Plaintext
407 lines
15 KiB
Plaintext
######################################################################################
|
|
#
|
|
# Using UPDATE statements in order to:
|
|
# - move a record outside the curent index extremities (min and max values).
|
|
# - move a record forward and backward in the index (add and subtract some number).
|
|
# - move a record into, out of and inside a locked index interval.
|
|
#
|
|
# This test is using FOR UPDATE to lock index ranges and to make sure we do not
|
|
# base new values on old record versions (in the face of concurrent updates).
|
|
#
|
|
# Need to handle any tx errors, ROLLBACK if needed to maintain table consistency.
|
|
#
|
|
# This test runs several transactions, each transaction executing one or more
|
|
# UPDATE statements and potentially other helping SELECT queries.
|
|
#
|
|
# If we pick a row (pk) by random that does not exist, it does not matter (will try
|
|
# again next time), but we should probably keep this to a minimum.
|
|
#
|
|
# We need a way to maintain table consistency when updating a field with an arbitrary
|
|
# number. Using this algorithm:
|
|
# * We need to know: How much does the table sum change with this update?
|
|
# * Change is: <new value> - <old value>.
|
|
# * We must then add back the negative of that to a different field in the table.
|
|
#
|
|
# Example: Columns a and b with values a = a1, b = b1
|
|
# We want to update a to a2. We need to figure out what b2 should be.
|
|
# - Save a1 (e.g. as user variable)
|
|
# - Update a to a2 and b to b2 = b - (a2 - a1)
|
|
# - In other words: a changed with a2 - a1.
|
|
# b changed with b2 - b1 = b1 - (a2 - a1) - b1 = -(a2 - a1)
|
|
# => Zero-sum change.
|
|
#
|
|
# NOTE: Consider splitting this up into multiple test files if we get too many
|
|
# skips due to locking errors (see check_for_error_rollback_skip.inc) .
|
|
######################################################################################
|
|
|
|
SET autocommit = 0;
|
|
|
|
###################
|
|
# Transaction 1
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record out of locked portion of index:
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (result will vary)
|
|
--disable_result_log
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` LIMIT 10 FOR UPDATE;
|
|
|
|
# Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
|
|
|
|
# We (may) have locked some records (if any were found).
|
|
# Set an int1_key to a value outside of this range.
|
|
# First pick a pk. We may use this later in the transaction.
|
|
SELECT @pk:=`pk` FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` LIMIT 1;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
# We should mark row as consistent if the row-sum is 0.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int1_key` = `int1_key` + 50,
|
|
`int2_key` = `int2_key` - 50,
|
|
`id` = 10,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
###################
|
|
# Transaction 2
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record out of locked portion of UNIQUE index:
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (result will vary)
|
|
--disable_result_log
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `int1_unique` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_unique` LIMIT 10 FOR UPDATE;
|
|
|
|
# Even if SELECT FOR UPDATE failed, we can continue - we just don't necessarily move the row out of locked portion of index.
|
|
|
|
# We (may) have locked some records (if any were found)
|
|
# Set an int1_unique to a value outside of this range.
|
|
# First pick a pk to use several times later in the transaction.
|
|
SELECT @pk:=`pk` FROM t1 WHERE `int1_unique` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_unique` LIMIT 1;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
# We should mark row as consistent if the row-sum is 0.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
|
|
UPDATE t1 SET `int1_unique` = `int1_unique` + 50 + CONNECTION_ID(),
|
|
`int2_unique` = `int2_unique` - 50 - CONNECTION_ID(),
|
|
`id` = 11,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
###################
|
|
# Transaction 3
|
|
###################
|
|
# Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
|
|
|
|
--echo
|
|
--echo *** Move record into locked portion of index:
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (result will vary)
|
|
--disable_result_log
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` LIMIT 10 FOR UPDATE;
|
|
|
|
# If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
|
|
|
|
# We (may) have locked some records (if any were found)
|
|
# Set an int1_key to a value outside of this range.
|
|
# Pick a pk to use later in the transaction. Select one that is outside of the locked range.
|
|
SELECT @pk:=`pk` FROM t1 WHERE `int1_key` > 1030 ORDER BY `int1_key`, `pk` LIMIT 1;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
# We should mark row as consistent if the row-sum is 0.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int1_key` = `int1_key` + 50,
|
|
`int2_key` = `int2_key` - 50,
|
|
`id` = 12,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
###################
|
|
# Transaction 4
|
|
###################
|
|
# Not doing this for unique index (too tricky to avoid DUP_ENTRY...)
|
|
|
|
--echo
|
|
--echo *** Move record inside locked portion of index (move it but stay inside the locked range):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (result will vary)
|
|
--disable_result_log
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `int1_key` BETWEEN 981 + (CONNECTION_ID() MOD 15) AND 1030 ORDER BY `int1_key`, `pk` LIMIT 10 FOR UPDATE;
|
|
|
|
# If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.
|
|
|
|
# We (may) have locked some records (if any were found)
|
|
# Set an int1_key to a value outside of this range.
|
|
# Pick a pk to use later in the transaction. Select one that is outside of the locked range.
|
|
SELECT @pk:=`pk` FROM t1 WHERE `int1_key` BETWEEN 981 + 10 + (CONNECTION_ID() MOD 15) AND 1019 ORDER BY `int1_key`, `pk` LIMIT 1;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
# We should mark row as consistent if the row-sum is 0.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int1_key` = `int1_key` - 10,
|
|
`int2_key` = `int2_key` + 10,
|
|
`id` = 13,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
###################
|
|
# Transaction 5
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record outside existing index boundary (max):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (results will vary)
|
|
--disable_result_log
|
|
|
|
# Get the max value of `int2_key`.
|
|
# Pick a random pk value.
|
|
# The pk identifies a row that we want to update to move its int2_key value above the current MAX.
|
|
SELECT @max:=MAX(`int2_key`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
|
|
|
|
# Get the current value of `int2_key` of the row we are going to update.
|
|
# We need this to be able to calculate values for maintaining table consistency.
|
|
# Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
|
|
# Hence, we need to lock the row to avoid concurrent modifications.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
|
|
|
|
# Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
|
|
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
|
|
|
|
SELECT @old:=`int2_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int2_key` = @max + 1,
|
|
`int2` = `int2` - (@max + 1 - @old),
|
|
`id` = 14,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(@sum = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
## Do the same with a UNIQUE index
|
|
|
|
###################
|
|
# Transaction 6
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record outside existing UNIQUE index boundary (max):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (results will vary)
|
|
--disable_result_log
|
|
|
|
# Get the max value of `int2_unique`.
|
|
# Pick a random pk value.
|
|
# The pk identifies a row that we want to update to move its int2_key value above the current MAX.
|
|
SELECT @max:=MAX(`int2_unique`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
|
|
|
|
# Get the current value of `int2_key` of the row we are going to update.
|
|
# We need this to be able to calculate values for maintaining table consistency.
|
|
# Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
|
|
# We need to lock the row to avoid concurrent "silent" modifications.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
|
|
|
|
# Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
|
|
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
|
|
|
|
SELECT @old:=`int2_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
|
|
UPDATE t1 SET `int2_unique` = @max + 1,
|
|
`int2` = `int2` - (@max + 1 - @old),
|
|
`id` = 15,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(@sum = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
|
|
|
|
# Verify sum after update:
|
|
if(`SELECT IF(`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` <> 0 AND `is_consistent` = 1, 1, 0) WHERE `pk` = @pk`)
|
|
{
|
|
--echo FAIL - updated row, set is_consistent = 1 but sum is not 0!
|
|
SELECT `pk`, `int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique` WHERE `pk` = @pk;
|
|
}
|
|
|
|
COMMIT;
|
|
|
|
|
|
###################
|
|
# Transaction 7
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record outside existing index boundary (min):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (results will vary)
|
|
--disable_result_log
|
|
|
|
# Get the min value of `int1_key`.
|
|
# Pick a random pk value.
|
|
# The pk identifies a row that we want to update to move its int1_key value below the current MIN.
|
|
SELECT @min:=MIN(`int1_key`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
|
|
|
|
# Get the current value of `int1_key` of the row we are going to update.
|
|
# We need this to be able to calculate values for maintaining table consistency.
|
|
# Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
|
|
# Hence, we need to lock the row to avoid concurrent modifications.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
|
|
|
|
# Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
|
|
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
|
|
|
|
SELECT @old:=`int1_key`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int1_key` = @min - 1,
|
|
`int1` = `int1` - (@min - 1 - @old),
|
|
`id` = 16,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(@sum = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
## Do the same with a UNIQUE index
|
|
|
|
###################
|
|
# Transaction 8
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record outside existing UNIQUE index boundary (min):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
--echo *** Disabling result log (results will vary)
|
|
--disable_result_log
|
|
|
|
# Get the max value of `int1_unique`.
|
|
# Pick a random pk value.
|
|
# The pk identifies a row that we want to update to move its int2_key value above the current MAX.
|
|
SELECT @min:=MIN(`int1_unique`), @pk:=FLOOR(1 + RAND() * (MAX(`pk`) - 1)) FROM t1;
|
|
|
|
# Get the current value of `int2_key` of the row we are going to update.
|
|
# We need this to be able to calculate values for maintaining table consistency.
|
|
# Also, we should mark row as consistent if the row-sum is 0, so calculate the sum of the data ints.
|
|
# Hence, we need to lock the row to avoid concurrent modifications.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
SELECT * FROM t1 WHERE `pk` = @pk FOR UPDATE;
|
|
|
|
# Do not continue if the above FOR UPDATE locking fails - we may end up making the table/row inconstistent.
|
|
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc
|
|
|
|
SELECT @old:=`int1_unique`, (@sum:=`int1` + `int2` + `int1_key` + `int2_key` + `int1_unique` + `int2_unique`) FROM t1 WHERE `pk` = @pk;
|
|
|
|
--echo *** Enabling result log
|
|
--enable_result_log
|
|
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD, ER_DUP_ENTRY
|
|
UPDATE t1 SET `int1_unique` = @min - 1,
|
|
`int1` = `int1` - (@min - 1 - @old),
|
|
`id` = 17,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`is_consistent` = IF(@sum = 0, 1, 0),
|
|
`thread_id` = 0
|
|
WHERE `pk` = @pk;
|
|
|
|
COMMIT;
|
|
|
|
|
|
###################
|
|
# Transaction 9
|
|
###################
|
|
|
|
--echo
|
|
--echo *** Move record forward in index (add some number):
|
|
--echo
|
|
START TRANSACTION;
|
|
|
|
# Updating a "random" row.
|
|
# Subtract the same number from another field to maintain consistency.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int2_key` = `int2_key` + 16,
|
|
`int2` = `int2` - 16,
|
|
`id` = 18,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`thread_id` = 0
|
|
WHERE `pk` = CONNECTION_ID() MOD 1000;
|
|
|
|
## 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.
|
|
|
|
--echo
|
|
--echo *** Move record backward in index (subtract some number):
|
|
--echo
|
|
|
|
# Updating a "random" row.
|
|
# Add the same number to another field to maintain consistency.
|
|
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
|
|
UPDATE t1 SET `int1_key` = `int1_key` - 16,
|
|
`int1` = `int1` + 16,
|
|
`id` = 18,
|
|
`connection_id` = CONNECTION_ID(),
|
|
`thread_id` = 0
|
|
WHERE `pk` = CONNECTION_ID() + 16 MOD 1000;
|
|
|
|
COMMIT;
|