| 1 |
####################################################################################### |
| 2 |
# |
| 3 |
# Executes REPLACE statements (effectively INSERT, or DELETE then INSERT if |
| 4 |
# a duplicate value for a unique index or primary key is specified. |
| 5 |
# |
| 6 |
# Transaction 1: REPLACE INTO |
| 7 |
# Transaction 2: REPLACE INTO SELECT |
| 8 |
# |
| 9 |
# NOTE: We should not delete rows where pk MOD 5 = 0 AND pk < 1001 |
| 10 |
# (consistency check, see rr_c_count_not_zero). |
| 11 |
# So, make sure to insert/replace rows with pk > 1000, etc. |
| 12 |
# |
| 13 |
# Note that one REPLACE insert may replace more than one row, if we have |
| 14 |
# multiple columns with unique indexes. |
| 15 |
# Vary the values for unique columns so that we don't replace the same row every time. |
| 16 |
###################################################################################### |
| 17 |
|
| 18 |
SET autocommit = 0; |
| 19 |
|
| 20 |
|
| 21 |
########################### |
| 22 |
# Transaction 1 |
| 23 |
########################### |
| 24 |
|
| 25 |
START TRANSACTION; |
| 26 |
|
| 27 |
# In order to avoid replacing a "reserved" row (pk mod 5 = 0 AND pk <= 1000), we need |
| 28 |
# to pick a row to replace which fulfills this requirement (we cannot replace an arbitrary row). |
| 29 |
# We also should not replace internally inconsistent rows, as this would not be a zero-sum transaction. |
| 30 |
# We select a pk between 200 and 1200 depending on conn_id, not including those where pk MOD 5 = 0. |
| 31 |
# This will cost an extra roundtrip and reduce concurency, but there is not much else to do apart |
| 32 |
# from not having unique indexes in the table. |
| 33 |
# Using FOR UPDATE to avoid letting other treads change the uniques values or the row's consistency |
| 34 |
# properties in the mean time. |
| 35 |
|
| 36 |
--echo *** Disabling result log |
| 37 |
--disable_result_log |
| 38 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 39 |
SELECT @pk:=`pk`, @unique:=`int1_unique` |
| 40 |
FROM t1 |
| 41 |
WHERE `pk` MOD 5 <> 0 |
| 42 |
AND `pk` > 200 + (CONNECTION_ID() MOD 1000) |
| 43 |
AND `int1_unique` NOT IN (SELECT `int1_unique` FROM t1 WHERE (`pk` < 1000 AND `pk` MOD 5 = 0) OR `is_consistent` = 0) |
| 44 |
AND -`int1_unique` NOT IN (SELECT `int2_unique` FROM t1 WHERE (`pk` < 1000 AND `pk` MOD 5 = 0) OR `is_consistent` = 0) |
| 45 |
AND `is_consistent`= 1 |
| 46 |
LIMIT 1 FOR UPDATE; |
| 47 |
--echo *** Enabling result log |
| 48 |
--enable_result_log |
| 49 |
|
| 50 |
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc |
| 51 |
|
| 52 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 53 |
REPLACE INTO t1 SET `pk` = @pk, |
| 54 |
`id` = 7, |
| 55 |
`int1` = 7, |
| 56 |
`int1_key` = -7, |
| 57 |
`int1_unique` = @unique, |
| 58 |
`int2` = -7, |
| 59 |
`int2_key` = 7, |
| 60 |
`int2_unique` = -@unique, |
| 61 |
`connection_id` = CONNECTION_ID(), |
| 62 |
`is_consistent` = 1; |
| 63 |
|
| 64 |
COMMIT; |
| 65 |
|
| 66 |
########################### |
| 67 |
# Transaction 2 |
| 68 |
########################### |
| 69 |
|
| 70 |
START TRANSACTION; |
| 71 |
|
| 72 |
# Same rules apply as in previous transaction. Do not replace a "reserved" or inconsistent row. |
| 73 |
|
| 74 |
# We get Warning "1592: Statement is not safe to log in statement mode" with server 5.1 |
| 75 |
# due to LIMIT (see Bug#42415 and Bug#42851). |
| 76 |
--disable_warnings |
| 77 |
|
| 78 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 79 |
REPLACE INTO t1 |
| 80 |
SELECT * FROM t1 |
| 81 |
WHERE `pk` > 1000 + CONNECTION_ID() MOD 777 |
| 82 |
AND `int1_unique` NOT IN (SELECT `int1_unique` FROM t1 WHERE `pk` < 1000 OR `is_consistent` = 0) |
| 83 |
AND `int2_unique` NOT IN (SELECT `int2_unique` FROM t1 WHERE `pk` < 1000 OR `is_consistent` = 0) |
| 84 |
AND `pk` MOD 5 <> 0 |
| 85 |
AND `is_consistent` = 1 |
| 86 |
ORDER BY `pk` LIMIT 1; |
| 87 |
--enable_warnings |
| 88 |
|
| 89 |
# Conditional, so skip query log: |
| 90 |
--disable_query_log |
| 91 |
--echo *** Updating replaced row (if applicable) |
| 92 |
# Update id, conn_id if we successfully replaced a row. |
| 93 |
if (`SELECT IF(ROW_COUNT() > 0, 1, 0)`) |
| 94 |
{ |
| 95 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 96 |
UPDATE t1 SET `id` = 8, `connection_id` = CONNECTION_ID() WHERE `pk` = @@last_insert_id; |
| 97 |
--source suite/engines/rr_trx/include/check_for_error_rollback.inc |
| 98 |
} |
| 99 |
--enable_query_log |
| 100 |
|
| 101 |
COMMIT; |