Debian Codegraph @

mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_slave_trans_retries_hang.test

raw
1 # ==== Purpose ====
2 #
3 # This test verifies that deadlock errors arising due to lock conflicts are
4 # handled properly when --replica-preserve-commit-order is enabled on the replica
5 # server.
6 #
7 # ==== Implementation ====
8 #
9 # 1. Create source-replica topolgy.
10 #
11 # 2. Setup testing environment.
12 #
13 # 2.1. Create table on source and insert few rows.
14 # 2.2. Setup necessary variables on replica server.
15 #
16 # 3. Execute transactions on source server that would generate deadlock on the
17 # replica server.
18 #
19 # The commands used to generate deadlock in this test are:
20 #
21 # INSERT INTO t1 (11, NULL);
22 # DELETE FROM t1 WHERE c2 <= 3;
23 #
24 # With the help of debug points on the source server, simulate that they are
25 # executed parallely on master and make sure INSERT is binlogged before
26 # DELETE.
27 #
28 # On replica server, they are applied parallel by w1 and w2. And the deadlock is:
29 # w1 w2
30 # ------------------------- --------------------------
31 # Applying INSERT Applying DELETE
32 # Gets the row locks first.
33 # Waits for w2 to release
34 # the row lock.
35 # Waits for w1 to commit.
36 #
37 # For these two special statements, INSERT doesn't block DELETE, but DELETE
38 # does block the INSERT because of the gap locks.
39 #
40 # CASE 1:
41 # 3.1. Verify that replica server can find the deadlock when the victim
42 # thread (DELETE) is waiting for its turn to commit and is handled
43 # properly.
44 # 3.2. Wait till server to handle the deadlock and till the co-ordinator
45 # thread to error out with ER_LOCK_DEADLOCK.
46 # 3.3. Restart the replication threads, sync the replica and verify that
47 # table data is consistent.
48 #
49 # CASE 2:
50 # 3.4. Verify that replica server can find the deadlock when the victim
51 # thread (DELETE) is in the middle of transaction and is handled
52 # properly.
53 # 3.5. Wait till server to handle the deadlock and till the co-ordinator
54 # thread to error out with ER_MTA_INCONSISTENT_DATA.
55 # 3.6. Restart the replication threads, sync the replica and verify that
56 # table data is consistent.
57 #
58 # 4. Cleanup.
59 #
60 # ==== References ====
61 #
62 # Bug #87796 Commit_order_manager can't terminate MTS worker properly when deadlock happens
63 # Bug #89247 Deadlock with MTS when replica_preserve_commit_order = ON.
64 # Bug #95249 stop slave permanently blocked
65 # Bug #99440 Threads of MTS Slave randomly stuck
66
67 # The test needs debug binaries
68
69 --source include/have_debug.inc
70 --source include/have_binlog_format_statement.inc
71
72 --echo #
73 --echo # 1. Create source-replica topolgy.
74 --source include/master-slave.inc
75
76 --echo #
77 --echo # 2. Setup testing environment.
78 --echo # 2.1. Create table on source and insert few rows.
79 --echo # 2.2. Setup necessary variables on replica server.
80 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
81 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
82
83 --source include/sync_slave_sql_with_master.inc
84 --source include/stop_slave_sql.inc
85 SET @saved_replica_parallel_type = @@GLOBAL.replica_parallel_type;
86 SET @saved_replica_parallel_workers = @@GLOBAL.replica_parallel_workers;
87 SET @saved_replica_preserve_commit_order = @@GLOBAL.replica_preserve_commit_order;
88 SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout;
89 SET @saved_replica_transaction_retries = @@GLOBAL.replica_transaction_retries;
90
91 SET GLOBAL replica_transaction_retries = 0;
92 SET GLOBAL replica_parallel_type = "LOGICAL_CLOCK";
93 SET GLOBAL replica_parallel_workers = 8;
94 SET GLOBAL replica_preserve_commit_order = ON;
95 # Set it to a long time to guarantee it doesn't report timeout error.
96 SET GLOBAL innodb_lock_wait_timeout = 1000;
97
98 --echo #
99 --echo # Case 1
100 --echo # ======
101 --echo #
102 --echo # Verify replica server can find the deadlock when the victim thread
103 --echo # (DELETE) is waiting for its turn to commit and is handled properly.
104 --echo #
105 --echo # 3.1 Execute transactions on source server that would generate deadlock on the
106 --echo # replica server.
107 --source include/rpl_connection_master.inc
108 # There is a bug that the first two transactions cannot be applied parallel.
109 # So we need to an extra transaction here.
110 INSERT INTO t1 VALUES(10, 10);
111
112 --let $debug_point = set_commit_parent_100
113 --source include/add_debug_point.inc
114 INSERT INTO t1 VALUES(11, NULL); # Transaction 1
115 DELETE FROM t1 WHERE c2 <= 3; # Transaction 2
116 INSERT INTO t1 VALUES(21, 21); # Transaction 3
117 INSERT INTO t1 VALUES(22, 22); # Transaction 4
118 INSERT INTO t1 VALUES(23, 23); # Transaction 5
119 --source include/remove_debug_point.inc
120
121 # Start a local Transaction 6 on replica to block the INSERT statement coming
122 # through the replication channel. This blocks the worker thread executing
123 # "INSERT INTO t1 VALUES(11, NULL)"
124 --source include/rpl_connection_slave.inc
125 BEGIN;
126 INSERT INTO t1 VALUES(11, 11);
127
128 # Wait till DELETE statement(w2) and other workers wait for the the preceding transaction(w1) to commit.
129 --source include/rpl_connection_slave1.inc
130 --source include/start_slave_sql.inc
131 --let $wait_condition = SELECT count(*) = 4 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
132 --source include/wait_condition.inc
133
134 # Resume the INSERT statement and trigger the deadlock
135 --source include/rpl_connection_slave.inc
136 ROLLBACK;
137
138 # When the INSERT statement proceeds, the w1 finds deadlock with w2, and it
139 # will ask w2 to rollback. When w2 rolls back the transaction, it would check
140 # the value of slave_trans_retries before retrying the transaction. Since, we
141 # have set it to 0 throughout the test, the w2 shall not retry the transaction
142 # and would call report_rollback.
143 #
144 # Without the fix, calling report_rollback() would make the w2 to return
145 # without removing its entry from the SPCO queue thus resulting in other worker
146 # threads to wait forever on the signal. Meanwhile if co-ordinator was asked to
147 # go down by any of the previous workers, this would make co-ordinator thread
148 # to wait for the workers to finish thus leading to server hang.
149
150 --echo # Add error supressions.
151 CALL mtr.add_suppression("Worker .* failed executing transaction.*");
152
153 --echo #
154 --echo # 3.2. Wait till the co-ordinator thread to error out with ER_LOCK_DEADLOCK.
155 --let $slave_sql_errno = convert_error(ER_LOCK_DEADLOCK)
156 --source include/wait_for_slave_sql_error.inc
157
158 --echo #
159 --echo # 3.3. Restart replication threads, sync the replica and verify that table
160 --echo # data is consistent.
161 --source include/start_slave.inc
162 --let $rpl_diff_statement = SELECT * FROM t1;
163 --source include/rpl_diff.inc
164
165 --echo #
166 --echo # Case 2
167 --echo # ======
168 --echo #
169 --echo # Verify that replica server can find the deadlock when the victim
170 --echo # thread (DELETE) is in the middle of transaction and is handled
171 --echo # properly.
172 --echo #
173 --echo # 3.4 Execute transactions on source server that would generate deadlock on the
174 --echo # replica server.
175 --source include/rpl_connection_master.inc
176 TRUNCATE t1;
177 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
178 --source include/sync_slave_sql_with_master.inc
179 --source include/stop_slave_sql.inc
180
181 --source include/rpl_connection_master.inc
182 INSERT INTO t1 VALUES(20, NULL);
183
184 --let $debug_point = set_commit_parent_100
185 --source include/add_debug_point.inc
186 INSERT INTO t1 VALUES(21, NULL); # Transaction 1
187
188 BEGIN; # Transaction 2
189 INSERT INTO t1 VALUES(22, 22);
190 DELETE FROM t1 WHERE c2 <= 3;
191 INSERT INTO t1 VALUES(23, 23);
192 INSERT INTO t1 VALUES(24, NULL);
193 INSERT INTO t1 VALUES(25, 25);
194 COMMIT;
195
196 INSERT INTO t1 VALUES(26, 26); # Transaction 3
197 INSERT INTO t1 VALUES(27, 27); # Transaction 4
198 INSERT INTO t1 VALUES(28, 28); # Transaction 5
199 --source include/remove_debug_point.inc
200
201 --source include/rpl_connection_slave.inc
202 # Start a local transaction on replica to block the INSERT statement coming
203 # through the replication channel. This blocks the worker thread executing
204 # "INSERT INTO t1 VALUES(21, NULL)"
205 BEGIN;
206 INSERT INTO t1 VALUES(21, 21);
207
208 --let $rpl_connection_name = server_2_1
209 --source include/rpl_connection.inc
210 # Use it to block the worker applying 'INSERT INTO t1 VALUES(23, 23)';
211 BEGIN;
212 INSERT INTO t1 VALUES(23, 23);
213
214 # Wait till DELETE statement(w2) and other workers wait for the the preceding transaction(w1) to commit.
215 --source include/rpl_connection_slave1.inc
216 --source include/start_slave_sql.inc
217
218 # It guarantees 'DELETE FROM t1 WHERE c2 <= 3;' is applied.
219 --let $wait_condition = SELECT count(*) = 1 FROM information_schema.processlist WHERE info = "INSERT INTO t1 VALUES(23, 23)"
220 --source include/wait_condition.inc
221
222 --let $wait_condition = SELECT count(*) = 3 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
223 --source include/wait_condition.inc
224
225 # It guarantees that both workers are waiting for locks held by above two transactions.
226 --let $status_var = Innodb_row_lock_current_waits
227 --let $status_var_value = 2
228 --source include/wait_for_status_var.inc
229
230 # Resume "INSERT INTO t1 VALUES(21, 21);" to trigger the lock conflict.
231 --source include/rpl_connection_slave.inc
232 ROLLBACK;
233
234 # Resume "INSERT INTO t1 VALUES(23, 23);". It will be rolled back.
235 --let $rpl_connection_name = server_2_1
236 --source include/rpl_connection.inc
237 ROLLBACK;
238
239 --echo #
240 --echo # 3.5. Wait till the co-ordinator thread to error out with ER_MTA_INCONSISTENT_DATA.
241 --let $slave_sql_errno = convert_error(ER_MTA_INCONSISTENT_DATA)
242 --source include/wait_for_slave_sql_error.inc
243
244 --echo #
245 --echo # 3.6. Restart replication threads, sync the replica and verify that table
246 --echo # data is consistent.
247 --source include/start_slave.inc
248 --let $rpl_diff_statement = SELECT * FROM t1;
249 --source include/rpl_diff.inc
250
251 --echo #
252 --echo # 4. Cleanup.
253 --echo #
254 --source include/rpl_connection_master.inc
255 DROP TABLE t1;
256 --source include/sync_slave_sql_with_master.inc
257
258 # Add Test suppressions
259 CALL mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.");
260
261 --source include/stop_slave.inc
262 SET GLOBAL replica_transaction_retries = @saved_replica_transaction_retries;
263 SET GLOBAL replica_parallel_type = @saved_replica_parallel_type;
264 --disable_warnings
265 SET GLOBAL replica_parallel_workers = @saved_replica_parallel_workers;
266 --enable_warnings
267 SET GLOBAL replica_preserve_commit_order = @saved_replica_preserve_commit_order;
268 SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout;
269
270 --let $rpl_only_running_threads = 1
271 --source include/rpl_end.inc