Debian Codegraph @

mysql-test/suite/innodb/r/update-cascade.result

raw
1 #
2 # Bug #18451287 REDUNDANT DELETE MARKING AFTER DB_LOCK_WAIT
3 #
4 create table t1 (f1 int primary key, f2 blob) engine=innodb;
5 create table t2 (f1 int primary key, f2 int,
6 foreign key (f2) references t1(f1) on update cascade) engine=innodb;
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `f1` int(11) NOT NULL,
11 `f2` blob DEFAULT NULL,
12 PRIMARY KEY (`f1`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
14 show create table t2;
15 Table Create Table
16 t2 CREATE TABLE `t2` (
17 `f1` int(11) NOT NULL,
18 `f2` int(11) DEFAULT NULL,
19 PRIMARY KEY (`f1`),
20 KEY `f2` (`f2`),
21 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE
22 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
23 insert into t1 values (1, repeat('+', 20000));
24 insert into t1 values (2, repeat('-', 20000));
25 insert into t1 values (3, repeat('=', 20000));
26 insert into t2 values (1, 2);
27 select f1, right(f2, 20) as p2 from t1;
28 f1 p2
29 1 ++++++++++++++++++++
30 2 --------------------
31 3 ====================
32 select f1, f2 from t2;
33 f1 f2
34 1 2
35 connect con1,localhost,root,,test;
36 start transaction;
37 select f1, f2 from t2 for update;
38 f1 f2
39 1 2
40 connection default;
41 set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd';
42 update t1 set f1 = 10 where f1 = 2;
43 connection con1;
44 set debug_sync='now WAIT_FOR upd_waiting';
45 rollback;
46 set debug_sync='now SIGNAL go_upd';
47 connection default;
48 # reap: update t1 set f1 = 10 where f1 = 2;
49 select f1, right(f2, 20) as p2 from t1;
50 f1 p2
51 1 ++++++++++++++++++++
52 3 ====================
53 10 --------------------
54 select f1, f2 from t2;
55 f1 f2
56 1 10
57 drop table t2, t1;
58 set debug_sync = reset;
59 #
60 # Test Scenario: Two tables t1 -> t2 are involved in update cascade.
61 # If DB_LOCK_WAIT happens when t1 is being updated and FK constraints
62 # are being checked in t2, then retry must happen on t1. The update
63 # cascade happens in secondary index. For secondary index testing,
64 # blobs are not needed.
65 #
66 create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
67 create table t2 (f1 int primary key, f2 int,
68 foreign key (f2) references t1(f2) on update cascade) engine=innodb;
69 show create table t1;
70 Table Create Table
71 t1 CREATE TABLE `t1` (
72 `f1` int(11) NOT NULL,
73 `f2` int(11) DEFAULT NULL,
74 PRIMARY KEY (`f1`),
75 KEY `k1` (`f2`)
76 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
77 show create table t2;
78 Table Create Table
79 t2 CREATE TABLE `t2` (
80 `f1` int(11) NOT NULL,
81 `f2` int(11) DEFAULT NULL,
82 PRIMARY KEY (`f1`),
83 KEY `f2` (`f2`),
84 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
86 insert into t1 values (1, 91);
87 insert into t2 values (1, 91);
88 select f1, f2 from t1;
89 f1 f2
90 1 91
91 select f1, f2 from t2;
92 f1 f2
93 1 91
94 connection con1;
95 start transaction;
96 select f1, f2 from t2 for update;
97 f1 f2
98 1 91
99 connection default;
100 set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd';
101 update t1 set f2 = 28 where f2 = 91;
102 connection con1;
103 set debug_sync='now WAIT_FOR upd_waiting';
104 rollback;
105 set debug_sync='now SIGNAL go_upd';
106 connection default;
107 # reap: update t1 set f1 = 10 where f1 = 2;
108 select f1, f2 from t1;
109 f1 f2
110 1 28
111 select f1, f2 from t2;
112 f1 f2
113 1 28
114 drop table t2, t1;
115 set debug_sync = reset;
116 #
117 # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update cascade.
118 # If DB_LOCK_WAIT happens when t2 is being updated, then retry must happen
119 # on t2.
120 #
121 create table t1 (f1 int primary key, f2 blob) engine=innodb;
122 create table t2 (f1 int primary key, f2 blob,
123 foreign key (f1) references t1(f1) on update cascade) engine=innodb;
124 create table t3 (f1 int primary key, f2 blob,
125 foreign key (f1) references t2(f1) on update cascade) engine=innodb;
126 show create table t1;
127 Table Create Table
128 t1 CREATE TABLE `t1` (
129 `f1` int(11) NOT NULL,
130 `f2` blob DEFAULT NULL,
131 PRIMARY KEY (`f1`)
132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
133 show create table t2;
134 Table Create Table
135 t2 CREATE TABLE `t2` (
136 `f1` int(11) NOT NULL,
137 `f2` blob DEFAULT NULL,
138 PRIMARY KEY (`f1`),
139 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
141 show create table t3;
142 Table Create Table
143 t3 CREATE TABLE `t3` (
144 `f1` int(11) NOT NULL,
145 `f2` blob DEFAULT NULL,
146 PRIMARY KEY (`f1`),
147 CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t2` (`f1`) ON UPDATE CASCADE
148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
149 insert into t1 values (2, repeat('-', 20000));
150 insert into t2 values (2, repeat('%', 20000));
151 insert into t3 values (2, repeat('+', 20000));
152 select f1, right(f2, 20) as p2 from t1;
153 f1 p2
154 2 --------------------
155 select f1, right(f2, 20) as p2 from t2;
156 f1 p2
157 2 %%%%%%%%%%%%%%%%%%%%
158 select f1, right(f2, 20) as p2 from t3;
159 f1 p2
160 2 ++++++++++++++++++++
161 connection con1;
162 start transaction;
163 select f1 from t3 for update;
164 f1
165 2
166 connection default;
167 set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd';
168 update t1 set f1 = 10 where f1 = 2;
169 connection con1;
170 set debug_sync='now WAIT_FOR upd_waiting';
171 rollback;
172 # The table t1 is updated.
173 # In t2 delete-mark happened. Retry will happen on t2.
174 # In t3 yet to be updated.
175 set session transaction isolation level read uncommitted;
176 start transaction;
177 select f1, right(f2, 20) as p2 from t1;
178 f1 p2
179 select f1, right(f2, 20) as p2 from t2;
180 f1 p2
181 select f1, right(f2, 20) as p2 from t3;
182 f1 p2
183 2 ++++++++++++++++++++
184 commit;
185 set debug_sync='now SIGNAL go_upd';
186 connection default;
187 # reap: update t1 set f1 = 10 where f1 = 2;
188 start transaction;
189 select f1, right(f2, 20) as p2 from t1;
190 f1 p2
191 10 --------------------
192 select f1, right(f2, 20) as p2 from t2;
193 f1 p2
194 10 %%%%%%%%%%%%%%%%%%%%
195 select f1, right(f2, 20) as p2 from t3;
196 f1 p2
197 10 ++++++++++++++++++++
198 commit;
199 drop table t3, t2, t1;
200 set debug_sync = reset;
201 #
202 # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update
203 # cascade. If DB_LOCK_WAIT happens when t2 is being updated, then
204 # retry must happen on t2. The update cascade is happening via
205 # secondary index (hence blobs are not needed).
206 #
207 create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
208 create table t2 (f1 int primary key, f2 int,
209 foreign key (f2) references t1(f2) on update cascade) engine=innodb;
210 create table t3 (f1 int primary key, f2 int,
211 foreign key (f2) references t2(f2) on update cascade) engine=innodb;
212 show create table t1;
213 Table Create Table
214 t1 CREATE TABLE `t1` (
215 `f1` int(11) NOT NULL,
216 `f2` int(11) DEFAULT NULL,
217 PRIMARY KEY (`f1`),
218 KEY `k1` (`f2`)
219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
220 show create table t2;
221 Table Create Table
222 t2 CREATE TABLE `t2` (
223 `f1` int(11) NOT NULL,
224 `f2` int(11) DEFAULT NULL,
225 PRIMARY KEY (`f1`),
226 KEY `f2` (`f2`),
227 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE
228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
229 show create table t3;
230 Table Create Table
231 t3 CREATE TABLE `t3` (
232 `f1` int(11) NOT NULL,
233 `f2` int(11) DEFAULT NULL,
234 PRIMARY KEY (`f1`),
235 KEY `f2` (`f2`),
236 CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t2` (`f2`) ON UPDATE CASCADE
237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
238 insert into t1 values (2, 91);
239 insert into t2 values (2, 91);
240 insert into t3 values (2, 91);
241 select f1, f2 from t1;
242 f1 f2
243 2 91
244 select f1, f2 from t2;
245 f1 f2
246 2 91
247 select f1, f2 from t3;
248 f1 f2
249 2 91
250 connection con1;
251 start transaction;
252 select f1 from t3 for update;
253 f1
254 2
255 connection default;
256 set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd';
257 update t1 set f2 = 28 where f2 = 91;
258 connection con1;
259 set debug_sync='now WAIT_FOR upd_waiting';
260 rollback;
261 # The table t1 is updated.
262 # In t2 delete-mark happened. Retry will happen on t2.
263 # In t3 yet to be updated.
264 set session transaction isolation level read uncommitted;
265 start transaction;
266 select f1, f2 from t1;
267 f1 f2
268 select f1, f2 from t2;
269 f1 f2
270 select f1, f2 from t3;
271 f1 f2
272 2 91
273 commit;
274 set debug_sync='now SIGNAL go_upd';
275 disconnect con1;
276 connection default;
277 # reap: update t1 set f2 = 28 where f2 = 91;
278 start transaction;
279 select f1, f2 from t1;
280 f1 f2
281 2 28
282 select f1, f2 from t2;
283 f1 f2
284 2 28
285 select f1, f2 from t3;
286 f1 f2
287 2 28
288 commit;
289 drop table t3, t2, t1;
290 set debug_sync = reset;