Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_gtid_mts_spco_deadlock_other_locks.test

raw
1 # ==== Purpose ====
2 #
3 # This test's goal is to check that no deadlock occurs in an SPCO enabled
4 # multi-threaded replica when client connections issue global locking or
5 # ACL statements concurrently with the applier worker threads.
6 #
7 # ==== Requirements ====
8 #
9 #
10 # R1. A multi-threaded applier with enabled commit order preservation MUST
11 # be able to break deadlocks mixing commit order serialization and
12 # global locks like the ones taken by `SET READ_ONLY = ON` or `FLUSH
13 # TABLES WITH READ LOCK`.
14 #
15 # R2. A multi-threaded applier with enabled commit order preservation MUST
16 # be able to break deadlocks mixing commit order serialization and ACL
17 # related locks.
18 #
19 # R3. A multi-threaded applier with enabled commit order preservation MUST
20 # keep the current retry behavior by enforcing the
21 # `replica_transaction_retries` variable value for the amount of retries,
22 # when requested to back-off a transaction deadlocked by _R1_ and _R2_
23 # described scenarios.
24 #
25 # R4. A multi-threaded applier with enabled commit order preservation MUST
26 # keep the current replication channel behavior by stopping both the
27 # coordinator and worker threads when the configured amount of
28 # transaction retries is reached unsuccessfully, when requested to
29 # back-off a transaction deadlocked by _R1_ and _R2_ described
30 # scenarios.
31 #
32 # ==== Implementation ====
33 #
34 # Setup
35 # -----
36 # 1. Create two tables on the source
37 # 2. Setup replica to be multi-threaded
38 #
39 # Scenario logic
40 # --------------
41 # The scenarios being tested are all built upon a generic logic that will
42 # serliaze the client side execution and the MTA worker thread in order to
43 # force a deadlock situation that should be broken. This logic is
44 # implemented in `extra/rpl_tests/mts_spco_generate_deadlock.inc` and the file
45 # contains a description on how it's done. A high-level description is:
46 #
47 # 1. On the source, set up a commit group that contains statements that
48 # shouldn't be paralellized together using DBUG sync points.
49 # 2. On the replica, using client connection A, start a transaction and
50 # assign it the same GTID as the first statement issued on the source.
51 # 3. On the replica, using an additional client connection, issue a
52 # statement that will introduce a circular locking dependency with the
53 # worker threads and their assigned statements.
54 # 4. Wait until all worker threads and client connections are waiting on
55 # each other.
56 # 5. On the replica, rollback the transaction started in 2., leading to
57 # the locking circularity to be complete.
58 # 6. Wait for the replica to error out or to overcome the deadlock,
59 # depending on the configuration. In this test-case, the replica
60 # should error out.
61 # 7. Revert the effects of 3., breaking the deadlock and restart
62 # replication.
63 #
64 # Also, each test case is repeated twice: once where worker threads are
65 # all made to wait on the commit order queue and all are required to
66 # back-off by the MDL infra-structure; once where the worker executing
67 # the last transaction on the commit order is made to wait before
68 # arriving the commit stage and until the deadlock is identified, in
69 # which case this last worker will be required to back-off by the state
70 # of the commit order queue.
71 #
72 # TC1. `GLOBAL READ_ONLY=ON` issued by client and DDL replicated from source.
73 # -----------------------------------------------------------------------------
74 # 1. Alter a table and insert rows on the source in a way that they will
75 # belong to the same commit group.
76 # 2. On the replica, using client connection B, execute `SET GLOBAL
77 # READ_ONLY=ON` to acquire the global read lock, leading to the
78 # following lock acquisition dependencies: Client B --commit lock-->
79 # Worker 2 --commit order lock--> Worker 1 --gtid lock--> Client A.
80 # 3. On the replica, using client conneciton A, rollback the pending
81 # transaction, leading to the following lock acquisition dependencies:
82 # Client B --commit lock--> Worker 2 --commit order lock--> Worker 1
83 # --global read lock--> Client B.
84 # 4. Wait for the replication applier to error out with
85 # ER_LOCK_DEADLOCK or ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR.
86 # 5. On the replica, using client connection B, execute `SET GLOBAL
87 # READ_ONLY=OFF` to let the applier worker threads to finish after
88 # restart of the applier.
89 #
90 # TC2. `GLOBAL READ_ONLY=ON` issued by client and DML replicated from source.
91 # -----------------------------------------------------------------------------
92 # 1. Insert rows on the source in a way that they will belong to the same
93 # commit group.
94 # 2. On the replica, using client connection B, execute `SET GLOBAL
95 # READ_ONLY=ON` to acquire the global read lock, leading to the
96 # following lock acquisition dependencies: Client B --commit lock-->
97 # Worker 2 --commit order lock--> Worker 1 --gtid lock--> Client A.
98 # 3. On the replica, using client conneciton A, rollback the pending
99 # transaction, leading to the following lock acquisition dependencies:
100 # Client B --commit lock--> Worker 2 --commit order lock--> Worker 1
101 # --global read lock--> Client B.
102 # 4. Wait for the replication applier to error out with
103 # ER_LOCK_DEADLOCK or ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR.
104 # 5. On the replica, using client connection B, execute `SET GLOBAL
105 # READ_ONLY=OFF` to let the applier worker threads to finish.
106 #
107 # TC3. `FLUSH TABLES WITH READ LOCK` issued by client and DDL replicated
108 # from source.
109 # -----------------------------------------------------------------------------
110 # 1. Alter a table and insert rows on the source in a way that they will
111 # belong to the same commit group.
112 # 2. On the replica, using client connection B, execute `FLUSH TABLES
113 # WITH READ LOCK` to acquire the global read lock, leading to the
114 # following lock acquisition dependencies: Client B --commit lock-->
115 # Worker 2 --commit order lock--> Worker 1 --gtid lock--> Client A.
116 # 3. On the replica, using client conneciton A, rollback the pending
117 # transaction, leading to the following lock acquisition dependencies:
118 # Client B --commit lock--> Worker 2 --commit order lock--> Worker 1
119 # --global read lock--> Client B.
120 # 4. Wait for the replication applier to error out with
121 # ER_LOCK_DEADLOCK or ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR.
122 # 5. On the replica, using client connection B, execute `UNLOCK TABLES`
123 # to let the applier worker threads to finish.
124 #
125 # TC4. `FLUSH TABLES WITH READ LOCK` issued by client and DML replicated
126 # from source.
127 # -----------------------------------------------------------------------------
128 # 1. Insert rows on the source in a way that they will belong to the same
129 # commit group.
130 # 2. On the replica, using client connection B, execute `SET GLOBAL
131 # READ_ONLY=ON` to acquire the global read lock, leading to the
132 # following lock acquisition dependencies: Client B --commit lock-->
133 # Worker 2 --commit order lock--> Worker 1 --gtid lock--> Client A.
134 # 3. On the replica, using client conneciton A, rollback the pending
135 # transaction, leading to the following lock acquisition dependencies:
136 # Client B --commit lock--> Worker 2 --commit order lock--> Worker 1
137 # --global read lock--> Client B.
138 # 4. Wait for the replication applier to error out with
139 # ER_LOCK_DEADLOCK or ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR.
140 # 5. On the replica, using client connection B, execute `UNLOCK TABLES`
141 # to let the applier worker threads to finish.
142 #
143 # TC5. DML issued by client and `XA PREPARE` replicated from source.
144 # -----------------------------------------------------------------------------
145 # 1. Insert row on the source withing an XA transaction and issue `XA
146 # PREPARE` in a way that they will belong to the same commit group.
147 # 2. On the replica, using client conneciton A, rollback the pending
148 # transaction, leading to the following lock acquisition dependencies:
149 # Worker 1 --data lock--> Client B.
150 # 3. Wait for the replica applier to error out with ER_LOCK_WAIT_TIMEOUT.
151 # 4. On the source, issue `XA COMMIT` to allow the applier worker threads
152 # to finish.
153 #
154 # ==== References ====
155 #
156 # WL#13574 Include MDL and ACL locks in MTS deadlock detection
157 # infra-structure
158 # BUG#29930339 MTS ORDERED COMMIT MAY CAUSE DEADLOCK
159 # BUG#27798650 DEADLOCK OCCURRED BY "FLUSH TABLES WITH READ LOCK" WHEN USING
160 # MTS
161 # BUG#27415352 DEADLOCK WITH MTS WHEN REPLICA_PRESERVE_COMMIT_ORDER = ON.
162 # BUG#28165684 REPLICATION PARALLEL APPLIER BLOCKS AND PREVENTS SET GLOBAL
163 # SUPER_READ_ONLY= 1
164 # BUG#29239526 PARALLEL WORKERS+REPLICA_PRESERVE_COMMIT_ORDER+FLUSHTABLES WITH
165 # READ LOCK DEADLOCK
166 # BUG#29961792 ACL COMMANDS + DML ON MASTER MAY HANG SPCO ENABLED SLAVE
167 # SERVER
168 # BUG#26277791 DEADLOCK WITH REPLICA_PRESERVE_COMMIT_ORDER=ON WITH BUG#86078.
169 # BUG#26883680 COMMIT_ORDER_MANAGER CAN'T TERMINATE MTS WORKER PROPERLY WHEN
170 # DEADLOCK HAPPENS
171 # BUG#29512103 OPTIMIZE TABLE CAUSE THE SLAVE MTS DEADLOCK!
172 #
173 --source include/have_debug_sync.inc
174 --source include/have_slave_repository_type_table.inc
175 --let $rpl_privilege_checks_user = *:'applier'@'localhost'
176 --source include/master-slave.inc
177
178 CALL mtr.add_suppression(".*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
179
180 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
181 INSERT INTO t1 VALUES
182 (1, NULL),
183 (2, 2),
184 (3, NULL),
185 (4, 4),
186 (5, NULL),
187 (6, 6);
188
189 CREATE TABLE t2(a INT PRIMARY KEY);
190
191 --source include/sync_slave_sql_with_master.inc
192
193 --source extra/rpl_tests/mts_spco_generate_deadlock_setup.inc
194
195 --let $rpl_diff_statement = SELECT * FROM t1, t2
196
197 --echo
198 --echo #
199 --echo # TC1. `GLOBAL READ_ONLY=ON` issued by client and DDL replicated
200 --echo # from source.
201 --echo #------------------------------------------------------------------------
202 --source include/rpl_connection_master.inc
203 INSERT INTO t1 VALUES(10, 10);
204
205 --let $mts_spco_gd_trx_blocking_worker_1 = ALTER TABLE t2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
206 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(11, NULL)
207 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(12, NULL)
208 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 0
209 --let $mts_spco_gd_trx_blocking_client = SET GLOBAL read_only = ON
210 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
211 --let $mts_spco_gd_error_expected_replica = ER_LOCK_DEADLOCK
212 --let $mts_spco_gd_trx_to_client_unblocking_workers = SET GLOBAL read_only = OFF
213 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
214
215 --source include/rpl_connection_master.inc
216 INSERT INTO t1 VALUES(13, 13);
217 --let $mts_spco_gd_trx_blocking_worker_1 = ALTER TABLE t2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
218 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(14, NULL)
219 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(15, NULL)
220 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 1
221 --let $mts_spco_gd_trx_blocking_client = SET GLOBAL read_only = ON
222 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
223 --let $mts_spco_gd_error_expected_replica = ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR
224 --let $mts_spco_gd_trx_to_client_unblocking_workers = SET GLOBAL read_only = OFF
225 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
226
227
228 --echo
229 --echo #
230 --echo # TC2. `GLOBAL READ_ONLY=ON` issued by client and DML replicated
231 --echo # from source.
232 --echo #------------------------------------------------------------------------
233 --source include/rpl_connection_master.inc
234 INSERT INTO t1 VALUES(16, 16);
235
236 --let $mts_spco_gd_trx_blocking_worker_1 = INSERT INTO t1 VALUES(17, NULL)
237 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(18, NULL)
238 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(19, NULL)
239 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 0
240 --let $mts_spco_gd_trx_blocking_client = SET GLOBAL read_only = ON
241 --let $mts_spco_gd_error_expected_replica = ER_LOCK_DEADLOCK
242 --let $mts_spco_gd_trx_to_client_unblocking_workers = SET GLOBAL read_only = OFF
243 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
244
245 --source include/rpl_connection_master.inc
246 INSERT INTO t1 VALUES(20, 20);
247
248 --let $mts_spco_gd_trx_blocking_worker_1 = INSERT INTO t1 VALUES(21, NULL)
249 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(22, NULL)
250 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(23, NULL)
251 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 1
252 --let $mts_spco_gd_trx_blocking_client = SET GLOBAL read_only = ON
253 --let $mts_spco_gd_error_expected_replica = ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR
254 --let $mts_spco_gd_trx_to_client_unblocking_workers = SET GLOBAL read_only = OFF
255 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
256
257 --echo
258 --echo #
259 --echo # TC3. `FLUSH TABLES WITH READ LOCK` issued by client and DDL replicated
260 --echo # from source.
261 --echo #------------------------------------------------------------------------
262 --source include/rpl_connection_master.inc
263 INSERT INTO t1 VALUES(24, 24);
264
265 --let $mts_spco_gd_trx_blocking_worker_1 = ALTER TABLE t2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
266 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(25, NULL)
267 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(26, NULL)
268 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 0
269 --let $mts_spco_gd_trx_blocking_client = FLUSH TABLES WITH READ LOCK
270 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
271 --let $mts_spco_gd_wait_for_coordinator_running_state = Replica has read all relay log; waiting for more updates
272 --let $mts_spco_gd_error_expected_replica = ER_LOCK_DEADLOCK
273 --let $mts_spco_gd_trx_to_client_unblocking_workers = UNLOCK TABLES
274 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
275
276 --source include/rpl_connection_master.inc
277 INSERT INTO t1 VALUES(27, 27);
278
279 --let $mts_spco_gd_trx_blocking_worker_1 = ALTER TABLE t2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
280 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(28, NULL)
281 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(29, NULL)
282 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 1
283 --let $mts_spco_gd_trx_blocking_client = FLUSH TABLES WITH READ LOCK
284 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
285 --let $mts_spco_gd_wait_for_coordinator_running_state = Replica has read all relay log; waiting for more updates
286 --let $mts_spco_gd_error_expected_replica = ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR
287 --let $mts_spco_gd_trx_to_client_unblocking_workers = UNLOCK TABLES
288 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
289
290 --echo
291 --echo #
292 --echo # TC4. `FLUSH TABLES WITH READ LOCK` issued by client and DML replicated
293 --echo # from source.
294 --echo #------------------------------------------------------------------------
295 --source include/rpl_connection_master.inc
296 INSERT INTO t1 VALUES(30, 30);
297
298 --let $mts_spco_gd_trx_blocking_worker_1 = INSERT INTO t1 VALUES(31, NULL)
299 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(32, NULL)
300 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(33, NULL)
301 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 0
302 --let $mts_spco_gd_trx_blocking_client = FLUSH TABLES WITH READ LOCK
303 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
304 --let $mts_spco_gd_wait_for_coordinator_running_state = Replica has read all relay log; waiting for more updates
305 --let $mts_spco_gd_error_expected_replica = ER_LOCK_DEADLOCK
306 --let $mts_spco_gd_trx_to_client_unblocking_workers = UNLOCK TABLES
307 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
308
309 --source include/rpl_connection_master.inc
310 INSERT INTO t1 VALUES(34, 34);
311
312 --let $mts_spco_gd_trx_blocking_worker_1 = INSERT INTO t1 VALUES(35, NULL)
313 --let $mts_spco_gd_trx_assigned_worker_2 = INSERT INTO t1 VALUES(36, NULL)
314 --let $mts_spco_gd_trx_assigned_worker_3 = INSERT INTO t1 VALUES(37, NULL)
315 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 1
316 --let $mts_spco_gd_trx_blocking_client = FLUSH TABLES WITH READ LOCK
317 --let $mts_spco_gd_state_blocking_client = Waiting for commit lock
318 --let $mts_spco_gd_wait_for_coordinator_running_state = Replica has read all relay log; waiting for more updates
319 --let $mts_spco_gd_error_expected_replica = ER_REPLICA_WORKER_STOPPED_PREVIOUS_THD_ERROR
320 --let $mts_spco_gd_trx_to_client_unblocking_workers = UNLOCK TABLES
321 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
322
323 --echo
324 --echo #
325 --echo # TC5. DML issued by client and `XA PREPARE` replicated from source.
326 --echo #------------------------------------------------------------------------
327 --source include/rpl_connection_master.inc
328 INSERT INTO t1 VALUES(41, 41);
329
330 --let $mts_spco_gd_trx_blocking_worker_1 = XA START 'xa1'; INSERT INTO t1 VALUES(42, NULL); XA END 'xa1'; XA PREPARE 'xa1'
331 --let $mts_spco_gd_worker_3_only_runs_after_deadlock = 0
332 --let $mts_spco_gd_trx_blocking_client = BEGIN; INSERT INTO t1 VALUES(42, NULL)
333 --let $mts_spco_gd_state_blocking_client = None
334 --let $mts_spco_gd_error_expected_replica = ER_LOCK_WAIT_TIMEOUT
335 --let $mts_spco_gd_trx_to_client_unblocking_workers = ROLLBACK
336 --let $mts_spco_gd_trx_finishing_group = XA COMMIT 'xa1'
337 --source extra/rpl_tests/mts_spco_generate_deadlock.inc
338
339 --source include/start_slave_sql.inc
340 --source include/rpl_connection_master.inc
341 DROP TABLE t1;
342 DROP TABLE t2;
343 --source include/rpl_end.inc
344
345 --source extra/rpl_tests/mts_spco_generate_deadlock_cleanup.inc