Debian Codegraph @

mysql-test/suite/innodb/t/update-cascade.test

raw
1 --source include/have_debug_sync.inc
2
3 # Save the initial number of concurrent sessions
4 --source include/count_sessions.inc
5
6 --echo #
7 --echo # Bug #18451287 REDUNDANT DELETE MARKING AFTER DB_LOCK_WAIT
8 --echo #
9
10 create table t1 (f1 int primary key, f2 blob) engine=innodb;
11 create table t2 (f1 int primary key, f2 int,
12 foreign key (f2) references t1(f1) on update cascade) engine=innodb;
13
14 show create table t1;
15 show create table t2;
16 insert into t1 values (1, repeat('+', 20000));
17 insert into t1 values (2, repeat('-', 20000));
18 insert into t1 values (3, repeat('=', 20000));
19 insert into t2 values (1, 2);
20
21 select f1, right(f2, 20) as p2 from t1;
22 select f1, f2 from t2;
23
24 --echo # Connection con1:
25 connect(con1,localhost,root,,test);
26 start transaction;
27 select f1, f2 from t2 for update;
28
29 --echo # Connection default:
30 connection default;
31 set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
32 --echo # send: update t1 set f1 = 10 where f1 = 2;
33 --send
34 update t1 set f1 = 10 where f1 = 2;
35
36 --echo # Connection con1:
37 connection con1;
38 set debug_sync='now WAIT_FOR upd_waiting';
39 rollback;
40 set debug_sync='now SIGNAL go_upd';
41
42 --echo # Connection default:
43 connection default;
44 --echo # reap: update t1 set f1 = 10 where f1 = 2;
45 --reap
46
47 select f1, right(f2, 20) as p2 from t1;
48 select f1, f2 from t2;
49
50 drop table t2, t1;
51
52 set debug_sync = reset;
53
54 --echo #
55 --echo # Test Scenario: Two tables t1 -> t2 are involved in update cascade.
56 --echo # If DB_LOCK_WAIT happens when t1 is being updated and FK constraints
57 --echo # are being checked in t2, then retry must happen on t1. The update
58 --echo # cascade happens in secondary index. For secondary index testing,
59 --echo # blobs are not needed.
60 --echo #
61
62 create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
63 create table t2 (f1 int primary key, f2 int,
64 foreign key (f2) references t1(f2) on update cascade) engine=innodb;
65
66 show create table t1;
67 show create table t2;
68 insert into t1 values (1, 91);
69 insert into t2 values (1, 91);
70
71 select f1, f2 from t1;
72 select f1, f2 from t2;
73
74 --echo # Connection con1:
75 connection con1;
76 start transaction;
77 select f1, f2 from t2 for update;
78
79 --echo # Connection default:
80 connection default;
81 set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
82 --echo # send: update t1 set f1 = 10 where f1 = 2;
83 --send
84 update t1 set f2 = 28 where f2 = 91;
85
86 --echo # Connection con1:
87 connection con1;
88 set debug_sync='now WAIT_FOR upd_waiting';
89 rollback;
90 set debug_sync='now SIGNAL go_upd';
91
92 --echo # Connection default:
93 connection default;
94 --echo # reap: update t1 set f1 = 10 where f1 = 2;
95 --reap
96
97 select f1, f2 from t1;
98 select f1, f2 from t2;
99
100 drop table t2, t1;
101
102 set debug_sync = reset;
103
104 --echo #
105 --echo # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update cascade.
106 --echo # If DB_LOCK_WAIT happens when t2 is being updated, then retry must happen
107 --echo # on t2.
108 --echo #
109
110 create table t1 (f1 int primary key, f2 blob) engine=innodb;
111 create table t2 (f1 int primary key, f2 blob,
112 foreign key (f1) references t1(f1) on update cascade) engine=innodb;
113 create table t3 (f1 int primary key, f2 blob,
114 foreign key (f1) references t2(f1) on update cascade) engine=innodb;
115
116 show create table t1;
117 show create table t2;
118 show create table t3;
119
120 insert into t1 values (2, repeat('-', 20000));
121 insert into t2 values (2, repeat('%', 20000));
122 insert into t3 values (2, repeat('+', 20000));
123
124 select f1, right(f2, 20) as p2 from t1;
125 select f1, right(f2, 20) as p2 from t2;
126 select f1, right(f2, 20) as p2 from t3;
127
128 --echo # Connection con1:
129 connection con1;
130 start transaction;
131 select f1 from t3 for update;
132
133 --echo # Connection default:
134 connection default;
135 set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
136 --echo # send: update t1 set f1 = 10 where f1 = 2;
137 --send
138 update t1 set f1 = 10 where f1 = 2;
139
140 --echo # Connection con1:
141 connection con1;
142 set debug_sync='now WAIT_FOR upd_waiting';
143 rollback;
144
145 --echo # The table t1 is updated.
146 --echo # In t2 delete-mark happened. Retry will happen on t2.
147 --echo # In t3 yet to be updated.
148 set session transaction isolation level read uncommitted;
149 start transaction;
150 select f1, right(f2, 20) as p2 from t1;
151 select f1, right(f2, 20) as p2 from t2;
152 select f1, right(f2, 20) as p2 from t3;
153 commit;
154
155 set debug_sync='now SIGNAL go_upd';
156
157 --echo # Connection default:
158 connection default;
159 --echo # reap: update t1 set f1 = 10 where f1 = 2;
160 --reap;
161
162 start transaction;
163 select f1, right(f2, 20) as p2 from t1;
164 select f1, right(f2, 20) as p2 from t2;
165 select f1, right(f2, 20) as p2 from t3;
166 commit;
167
168 drop table t3, t2, t1;
169
170 set debug_sync = reset;
171
172 --echo #
173 --echo # Test Scenario: Three tables t1 -> t2 -> t3 are involved in update
174 --echo # cascade. If DB_LOCK_WAIT happens when t2 is being updated, then
175 --echo # retry must happen on t2. The update cascade is happening via
176 --echo # secondary index (hence blobs are not needed).
177 --echo #
178
179 create table t1 (f1 int primary key, f2 int, key k1(f2)) engine=innodb;
180 create table t2 (f1 int primary key, f2 int,
181 foreign key (f2) references t1(f2) on update cascade) engine=innodb;
182 create table t3 (f1 int primary key, f2 int,
183 foreign key (f2) references t2(f2) on update cascade) engine=innodb;
184
185 show create table t1;
186 show create table t2;
187 show create table t3;
188
189 insert into t1 values (2, 91);
190 insert into t2 values (2, 91);
191 insert into t3 values (2, 91);
192
193 select f1, f2 from t1;
194 select f1, f2 from t2;
195 select f1, f2 from t3;
196
197 --echo # Connection con1:
198 connection con1;
199 start transaction;
200 select f1 from t3 for update;
201
202 --echo # Connection default:
203 connection default;
204 set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd';
205 --echo # send: update t1 set f2 = 28 where f2 = 91;
206 --send
207 update t1 set f2 = 28 where f2 = 91;
208
209 --echo # Connection con1:
210 connection con1;
211 set debug_sync='now WAIT_FOR upd_waiting';
212 rollback;
213
214 --echo # The table t1 is updated.
215 --echo # In t2 delete-mark happened. Retry will happen on t2.
216 --echo # In t3 yet to be updated.
217 set session transaction isolation level read uncommitted;
218 start transaction;
219 select f1, f2 from t1;
220 select f1, f2 from t2;
221 select f1, f2 from t3;
222 commit;
223
224 set debug_sync='now SIGNAL go_upd';
225
226 --echo # Connection default:
227 connection default;
228 disconnect con1;
229 --echo # reap: update t1 set f2 = 28 where f2 = 91;
230 --reap;
231
232 start transaction;
233 select f1, f2 from t1;
234 select f1, f2 from t2;
235 select f1, f2 from t3;
236 commit;
237
238 drop table t3, t2, t1;
239
240 set debug_sync = reset;
241
242 # Wait till all disconnects are completed
243 --source include/wait_until_count_sessions.inc