Debian Codegraph @

mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test

raw
1 # WL#6745 InnoDB R-tree support
2 # This test case will test R-tree split.
3
4 --source include/have_innodb.inc
5 --source include/have_debug.inc
6 --source include/have_debug_sync.inc
7 --source include/big_test.inc
8
9 # Create table with R-tree index.
10 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
11
12 # Insert enough values to let R-tree split.
13 insert into t1 values(1, Point(1,1));
14 insert into t1 values(2, Point(2,2));
15 insert into t1 values(3, Point(3,3));
16 insert into t1 values(4, Point(4,4));
17 insert into t1 values(5, Point(5,5));
18 insert into t1 values(6, Point(6,6));
19 insert into t1 values(7, Point(7,7));
20 insert into t1 values(8, Point(8,8));
21 insert into t1 values(9, Point(9,9));
22
23 insert into t1 select * from t1;
24 insert into t1 select * from t1;
25 insert into t1 select * from t1;
26 insert into t1 select * from t1;
27
28 insert into t1 select * from t1;
29 insert into t1 select * from t1;
30
31 connect (a,localhost,root,,);
32 SET debug_dbug='+d,rtr_pcur_move_to_next_return';
33
34 set session transaction isolation level serializable;
35 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
36 SET DEBUG_SYNC = 'RESET';
37 SET DEBUG_SYNC = 'row_search_for_mysql_before_return SIGNAL started WAIT_FOR go_ahead';
38 --send select count(*) from t1 where MBRWithin(t1.c2, @g1);
39
40 connect (con1,localhost,root,,);
41 set session transaction isolation level serializable;
42
43 SET DEBUG_SYNC = 'now WAIT_FOR started';
44 insert into t1 select * from t1;
45 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
46
47 connection a;
48 reap;
49 select count(*) from t1 where MBRWithin(t1.c2, @g1);
50
51 connection default;
52 insert into t1 select * from t1;
53 insert into t1 select * from t1;
54
55 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
56 select count(*) from t1 where MBRWithin(t1.c2, @g1);
57
58 set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))');
59 select count(*) from t1 where MBRWithin(t1.c2, @g1);
60
61 set session transaction isolation level serializable;
62
63 truncate t1;
64
65 # Test on predicate locking
66 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
67 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
68 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
69 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
70 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
71 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
72 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
73
74 insert into t1 select * from t1;
75 insert into t1 select * from t1;
76 insert into t1 select * from t1;
77 insert into t1 select * from t1;
78
79 # Connection 'a' will place predicate lock on almost all pages
80 connection a;
81 set session transaction isolation level serializable;
82 select @@transaction_isolation;
83 start transaction;
84 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
85 select count(*) from t1 where MBRwithin(t1.c2, @g1);
86
87 # The split will replicate locks across pages
88 connect (b,localhost,root,,);
89 set session transaction isolation level serializable;
90 set session innodb_lock_wait_timeout = 1;
91
92 select @@transaction_isolation;
93
94 insert into t1 select * from t1;
95 insert into t1 select * from t1;
96 insert into t1 select * from t1;
97
98 # FIXME: Put this back once we sort out the shrink business
99 #insert into t1 select * from t1;
100
101 connection a;
102 commit;
103
104 connection default;
105 select count(*) from t1;
106
107 # Insert a record that would be in the search range
108 insert into t1 values (105, Point(105, 105));
109
110 # Connection 'a' will place predicate lock on almost all pages
111 connection a;
112 start transaction;
113 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
114 select count(*) from t1 where MBRwithin(t1.c2, @g1);
115
116 connection b;
117 select @@innodb_lock_wait_timeout;
118 select @@transaction_isolation;
119
120 --error ER_LOCK_WAIT_TIMEOUT
121 insert into t1 select * from t1;
122 select count(*) from t1;
123
124 connection a;
125 select sleep(2);
126 commit;
127
128 #==================Test predicates on "MBRIntersects"==========================
129 connection default;
130 truncate t1;
131
132 # Test on predicate locking
133 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
134 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
135 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
136 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
137 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
138 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
139 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
140
141 insert into t1 select * from t1;
142 insert into t1 select * from t1;
143 insert into t1 select * from t1;
144 insert into t1 select * from t1;
145
146 # Connection 'a' will place predicate lock on almost all pages
147 connection a;
148 set session transaction isolation level serializable;
149 select @@transaction_isolation;
150 start transaction;
151 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
152 select count(*) from t1 where MBRwithin(t1.c2, @g1);
153
154 # The split will replicate locks across pages
155 connection b;
156 set session transaction isolation level serializable;
157 set session innodb_lock_wait_timeout = 1;
158
159 select @@transaction_isolation;
160
161 insert into t1 select * from t1;
162 insert into t1 select * from t1;
163 insert into t1 select * from t1;
164 insert into t1 select * from t1;
165
166 connection a;
167 commit;
168
169 connection default;
170 select count(*) from t1;
171
172 # Insert a record that would be in the search range
173 insert into t1 values (105, Point(105, 105));
174
175 # Connection 'a' will place predicate lock on almost all pages
176 connection a;
177 start transaction;
178 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
179 select count(*) from t1 where MBRwithin(t1.c2, @g1);
180 select count(*) from t1 where MBRIntersects(t1.c2, @g1);
181
182 connection b;
183 select @@innodb_lock_wait_timeout;
184 select @@transaction_isolation;
185
186 # this should conflict with the "MBRIntersects" predicate lock in session "a"
187 --error ER_LOCK_WAIT_TIMEOUT
188 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 105, 200 105)'));
189 select count(*) from t1;
190
191 connection a;
192 select sleep(2);
193 commit;
194
195 #==================Test predicate lock on "delete"==========================
196 connection default;
197 truncate t1;
198
199 # Test on predicate locking
200 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
201 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
202 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
203 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
204 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
205 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
206
207 # Connection default will place predicate lock on follow range
208 set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))');
209 start transaction;
210 delete from t1 where MBRWithin(t1.c2, @g1);
211
212 connection a;
213 set session innodb_lock_wait_timeout = 1;
214 select @@innodb_lock_wait_timeout;
215 --error ER_LOCK_WAIT_TIMEOUT
216 insert into t1 values(4, Point(4,4));
217
218 connection default;
219 rollback;
220
221 #==================Test predicate lock on "select for update"==================
222 connection default;
223 truncate t1;
224
225 # Test on predicate locking
226 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
227 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
228 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
229 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
230 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
231 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
232
233 # Connection default will place predicate lock on follow range
234 set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))');
235 start transaction;
236 select count(*) from t1 where MBRWithin(t1.c2, @g1) for update;
237
238 connection a;
239 set session innodb_lock_wait_timeout = 1;
240 select @@innodb_lock_wait_timeout;
241 --error ER_LOCK_WAIT_TIMEOUT
242 insert into t1 values(4, Point(4,4));
243
244 connection default;
245 rollback;
246 #==================Test predicates replicate through split =================
247 connection default;
248 truncate t1;
249
250 delimiter |;
251 create procedure insert_t1(IN start int, IN total int)
252 begin
253 declare i int default 1;
254 set i = start;
255 while (i <= total) DO
256 insert into t1 values (i, Point(i, i));
257 set i = i + 1;
258 end while;
259 end|
260 delimiter ;|
261
262 CALL insert_t1(0, 1000);
263
264 # Connection 'a' will place predicate lock on root and last leaf page
265 connection a;
266 set session transaction isolation level serializable;
267 select @@transaction_isolation;
268 start transaction;
269 set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))');
270 select count(*) from t1 where MBRwithin(t1.c2, @g1);
271
272 # Connection 'b' will split the last leaf page, so the predicate
273 # lock should replicate
274 connection b;
275
276 CALL insert_t1(1001, 2000);
277
278 # This insert goes to the new page after split, it should be blocked
279 set session transaction isolation level serializable;
280 set session innodb_lock_wait_timeout = 1;
281 # Insert a record that would be in the search range
282 --error ER_LOCK_WAIT_TIMEOUT
283 insert into t1 values (1200, Point(950, 950));
284
285 connection a;
286 select sleep(2);
287 commit;
288 disconnect a;
289 disconnect b;
290
291 # Clean up.
292 connection default;
293 drop table t1;
294
295 drop procedure insert_t1;
296
297 #============ Test row locks =======================
298 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
299
300
301 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
302 INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(3 3, 160 160)'));
303 INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(4 4, 170 170)'));
304 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
305 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
306 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
307 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
308
309 connect (a,localhost,root,,);
310 SET SESSION debug_dbug='+d,rtr_pcur_move_to_next_return';
311
312 set transaction isolation level serializable;
313 start transaction;
314 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
315 select count(*) from t1 where MBRwithin(t1.c2, @g1);
316
317 connect (b,localhost,root,,);
318
319 # This should be successful
320 delete from t1 where c1 = 1;
321
322 connection a;
323 commit;
324 set transaction isolation level serializable;
325 start transaction;
326 set @g1 = ST_GeomFromText('Polygon((0 0, 0 300, 300 300, 300 0, 0 0))');
327 select count(*) from t1 where MBRwithin(t1.c2, @g1);
328
329 connection b;
330
331 set session innodb_lock_wait_timeout = 1;
332 --error ER_LOCK_WAIT_TIMEOUT
333 delete from t1 where c1 = 2;
334
335 # Clean up.
336 connection a;
337 commit;
338
339 connection default;
340 drop table t1;
341 SET DEBUG_SYNC= 'RESET';
342
343 # Test btr_discard_page adjust concurrent search path
344 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
345
346 start transaction;
347 insert into t1 values(1, Point(1,1));
348 insert into t1 values(2, Point(2,2));
349 insert into t1 values(3, Point(3,3));
350 insert into t1 values(4, Point(4,4));
351 insert into t1 values(5, Point(5,5));
352 insert into t1 values(6, Point(6,6));
353 insert into t1 values(7, Point(7,7));
354 insert into t1 values(8, Point(8,8));
355 insert into t1 values(9, Point(9,9));
356
357 insert into t1 select * from t1;
358 insert into t1 select * from t1;
359 insert into t1 select * from t1;
360 insert into t1 select * from t1;
361
362 insert into t1 select * from t1;
363 insert into t1 select * from t1;
364 insert into t1 select * from t1;
365 insert into t1 select * from t1;
366
367 insert into t1 select * from t1;
368
369 select count(*) from t1;
370
371 connection b;
372 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
373 set transaction isolation level read uncommitted;
374 SET DEBUG_SYNC= 'row_search_for_mysql_before_return SIGNAL siga WAIT_FOR sigb';
375 send select count(*) from t1 where MBRWithin(t1.c2, @g1);
376
377 connection default;
378 SET DEBUG_SYNC= 'now WAIT_FOR siga';
379 rollback;
380 SET DEBUG_SYNC= 'now SIGNAL sigb';
381
382 connection b;
383 --reap
384 select count(*) from t1 where MBRWithin(t1.c2, @g1);
385
386 connection default;
387 DROP TABLE t1;
388 SET DEBUG_SYNC = 'RESET';
389
390 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
391 insert into t1 values(100, point(100, 100));
392
393 delimiter |;
394 create procedure insert_t1(IN total int)
395 begin
396 declare i int default 1;
397 while (i <= total) DO
398 insert into t1 values (i, Point(i, i));
399 set i = i + 1;
400 end while;
401 end|
402 delimiter ;|
403
404 start transaction;
405
406 CALL insert_t1(100);
407
408 connection a;
409 set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
410 SET DEBUG_SYNC= 'rtr_pcur_move_to_next_return SIGNAL siga WAIT_FOR sigb';
411 --send select count(*) from t1 where MBRWithin(t1.c2, @g1);
412
413 connection default;
414 SET DEBUG_SYNC= 'now WAIT_FOR siga';
415 rollback;
416
417 SET DEBUG_SYNC= 'now SIGNAL sigb';
418
419 connection a;
420 reap;
421
422 connection default;
423 drop procedure insert_t1;
424 DROP TABLE t1;
425
426 disconnect a;
427 disconnect b;
428
429 connection default;
430 SET DEBUG_SYNC = 'RESET';