Edit File - File Manager

Edit File: rr_replace_7-8.test

#######################################################################################
#
# Executes REPLACE statements (effectively INSERT, or DELETE then INSERT if
# a duplicate value for a unique index or primary key is specified.
#
# Transaction 1: REPLACE INTO
# Transaction 2: REPLACE INTO SELECT
#
# NOTE: We should not delete rows where pk MOD 5 = 0 AND pk < 1001
#       (consistency check, see rr_c_count_not_zero).
#       So, make sure to insert/replace rows with pk > 1000, etc.
#
# Note that one REPLACE insert may replace more than one row, if we have
# multiple columns with unique indexes.
# Vary the values for unique columns so that we don't replace the same row every time.
######################################################################################

SET autocommit = 0;

###########################
# Transaction 1
###########################

START TRANSACTION;

# In order to avoid replacing a "reserved" row (pk mod 5 = 0 AND pk <= 1000), we need
# to pick a row to replace which fulfills this requirement (we cannot replace an arbitrary row).
# We also should not replace internally inconsistent rows, as this would not be a zero-sum transaction.
# We select a pk between 200 and 1200 depending on conn_id, not including those where pk MOD 5 = 0.
# This will cost an extra roundtrip and reduce concurency, but there is not much else to do apart
# from not having unique indexes in the table.
# Using FOR UPDATE to avoid letting other treads change the uniques values or the row's consistency
# properties in the mean time.

--echo *** Disabling result log
--disable_result_log
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
SELECT @pk:=`pk`, @unique:=`int1_unique`
    FROM t1
    WHERE `pk` MOD 5 <> 0
      AND `pk` > 200 + (CONNECTION_ID() MOD 1000)
      AND `int1_unique` NOT IN (SELECT `int1_unique` FROM t1 WHERE (`pk` < 1000 AND `pk` MOD 5 = 0) OR `is_consistent` = 0)
      AND -`int1_unique` NOT IN (SELECT `int2_unique` FROM t1 WHERE (`pk` < 1000 AND `pk` MOD 5 = 0) OR `is_consistent` = 0)
      AND `is_consistent`= 1
    LIMIT 1 FOR UPDATE;
--echo *** Enabling result log
--enable_result_log

--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc

--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
REPLACE INTO t1 SET `pk` = @pk,
                    `id` = 7,
                    `int1` = 7,
                    `int1_key` = -7,
                    `int1_unique` = @unique,
                    `int2` = -7,
                    `int2_key` = 7,
                    `int2_unique` = -@unique,
                    `connection_id` = CONNECTION_ID(),
                    `is_consistent` = 1;

COMMIT;

###########################
# Transaction 2
###########################

START TRANSACTION;

# Same rules apply as in previous transaction. Do not replace a "reserved" or inconsistent row.

# We get Warning "1592: Statement is not safe to log in statement mode" with server 5.1
# due to LIMIT (see Bug#42415 and Bug#42851).
--disable_warnings

--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
REPLACE INTO t1
    SELECT * FROM t1
    WHERE `pk` > 1000 + CONNECTION_ID() MOD 777
      AND `int1_unique` NOT IN (SELECT `int1_unique` FROM t1 WHERE `pk` < 1000 OR `is_consistent` = 0)
      AND `int2_unique` NOT IN (SELECT `int2_unique` FROM t1 WHERE `pk` < 1000 OR `is_consistent` = 0)
      AND `pk` MOD 5 <> 0
      AND `is_consistent` = 1
    ORDER BY `pk` LIMIT 1;
--enable_warnings

# Conditional, so skip query log:
--disable_query_log
--echo *** Updating replaced row (if applicable)
# Update id, conn_id if we successfully replaced a row.
if (`SELECT IF(ROW_COUNT() > 0, 1, 0)`)
{
    --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD
    UPDATE t1 SET `id` = 8, `connection_id` = CONNECTION_ID() WHERE `pk` = @@last_insert_id;
    --source suite/engines/rr_trx/include/check_for_error_rollback.inc
}
--enable_query_log

COMMIT;

Back to File Manager