Debian Codegraph @

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

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