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