Debian Codegraph @

mysql-test/suite/galera/t/MDEV-20616.test

raw
1 #
2 # Test different deadlock scenarios in innodb and make sure that
3 # wsrep patch does not handle them as BF aborts.
4 #
5
6 --source include/galera_cluster.inc
7 --source include/have_debug.inc
8 --source include/have_debug_sync.inc
9
10 ##############################################################################
11 # test case to verify that natural deadlock of trigger SP execution is
12 # handled correctly
13 ##############################################################################
14
15 --echo
16 --echo Test phase 1 to make sure that natral deadlock in trigger SP execution is
17 --echo handled correctly
18 --echo
19 --let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
20
21 CREATE TABLE t1(a INT);
22 CREATE TABLE t2(f1 INT, f2 INT, f3 INT);
23 --disable_query_log
24 let $run=1000;
25 while($run)
26 {
27 INSERT INTO t2 VALUES (1, 2, 3);
28 dec $run;
29 }
30 --enable_query_log
31
32 DELIMITER |;
33 CREATE PROCEDURE proc()
34 BEGIN
35 INSERT INTO t2 VALUES(100, 200, 300);
36 UPDATE t2 SET f3 = f3 + 100;
37 END|
38 DELIMITER ;|
39
40 CREATE TRIGGER t1 BEFORE INSERT ON t1 FOR EACH ROW CALL proc();
41
42 --send INSERT INTO t1 VALUES(2);
43
44 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
45 --send INSERT INTO t1 VALUES(1);
46
47 --connection node_1
48 --error 0,ER_LOCK_DEADLOCK
49 --reap
50
51 --connection node_1a
52 --error 0,ER_LOCK_DEADLOCK
53 --reap
54
55 --connection node_1
56 --let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
57 --disable_query_log
58 --eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts;
59 --enable_query_log
60 DROP TABLE t1;
61 DROP TABLE t2;
62 DROP PROCEDURE proc;
63
64 ##############################################################################
65 #
66 # test case to verify that BF abort for SP execution is handled correctly
67 #
68 ##############################################################################
69
70 --echo
71 --echo Test phase 2 to make sure that BF abort for SP execution is
72 --echo handled correctly
73 --echo
74 --connection node_1
75 SET SESSION wsrep_retry_autocommit = 0;
76 SET SESSION wsrep_sync_wait = 0;
77 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1));
78
79 # Control connection for Galera sync point management
80 --connection node_1a
81
82 SET SESSION wsrep_retry_autocommit = 0;
83 SET SESSION wsrep_sync_wait = 0;
84 --let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
85
86 DELIMITER |;
87 CREATE PROCEDURE proc_update()
88 BEGIN
89 UPDATE t1 SET f2 = 'b';
90 END|
91 DELIMITER ;|
92
93 INSERT INTO t1 VALUES(1, 'a');
94
95 --connection node_1
96 SET debug_sync='wsrep_before_certification SIGNAL ready WAIT_FOR cont';
97 --send CALL proc_update
98
99 --connection node_1a
100 SET debug_sync='now WAIT_FOR ready';
101
102 --connection node_2
103 UPDATE t1 SET f2='c';
104
105 --connection node_1a
106 # wait for BF to happen
107 --let $wait_condition = SELECT VARIABLE_VALUE = $aborts_old + 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'
108 --source include/wait_condition.inc
109
110 SET debug_sync='now SIGNAL cont';
111
112 --connection node_1
113 --error ER_LOCK_DEADLOCK
114 --reap
115
116 --connection node_1a
117 SET debug_sync='RESET';
118
119 DROP PROCEDURE proc_update;
120
121
122 ##############################################################################
123 #
124 # test case to verify that natural deadlock does not cause BF abort
125 #
126 ##############################################################################
127
128 --connection node_1
129 --echo
130 --echo Test phase 3 to make sure natural deadlock is not treated as BF abort
131 --echo
132 TRUNCATE t1;
133 INSERT INTO t1 VALUES (1, 'a'), (2, 'a');
134
135 --connection node_1a
136 --let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
137
138 START TRANSACTION;
139 UPDATE t1 SET f2 = 'b' WHERE f1 = 1;
140
141 --connection node_1
142 START TRANSACTION;
143 UPDATE t1 SET f2 = 'c' WHERE f1 = 2;
144
145 --connection node_1a
146 # this hangs for lock wait
147 --send UPDATE t1 SET f2 = 'b' WHERE f1 = 2
148
149 #
150 # classic deadlock happens here
151 #
152 --connection node_1
153 --error 0, ER_LOCK_DEADLOCK
154 UPDATE t1 SET f2 = 'c' WHERE f1 = 1;
155
156 --connection node_1a
157 --error 0, ER_LOCK_DEADLOCK
158 --reap
159 COMMIT;
160
161 #
162 # either one of SP executions was aborted because of natural deadlock, or in worst case
163 # they were ordered seqeuntailly, and both succeeded.
164 # anyways, we just check here that no BF aborts happened
165 #
166 --let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
167 --disable_query_log
168 --eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts;
169 --enable_query_log
170
171 --connection node_1
172 ROLLBACK;
173
174 ##############################################################################
175 #
176 # test case to verify that natural deadlock within SP exceution
177 # does not cause BF abort
178 #
179 ##############################################################################
180
181 --echo
182 --echo Test phase 4 to make sure natural deadlock inside SP execution
183 --echo is not treated as BF abort
184 --echo
185
186 --connection node_1a
187 TRUNCATE t1;
188 INSERT INTO t1 VALUES (1, 'a'), (2, 'a');
189
190 --let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
191
192 DELIMITER |;
193 CREATE PROCEDURE proc_update_1()
194 BEGIN
195 START TRANSACTION;
196 UPDATE t1 SET f2 = 'b' WHERE f1 = 1;
197 SELECT SLEEP(5);
198 UPDATE t1 SET f2 = 'b' WHERE f1 = 2;
199 COMMIT;
200 END|
201 DELIMITER ;|
202
203 DELIMITER |;
204 CREATE PROCEDURE proc_update_2()
205 BEGIN
206 START TRANSACTION;
207 UPDATE t1 SET f2 = 'c' WHERE f1 = 2;
208 SELECT SLEEP(5);
209 UPDATE t1 SET f2 = 'c' WHERE f1 = 1;
210 COMMIT;
211 END|
212 DELIMITER ;|
213
214 --connection node_1
215 --send CALL proc_update_1
216
217 --connection node_1a
218 #
219 # calling proc_update_2 should cause a natural deadlock
220 # however, this test is not deterministic, and depends on the sleep() to
221 # cause expected ordering for update statement execution within SPs
222 # We therefore, allow both success and deadlock error for the result
223 #
224 --error 0, ER_LOCK_DEADLOCK
225 CALL proc_update_2;
226
227 #
228 # either one of SP executions was aborted because of natural deadlock, or in worst case
229 # they were ordered seqeuntailly, and both succeeded.
230 # anyways, we just check here that no BF aborts happened
231 #
232 --let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'`
233 --disable_query_log
234 --eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts;
235 --enable_query_log
236
237
238 --connection node_1
239 --error 0, ER_LOCK_DEADLOCK
240 --reap
241
242 DROP PROCEDURE proc_update_1;
243 DROP PROCEDURE proc_update_2;
244 DROP TABLE t1;