Debian Codegraph @

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

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