102 lines
3.9 KiB
Plaintext
102 lines
3.9 KiB
Plaintext
#######################################################################################
|
|
#
|
|
# 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;
|