Debian Codegraph @

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

raw
1 ################################################################################
2 # BUG#20136704 --REPLICA-PRESERVE-COMMIT-ORDER CAUSES SLAVE TO DEADLOCK AND
3 # BREAK FOR SOME QUERIE
4 #
5 # A corner case of deadlock:
6 # INSERT INTO t1 (7, NULL);
7 # DELETE FROM t1 WHERE c2 <= 3;
8 # They are executed parallel on master and INSERT is binlogged before DELETE.
9 #
10 # On slave, they are applied parallel by w0 and w1. And the deadlock is:
11 # w0 w1
12 # ------------------------- --------------------------
13 # applying DELETE applying INSERT
14 # get the row locks first.
15 # waiting for w1 to release the row lock.
16 # waiting for w2 to commit
17 #
18 # For this two special statements, INSERT doesn't block DELETE, but DELETE does
19 # block the INSERT since some innodb internal reasons.
20 #
21 # This test verifies the deadlock can be found and handled correctly.
22 # When it finds the deadlock, it will rollback the the transaction which should
23 # commit after the waiting transaction.
24 ################################################################################
25 --source include/have_debug.inc
26 --source include/have_binlog_format_statement.inc
27 --source include/master-slave.inc
28 #
29 # Initialize
30 #
31 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
32 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
33
34 --source include/sync_slave_sql_with_master.inc
35 --source include/stop_slave_sql.inc
36 SET @saved_replica_parallel_type = @@GLOBAL.replica_parallel_type;
37 SET @saved_replica_parallel_workers = @@GLOBAL.replica_parallel_workers;
38 SET @saved_replica_preserve_commit_order = @@GLOBAL.replica_preserve_commit_order;
39 SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout;
40 SET @saved_replica_transaction_retries = @@GLOBAL.replica_transaction_retries;
41
42 SET GLOBAL replica_transaction_retries = 2;
43 SET GLOBAL replica_parallel_type = "LOGICAL_CLOCK";
44 SET GLOBAL replica_parallel_workers = 3;
45 SET GLOBAL replica_preserve_commit_order = ON;
46 # Set it a long time to guarantee it doens't report an timeout error
47 SET GLOBAL innodb_lock_wait_timeout = 1000;
48
49 --source include/rpl_connection_slave.inc
50
51 --echo #
52 --echo # Case 1: Verify slave can find the deadlock when DELETE is waiting
53 --echo # for its turn to commit
54 --echo #
55 --source include/rpl_connection_master.inc
56 # There is a bug that the first two transactions cannot be applied parallel.
57 # So we need to an extra transaction here.
58 INSERT INTO t1 VALUES(10, 10);
59
60 --let $debug_point= d,set_commit_parent_100
61 --source include/add_debug_point.inc
62 INSERT INTO t1 VALUES(11, NULL);
63 DELETE FROM t1 WHERE c2 <= 3;
64
65 --source include/rpl_connection_slave.inc
66 # It blocks above INSERT statement
67 BEGIN;
68 INSERT INTO t1 VALUES(11, 11);
69
70 --source include/rpl_connection_slave1.inc
71 --source include/start_slave_sql.inc
72
73 # It guarantees the DELETE statement is waiting for
74 # the transaction before it to commit
75 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
76 --source include/wait_condition.inc
77
78 # Resume the INSERT statement and trigger the deadlock
79 --source include/rpl_connection_slave.inc
80 ROLLBACK;
81
82 --let $rpl_diff_statement= SELECT * FROM t1;
83 --source include/rpl_diff.inc
84
85 --source include/rpl_connection_master.inc
86 --let $debug_point= d,set_commit_parent_100
87 --source include/remove_debug_point.inc
88 TRUNCATE t1;
89 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
90 --source include/sync_slave_sql_with_master.inc
91 --source include/stop_slave_sql.inc
92
93 --echo #
94 --echo # Case 2: Verify slave can find the deadlock when it begins to applying
95 --echo # second DELETE statement.
96 --echo #
97 --source include/rpl_connection_master.inc
98 INSERT INTO t1 VALUES(20, NULL);
99
100 --let $debug_point= d,set_commit_parent_100
101 --source include/add_debug_point.inc
102 INSERT INTO t1 VALUES(21, NULL);
103
104 BEGIN;
105 INSERT INTO t1 VALUES(22, 22);
106 DELETE FROM t1 WHERE c2 <= 3;
107 INSERT INTO t1 VALUES(23, 23);
108 INSERT INTO t1 VALUES(24, 24);
109 INSERT INTO t1 VALUES(25, 25);
110 COMMIT;
111
112 --source include/rpl_connection_slave.inc
113 # Use it to block the worker applying 'INSERT INTO t1 VALUES(21, NULL)';
114 BEGIN;
115 INSERT INTO t1 VALUES(21, 21);
116
117 --let $rpl_connection_name= server_2_1
118 --source include/rpl_connection.inc
119 # Use it to block the worker applying 'INSERT INTO t1 VALUES(23, 23)';
120 BEGIN;
121 INSERT INTO t1 VALUES(23, 23);
122
123 --source include/rpl_connection_slave1.inc
124 --source include/start_slave_sql.inc
125
126 # It guarantees 'DELETE FROM t1 WHERE c2 <= 3;' is applied.
127 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE info = "INSERT INTO t1 VALUES(23, 23)"
128 --source include/wait_condition.inc
129
130 # Resume "INSERT INTO t1 VALUES(21, 21);" to trigger the lock conflict
131 --source include/rpl_connection_slave.inc
132 ROLLBACK;
133
134 # It guarantees that both workers are waiting for locks
135 # hold by above two transactions
136 --let $status_var= Innodb_row_lock_current_waits
137 --let $status_var_value= 2
138 --source include/wait_for_status_var.inc
139
140 # Resume "INSERT INTO t1 VALUES(23, 23);". It will be rolled back.
141 --let $rpl_connection_name= server_2_1
142 --source include/rpl_connection.inc
143 ROLLBACK;
144
145 --let $rpl_diff_statement= SELECT * FROM t1;
146 --source include/rpl_diff.inc
147
148 --source include/rpl_connection_master.inc
149 --let $debug_point= d,set_commit_parent_100
150 --source include/remove_debug_point.inc
151 TRUNCATE t1;
152 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (32, 32);
153 --source include/sync_slave_sql_with_master.inc
154 --source include/stop_slave_sql.inc
155
156
157 --echo #
158 --echo # Test case 3: Verify the worker can handle it correctly when it is
159 --echo # retrying a transaction.
160 --echo #
161 --source include/rpl_connection_master.inc
162 INSERT INTO t1 VALUES(30, NULL);
163
164 --let $debug_point= d,set_commit_parent_100
165 --source include/add_debug_point.inc
166
167 INSERT INTO t1 VALUES(31, NULL);
168 INSERT INTO t1 VALUES(33, NULL);
169
170 DELETE FROM t1 WHERE c2 <= 3;
171
172 --source include/rpl_connection_slave.inc
173 # Use it to block the worker applying 'INSERT INTO t1 VALUES(31, NULL)';
174 BEGIN;
175 INSERT INTO t1 VALUES(31, 31);
176
177 --let $rpl_connection_name= server_2_1
178 --source include/rpl_connection.inc
179 # Use it to block the worker applying 'INSERT INTO t1 VALUES(33, NULL)';
180 BEGIN;
181 INSERT INTO t1 VALUES(33, 33);
182
183 --source include/rpl_connection_slave1.inc
184 --source include/start_slave_sql.inc
185
186 # It guarantees 'DELETE' statement is waiting for the transactions before it.
187 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
188 --source include/wait_condition.inc
189
190 # Trigger one deadlock
191 --source include/rpl_connection_slave.inc
192 ROLLBACK;
193
194 # It guarantees "INSERT INTO t1 VALUES(31, 31);" is already applied.
195 --let $wait_condition= SELECT count(*) = 1 FROM t1 WHERE c1 = 31
196 --source include/wait_condition.inc
197
198 # It guarantees 'DELETE' statement is waiting for the transactions before it.
199 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
200 --source include/wait_condition.inc
201
202 # Trigger another deadlock
203 --let $rpl_connection_name= server_2_1
204 --source include/rpl_connection.inc
205 ROLLBACK;
206
207 --let $rpl_diff_statement= SELECT * FROM t1
208 --source include/rpl_diff.inc
209
210 --echo #
211 --echo # Test Case 4: Innodb internal transaction deadlock
212 --echo #
213 --source include/rpl_connection_master.inc
214 CALL mtr.add_suppression(".*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
215 --let $debug_point= d,set_commit_parent_100
216 --source include/remove_debug_point.inc
217
218 CREATE TABLE t2(database_name VARCHAR(200), table_name VARCHAR(200));
219 INSERT INTO t2 SELECT database_name, table_name FROM mysql.innodb_table_stats;
220 --source include/sync_slave_sql_with_master.inc
221 --source include/stop_slave_sql.inc
222 CALL mtr.add_suppression(".* Cannot save table statistics for table.*");
223 LOCK TABLE t1 WRITE;
224
225 --source include/rpl_connection_master.inc
226 # There is a bug that the first two transactions cannot be applied parallel.
227 # So we need to an extra transaction here.
228 TRUNCATE t2;
229 --let $debug_point= d,set_commit_parent_100
230 --source include/add_debug_point.inc
231
232 ANALYZE TABLE t1;
233 INSERT INTO t2 SELECT database_name, table_name FROM mysql.innodb_table_stats;
234
235 --source include/rpl_connection_slave.inc
236 --source include/start_slave_sql.inc
237
238 # It guarantees 'INSERT' statement is waiting for the transactions before it.
239 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit"
240 --source include/wait_condition.inc
241
242 UNLOCK TABLES;
243
244 --echo #
245 --echo # Test Case 5: It won't cause transaction rollback if
246 --echo # replica_preserve_commit_order is OFF
247 --echo #
248 --source include/rpl_connection_master.inc
249 --let $debug_point= d,set_commit_parent_100
250 --source include/remove_debug_point.inc
251
252 TRUNCATE t1;
253 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
254 --source include/sync_slave_sql_with_master.inc
255 --source include/stop_slave_sql.inc
256
257 --source include/rpl_connection_master.inc
258 INSERT INTO t1 VALUES(50, 50);
259
260 --let $debug_point= d,set_commit_parent_100
261 --source include/add_debug_point.inc
262
263 INSERT INTO t1 VALUES(51, NULL);
264 BEGIN;
265 INSERT INTO t1 VALUES(52, 52);
266 DELETE FROM t1 WHERE c2 <= 3;
267 INSERT INTO t1 VALUES(53, 53);
268 INSERT INTO t1 VALUES(54, 54);
269 INSERT INTO t1 VALUES(55, 55);
270 COMMIT;
271
272 --source include/rpl_connection_slave.inc
273 # Use it to block the worker applying 'INSERT INTO t1 VALUES(51, NULL)';
274 BEGIN;
275 INSERT INTO t1 VALUES(51, 51);
276
277 --let $rpl_connection_name= server_2_1
278 --source include/rpl_connection.inc
279 # Use it to block the worker applying 'INSERT INTO t1 VALUES(53, 53)';
280 BEGIN;
281 INSERT INTO t1 VALUES(53, 53);
282
283 --source include/rpl_connection_slave1.inc
284 SET GLOBAL replica_preserve_commit_order = OFF;
285 # It will make slave to stop if order commit deadlock happens
286 SET GLOBAL replica_transaction_retries = 0;
287 --source include/start_slave_sql.inc
288
289 # It guarantees 'DELETE FROM t1 WHERE c2 <= 3;' is applied.
290 --let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE info = "INSERT INTO t1 VALUES(53, 53)"
291 --source include/wait_condition.inc
292
293 # Resume "INSERT INTO t1 VALUES(51, 51);" to trigger the lock conflict
294 --source include/rpl_connection_slave.inc
295 ROLLBACK;
296
297 # It guarantees that both workers are waiting for locks
298 # hold by above two transactions
299 --let $status_var= Innodb_row_lock_current_waits
300 --let $status_var_value= 2
301 --source include/wait_for_status_var.inc
302
303 # Resume "INSERT INTO t1 VALUES(53, 53);" to continue its transaction.
304 --let $rpl_connection_name= server_2_1
305 --source include/rpl_connection.inc
306 ROLLBACK;
307
308 --let $rpl_diff_statement= SELECT * FROM t1;
309 --source include/rpl_diff.inc
310
311 --echo #
312 --echo # Deinitialize
313 --echo #
314 --source include/rpl_connection_master.inc
315 --let $debug_point= d,set_commit_parent_100
316 --source include/remove_debug_point.inc
317 DROP TABLE t1, t2;
318 --source include/sync_slave_sql_with_master.inc
319
320 --source include/stop_slave.inc
321 SET GLOBAL replica_transaction_retries = @saved_replica_transaction_retries;
322 SET GLOBAL replica_parallel_type = @saved_replica_parallel_type;
323 --disable_warnings
324 SET GLOBAL replica_parallel_workers = @saved_replica_parallel_workers;
325 --enable_warnings
326 SET GLOBAL replica_preserve_commit_order = @saved_replica_preserve_commit_order;
327 SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout;
328
329 --source include/start_slave.inc
330 --source include/rpl_end.inc