Debian Codegraph @

mysql-test/suite/engines/rr_trx/t/rr_replace_7-8.test

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