Debian Codegraph @

mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result

raw
1 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
2 insert into t1 values(1, Point(1,1));
3 insert into t1 values(2, Point(2,2));
4 insert into t1 values(3, Point(3,3));
5 insert into t1 values(4, Point(4,4));
6 insert into t1 values(5, Point(5,5));
7 insert into t1 values(6, Point(6,6));
8 insert into t1 values(7, Point(7,7));
9 insert into t1 values(8, Point(8,8));
10 insert into t1 values(9, Point(9,9));
11 insert into t1 select * from t1;
12 insert into t1 select * from t1;
13 insert into t1 select * from t1;
14 insert into t1 select * from t1;
15 insert into t1 select * from t1;
16 insert into t1 select * from t1;
17 connect a,localhost,root,,;
18 SET debug_dbug='+d,rtr_pcur_move_to_next_return';
19 set session transaction isolation level serializable;
20 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
21 SET DEBUG_SYNC = 'RESET';
22 SET DEBUG_SYNC = 'row_search_for_mysql_before_return SIGNAL started WAIT_FOR go_ahead';
23 select count(*) from t1 where MBRWithin(t1.c2, @g1);;
24 connect con1,localhost,root,,;
25 set session transaction isolation level serializable;
26 SET DEBUG_SYNC = 'now WAIT_FOR started';
27 insert into t1 select * from t1;
28 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
29 connection a;
30 count(*)
31 576
32 select count(*) from t1 where MBRWithin(t1.c2, @g1);
33 count(*)
34 1152
35 connection default;
36 insert into t1 select * from t1;
37 insert into t1 select * from t1;
38 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
39 select count(*) from t1 where MBRWithin(t1.c2, @g1);
40 count(*)
41 4608
42 set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))');
43 select count(*) from t1 where MBRWithin(t1.c2, @g1);
44 count(*)
45 0
46 set session transaction isolation level serializable;
47 truncate t1;
48 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
49 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
50 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
51 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
52 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
53 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
54 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
55 insert into t1 select * from t1;
56 insert into t1 select * from t1;
57 insert into t1 select * from t1;
58 insert into t1 select * from t1;
59 connection a;
60 set session transaction isolation level serializable;
61 select @@transaction_isolation;
62 @@transaction_isolation
63 SERIALIZABLE
64 start transaction;
65 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
66 select count(*) from t1 where MBRwithin(t1.c2, @g1);
67 count(*)
68 0
69 connect b,localhost,root,,;
70 set session transaction isolation level serializable;
71 set session innodb_lock_wait_timeout = 1;
72 select @@transaction_isolation;
73 @@transaction_isolation
74 SERIALIZABLE
75 insert into t1 select * from t1;
76 insert into t1 select * from t1;
77 insert into t1 select * from t1;
78 connection a;
79 commit;
80 connection default;
81 select count(*) from t1;
82 count(*)
83 896
84 insert into t1 values (105, Point(105, 105));
85 connection a;
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 count(*)
90 1
91 connection b;
92 select @@innodb_lock_wait_timeout;
93 @@innodb_lock_wait_timeout
94 1
95 select @@transaction_isolation;
96 @@transaction_isolation
97 SERIALIZABLE
98 insert into t1 select * from t1;
99 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
100 select count(*) from t1;
101 count(*)
102 897
103 connection a;
104 select sleep(2);
105 sleep(2)
106 0
107 commit;
108 connection default;
109 truncate t1;
110 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
111 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
112 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
113 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
114 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
115 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
116 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
117 insert into t1 select * from t1;
118 insert into t1 select * from t1;
119 insert into t1 select * from t1;
120 insert into t1 select * from t1;
121 connection a;
122 set session transaction isolation level serializable;
123 select @@transaction_isolation;
124 @@transaction_isolation
125 SERIALIZABLE
126 start transaction;
127 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
128 select count(*) from t1 where MBRwithin(t1.c2, @g1);
129 count(*)
130 0
131 connection b;
132 set session transaction isolation level serializable;
133 set session innodb_lock_wait_timeout = 1;
134 select @@transaction_isolation;
135 @@transaction_isolation
136 SERIALIZABLE
137 insert into t1 select * from t1;
138 insert into t1 select * from t1;
139 insert into t1 select * from t1;
140 insert into t1 select * from t1;
141 connection a;
142 commit;
143 connection default;
144 select count(*) from t1;
145 count(*)
146 1792
147 insert into t1 values (105, Point(105, 105));
148 connection a;
149 start transaction;
150 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
151 select count(*) from t1 where MBRwithin(t1.c2, @g1);
152 count(*)
153 1
154 select count(*) from t1 where MBRIntersects(t1.c2, @g1);
155 count(*)
156 1793
157 connection b;
158 select @@innodb_lock_wait_timeout;
159 @@innodb_lock_wait_timeout
160 1
161 select @@transaction_isolation;
162 @@transaction_isolation
163 SERIALIZABLE
164 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 105, 200 105)'));
165 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
166 select count(*) from t1;
167 count(*)
168 1793
169 connection a;
170 select sleep(2);
171 sleep(2)
172 0
173 commit;
174 connection default;
175 truncate t1;
176 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
177 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
178 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
179 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
180 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
181 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
182 set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))');
183 start transaction;
184 delete from t1 where MBRWithin(t1.c2, @g1);
185 connection a;
186 set session innodb_lock_wait_timeout = 1;
187 select @@innodb_lock_wait_timeout;
188 @@innodb_lock_wait_timeout
189 1
190 insert into t1 values(4, Point(4,4));
191 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
192 connection default;
193 rollback;
194 connection default;
195 truncate t1;
196 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
197 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
198 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
199 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
200 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
201 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
202 set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))');
203 start transaction;
204 select count(*) from t1 where MBRWithin(t1.c2, @g1) for update;
205 count(*)
206 0
207 connection a;
208 set session innodb_lock_wait_timeout = 1;
209 select @@innodb_lock_wait_timeout;
210 @@innodb_lock_wait_timeout
211 1
212 insert into t1 values(4, Point(4,4));
213 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
214 connection default;
215 rollback;
216 connection default;
217 truncate t1;
218 create procedure insert_t1(IN start int, IN total int)
219 begin
220 declare i int default 1;
221 set i = start;
222 while (i <= total) DO
223 insert into t1 values (i, Point(i, i));
224 set i = i + 1;
225 end while;
226 end|
227 CALL insert_t1(0, 1000);
228 connection a;
229 set session transaction isolation level serializable;
230 select @@transaction_isolation;
231 @@transaction_isolation
232 SERIALIZABLE
233 start transaction;
234 set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))');
235 select count(*) from t1 where MBRwithin(t1.c2, @g1);
236 count(*)
237 199
238 connection b;
239 CALL insert_t1(1001, 2000);
240 set session transaction isolation level serializable;
241 set session innodb_lock_wait_timeout = 1;
242 insert into t1 values (1200, Point(950, 950));
243 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
244 connection a;
245 select sleep(2);
246 sleep(2)
247 0
248 commit;
249 disconnect a;
250 disconnect b;
251 connection default;
252 drop table t1;
253 drop procedure insert_t1;
254 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
255 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
256 INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(3 3, 160 160)'));
257 INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(4 4, 170 170)'));
258 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
259 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
260 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
261 INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
262 connect a,localhost,root,,;
263 SET SESSION debug_dbug='+d,rtr_pcur_move_to_next_return';
264 set transaction isolation level serializable;
265 start transaction;
266 set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))');
267 select count(*) from t1 where MBRwithin(t1.c2, @g1);
268 count(*)
269 0
270 connect b,localhost,root,,;
271 delete from t1 where c1 = 1;
272 connection a;
273 commit;
274 set transaction isolation level serializable;
275 start transaction;
276 set @g1 = ST_GeomFromText('Polygon((0 0, 0 300, 300 300, 300 0, 0 0))');
277 select count(*) from t1 where MBRwithin(t1.c2, @g1);
278 count(*)
279 2
280 connection b;
281 set session innodb_lock_wait_timeout = 1;
282 delete from t1 where c1 = 2;
283 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
284 connection a;
285 commit;
286 connection default;
287 drop table t1;
288 SET DEBUG_SYNC= 'RESET';
289 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
290 start transaction;
291 insert into t1 values(1, Point(1,1));
292 insert into t1 values(2, Point(2,2));
293 insert into t1 values(3, Point(3,3));
294 insert into t1 values(4, Point(4,4));
295 insert into t1 values(5, Point(5,5));
296 insert into t1 values(6, Point(6,6));
297 insert into t1 values(7, Point(7,7));
298 insert into t1 values(8, Point(8,8));
299 insert into t1 values(9, Point(9,9));
300 insert into t1 select * from t1;
301 insert into t1 select * from t1;
302 insert into t1 select * from t1;
303 insert into t1 select * from t1;
304 insert into t1 select * from t1;
305 insert into t1 select * from t1;
306 insert into t1 select * from t1;
307 insert into t1 select * from t1;
308 insert into t1 select * from t1;
309 select count(*) from t1;
310 count(*)
311 4608
312 connection b;
313 set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
314 set transaction isolation level read uncommitted;
315 SET DEBUG_SYNC= 'row_search_for_mysql_before_return SIGNAL siga WAIT_FOR sigb';
316 select count(*) from t1 where MBRWithin(t1.c2, @g1);
317 connection default;
318 SET DEBUG_SYNC= 'now WAIT_FOR siga';
319 rollback;
320 SET DEBUG_SYNC= 'now SIGNAL sigb';
321 connection b;
322 count(*)
323 1
324 select count(*) from t1 where MBRWithin(t1.c2, @g1);
325 count(*)
326 0
327 connection default;
328 DROP TABLE t1;
329 SET DEBUG_SYNC = 'RESET';
330 create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
331 insert into t1 values(100, point(100, 100));
332 create procedure insert_t1(IN total int)
333 begin
334 declare i int default 1;
335 while (i <= total) DO
336 insert into t1 values (i, Point(i, i));
337 set i = i + 1;
338 end while;
339 end|
340 start transaction;
341 CALL insert_t1(100);
342 connection a;
343 set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
344 SET DEBUG_SYNC= 'rtr_pcur_move_to_next_return SIGNAL siga WAIT_FOR sigb';
345 select count(*) from t1 where MBRWithin(t1.c2, @g1);;
346 connection default;
347 SET DEBUG_SYNC= 'now WAIT_FOR siga';
348 rollback;
349 SET DEBUG_SYNC= 'now SIGNAL sigb';
350 connection a;
351 count(*)
352 1
353 connection default;
354 drop procedure insert_t1;
355 DROP TABLE t1;
356 disconnect a;
357 disconnect b;
358 connection default;
359 SET DEBUG_SYNC = 'RESET';