Edit File - File Manager

Edit File: rr_u_10-19.test

######################################################################################
#
# 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;

# 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;

# If the above statement resulted in deadlock we can still continue - the test will just try to do UPDATEs without explicitly locking first.

--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;

# 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;

Back to File Manager