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