Debian Codegraph @

mysql-test/suite/innodb/r/partition_locking.result

raw
1 set @start_read_only= @@global.read_only;
2 set @start_autocommit= @@global.autocommit;
3 set default_storage_engine= innodb;
4 set @@global.autocommit= 0;
5 CREATE USER test@localhost;
6 grant CREATE, SELECT, UPDATE, INSERT on *.* to test@localhost;
7 CREATE USER test2@localhost;
8 grant CREATE, SELECT, UPDATE on *.* to test2@localhost;
9 CREATE TABLE t1 (
10 a char(2) NOT NULL,
11 b char(2) NOT NULL,
12 c int(10) unsigned NOT NULL,
13 d varchar(255) DEFAULT NULL,
14 e varchar(1000) DEFAULT NULL,
15 PRIMARY KEY (a, b, c),
16 KEY (a),
17 KEY (a, b)
18 ) charset latin1 PARTITION BY KEY (a) PARTITIONS 20;
19 INSERT INTO t1 (a, b, c, d, e) VALUES
20 ('07', '03', 343, '1', '07_03_343'),
21 ('01', '04', 343, '2', '01_04_343'),
22 ('01', '06', 343, '3', '01_06_343'),
23 ('01', '07', 343, '4', '01_07_343'),
24 ('01', '08', 343, '5', '01_08_343'),
25 ('01', '09', 343, '6', '01_09_343'),
26 ('03', '03', 343, '7', '03_03_343'),
27 ('03', '06', 343, '8', '03_06_343'),
28 ('03', '07', 343, '9', '03_07_343'),
29 ('04', '03', 343, '10', '04_03_343'),
30 ('04', '06', 343, '11', '04_06_343'),
31 ('05', '03', 343, '12', '05_03_343'),
32 ('11', '03', 343, '13', '11_03_343'),
33 ('11', '04', 343, '14', '11_04_343')
34 ;
35 CREATE TABLE t2 (a int, name VARCHAR(50), purchased DATE)
36 PARTITION BY RANGE (a)
37 (PARTITION p0 VALUES LESS THAN (3),
38 PARTITION p1 VALUES LESS THAN (7),
39 PARTITION p2 VALUES LESS THAN (9),
40 PARTITION p3 VALUES LESS THAN (11));
41 INSERT INTO t2 VALUES
42 (1, 'desk organiser', '2003-10-15'),
43 (2, 'CD player', '1993-11-05'),
44 (3, 'TV set', '1996-03-10'),
45 (4, 'bookcase', '1982-01-10'),
46 (5, 'exercise bike', '2004-05-09'),
47 (6, 'sofa', '1987-06-05'),
48 (7, 'popcorn maker', '2001-11-22'),
49 (8, 'acquarium', '1992-08-04'),
50 (9, 'study desk', '1984-09-16'),
51 (10, 'lava lamp', '1998-12-25');
52 CREATE TABLE t3 SELECT * FROM t1;
53 ALTER TABLE t3 ADD PRIMARY KEY (d);
54 ALTER TABLE t3 ADD KEY (a);
55 ALTER TABLE t3 ADD KEY (a, b);
56 ANALYZE TABLE t3;
57 Table Op Msg_type Msg_text
58 test.t3 analyze status Engine-independent statistics collected
59 test.t3 analyze status OK
60 ########################################################################
61 connect con1,localhost,test,,test;
62 BEGIN;
63 SELECT d,a,b,c FROM t1 partition (p0);
64 d a b c
65 SELECT d,a,b,c FROM t1 partition (p1);
66 d a b c
67 7 03 03 343
68 8 03 06 343
69 9 03 07 343
70 SELECT d,a,b,c FROM t1 partition (p2);
71 d a b c
72 SELECT d,a,b,c FROM t1 partition (p3);
73 d a b c
74 SELECT d,a,b,c FROM t1 partition (p4);
75 d a b c
76 SELECT d,a,b,c FROM t1 partition (p5);
77 d a b c
78 SELECT d,a,b,c FROM t1 partition (p6);
79 d a b c
80 SELECT d,a,b,c FROM t1 partition (p7);
81 d a b c
82 SELECT d,a,b,c FROM t1 partition (p8);
83 d a b c
84 SELECT d,a,b,c FROM t1 partition (p9);
85 d a b c
86 2 01 04 343
87 3 01 06 343
88 4 01 07 343
89 5 01 08 343
90 6 01 09 343
91 10 04 03 343
92 11 04 06 343
93 12 05 03 343
94 SELECT d,a,b,c FROM t1 partition (p10);
95 d a b c
96 SELECT d,a,b,c FROM t1 partition (p11);
97 d a b c
98 13 11 03 343
99 14 11 04 343
100 SELECT d,a,b,c FROM t1 partition (p12);
101 d a b c
102 SELECT d,a,b,c FROM t1 partition (p13);
103 d a b c
104 SELECT d,a,b,c FROM t1 partition (p14);
105 d a b c
106 SELECT d,a,b,c FROM t1 partition (p15);
107 d a b c
108 SELECT d,a,b,c FROM t1 partition (p16);
109 d a b c
110 SELECT d,a,b,c FROM t1 partition (p17);
111 d a b c
112 1 07 03 343
113 SELECT d,a,b,c FROM t1 partition (p18);
114 d a b c
115 SELECT d,a,b,c FROM t1 partition (p19);
116 d a b c
117 SELECT * FROM t1 WHERE a='01' FOR UPDATE ;
118 a b c d e
119 01 04 343 2 01_04_343
120 01 06 343 3 01_06_343
121 01 07 343 4 01_07_343
122 01 08 343 5 01_08_343
123 01 09 343 6 01_09_343
124 SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' FOR UPDATE ;
125 a b c d e
126 01 04 343 2 01_04_343
127 01 06 343 3 01_06_343
128 01 07 343 4 01_07_343
129 01 08 343 5 01_08_343
130 01 09 343 6 01_09_343
131 connect con2,localhost,test,,test;
132 BEGIN;
133 SET SESSION innodb_lock_wait_timeout=1;
134 #
135 # SHARE ...
136 SELECT * FROM t1 LOCK IN SHARE MODE;
137 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
138 SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT;
139 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
140 SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
141 a b c d e
142 07 03 343 1 07_03_343
143 04 03 343 10 04_03_343
144 04 06 343 11 04_06_343
145 05 03 343 12 05_03_343
146 11 03 343 13 11_03_343
147 11 04 343 14 11_04_343
148 03 03 343 7 03_03_343
149 03 06 343 8 03_06_343
150 03 07 343 9 03_07_343
151 SELECT a,count(b) FROM t1 force index (a) GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
152 a count(b)
153 01 5
154 03 3
155 04 2
156 05 1
157 07 1
158 11 2
159 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
160 LOCK IN SHARE MODE SKIP LOCKED;
161 d a b c
162 1 07 03 343
163 10 04 03 343
164 11 04 06 343
165 12 05 03 343
166 13 11 03 343
167 14 11 04 343
168 7 03 03 343
169 8 03 06 343
170 9 03 07 343
171 SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
172 d a b c
173 1 07 03 343
174 10 04 03 343
175 11 04 06 343
176 12 05 03 343
177 13 11 03 343
178 14 11 04 343
179 7 03 03 343
180 8 03 06 343
181 9 03 07 343
182 ANALYZE TABLE t3;
183 Table Op Msg_type Msg_text
184 test.t3 analyze status Engine-independent statistics collected
185 test.t3 analyze status OK
186 SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
187 d a b c
188 1 07 03 343
189 10 04 03 343
190 11 04 06 343
191 12 05 03 343
192 13 11 03 343
193 14 11 04 343
194 7 03 03 343
195 8 03 06 343
196 9 03 07 343
197 explain SELECT d,a,b,c FROM t3 ORDER BY d
198 LOCK IN SHARE MODE SKIP LOCKED;
199 id select_type table type possible_keys key key_len ref rows Extra
200 1 SIMPLE t3 index NULL PRIMARY 257 NULL 14
201 #
202 # UPDATE ...
203 SELECT * FROM t1 FOR UPDATE;
204 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
205 SELECT * FROM t1 FOR UPDATE NOWAIT;
206 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
207 SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED;
208 a b c d e
209 03 03 343 7 03_03_343
210 03 06 343 8 03_06_343
211 03 07 343 9 03_07_343
212 04 03 343 10 04_03_343
213 04 06 343 11 04_06_343
214 05 03 343 12 05_03_343
215 07 03 343 1 07_03_343
216 11 03 343 13 11_03_343
217 11 04 343 14 11_04_343
218 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED;
219 a count(b)
220 03 3
221 04 2
222 05 1
223 07 1
224 11 2
225 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
226 FOR UPDATE SKIP LOCKED;
227 d a b c
228 1 07 03 343
229 10 04 03 343
230 11 04 06 343
231 12 05 03 343
232 13 11 03 343
233 14 11 04 343
234 7 03 03 343
235 8 03 06 343
236 9 03 07 343
237 SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED;
238 d a b c
239 1 07 03 343
240 10 04 03 343
241 11 04 06 343
242 12 05 03 343
243 13 11 03 343
244 14 11 04 343
245 7 03 03 343
246 8 03 06 343
247 9 03 07 343
248 SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED;
249 d a b c
250 1 07 03 343
251 10 04 03 343
252 11 04 06 343
253 12 05 03 343
254 13 11 03 343
255 14 11 04 343
256 7 03 03 343
257 8 03 06 343
258 9 03 07 343
259 connection con1;
260 COMMIT;
261 connection con1;
262 BEGIN;
263 SELECT * FROM t1 WHERE a='01' LOCK IN SHARE MODE ;
264 a b c d e
265 01 04 343 2 01_04_343
266 01 06 343 3 01_06_343
267 01 07 343 4 01_07_343
268 01 08 343 5 01_08_343
269 01 09 343 6 01_09_343
270 SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' LOCK IN SHARE MODE ;
271 a b c d e
272 01 04 343 2 01_04_343
273 01 06 343 3 01_06_343
274 01 07 343 4 01_07_343
275 01 08 343 5 01_08_343
276 01 09 343 6 01_09_343
277 connection con2;
278 BEGIN;
279 SET SESSION innodb_lock_wait_timeout=1;
280 #
281 # SHARE ...
282 SELECT * FROM t1 LOCK IN SHARE MODE;
283 a b c d e
284 03 03 343 7 03_03_343
285 03 06 343 8 03_06_343
286 03 07 343 9 03_07_343
287 01 04 343 2 01_04_343
288 01 06 343 3 01_06_343
289 01 07 343 4 01_07_343
290 01 08 343 5 01_08_343
291 01 09 343 6 01_09_343
292 04 03 343 10 04_03_343
293 04 06 343 11 04_06_343
294 05 03 343 12 05_03_343
295 11 03 343 13 11_03_343
296 11 04 343 14 11_04_343
297 07 03 343 1 07_03_343
298 SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT;
299 a b c d e
300 03 03 343 7 03_03_343
301 03 06 343 8 03_06_343
302 03 07 343 9 03_07_343
303 01 04 343 2 01_04_343
304 01 06 343 3 01_06_343
305 01 07 343 4 01_07_343
306 01 08 343 5 01_08_343
307 01 09 343 6 01_09_343
308 04 03 343 10 04_03_343
309 04 06 343 11 04_06_343
310 05 03 343 12 05_03_343
311 11 03 343 13 11_03_343
312 11 04 343 14 11_04_343
313 07 03 343 1 07_03_343
314 SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
315 a b c d e
316 07 03 343 1 07_03_343
317 04 03 343 10 04_03_343
318 04 06 343 11 04_06_343
319 05 03 343 12 05_03_343
320 11 03 343 13 11_03_343
321 11 04 343 14 11_04_343
322 01 04 343 2 01_04_343
323 01 06 343 3 01_06_343
324 01 07 343 4 01_07_343
325 01 08 343 5 01_08_343
326 01 09 343 6 01_09_343
327 03 03 343 7 03_03_343
328 03 06 343 8 03_06_343
329 03 07 343 9 03_07_343
330 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED;
331 a count(b)
332 01 5
333 03 3
334 04 2
335 05 1
336 07 1
337 11 2
338 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
339 LOCK IN SHARE MODE SKIP LOCKED;
340 d a b c
341 1 07 03 343
342 10 04 03 343
343 11 04 06 343
344 12 05 03 343
345 13 11 03 343
346 14 11 04 343
347 2 01 04 343
348 3 01 06 343
349 4 01 07 343
350 5 01 08 343
351 6 01 09 343
352 7 03 03 343
353 8 03 06 343
354 9 03 07 343
355 SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
356 d a b c
357 1 07 03 343
358 10 04 03 343
359 11 04 06 343
360 12 05 03 343
361 13 11 03 343
362 14 11 04 343
363 2 01 04 343
364 3 01 06 343
365 4 01 07 343
366 5 01 08 343
367 6 01 09 343
368 7 03 03 343
369 8 03 06 343
370 9 03 07 343
371 ANALYZE TABLE t3;
372 Table Op Msg_type Msg_text
373 test.t3 analyze status Engine-independent statistics collected
374 test.t3 analyze status OK
375 SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED;
376 d a b c
377 1 07 03 343
378 10 04 03 343
379 11 04 06 343
380 12 05 03 343
381 13 11 03 343
382 14 11 04 343
383 2 01 04 343
384 3 01 06 343
385 4 01 07 343
386 5 01 08 343
387 6 01 09 343
388 7 03 03 343
389 8 03 06 343
390 9 03 07 343
391 explain SELECT d,a,b,c FROM t3 ORDER BY d
392 LOCK IN SHARE MODE SKIP LOCKED;
393 id select_type table type possible_keys key key_len ref rows Extra
394 1 SIMPLE t3 index NULL PRIMARY 257 NULL 14
395 #
396 # UPDATE ...
397 SELECT * FROM t1 FOR UPDATE;
398 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
399 SELECT * FROM t1 FOR UPDATE NOWAIT;
400 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
401 SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED;
402 a b c d e
403 03 03 343 7 03_03_343
404 03 06 343 8 03_06_343
405 03 07 343 9 03_07_343
406 04 03 343 10 04_03_343
407 04 06 343 11 04_06_343
408 05 03 343 12 05_03_343
409 07 03 343 1 07_03_343
410 11 03 343 13 11_03_343
411 11 04 343 14 11_04_343
412 SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED;
413 a count(b)
414 03 3
415 04 2
416 05 1
417 07 1
418 11 2
419 SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d
420 FOR UPDATE SKIP LOCKED;
421 d a b c
422 1 07 03 343
423 10 04 03 343
424 11 04 06 343
425 12 05 03 343
426 13 11 03 343
427 14 11 04 343
428 7 03 03 343
429 8 03 06 343
430 9 03 07 343
431 SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED;
432 d a b c
433 1 07 03 343
434 10 04 03 343
435 11 04 06 343
436 12 05 03 343
437 13 11 03 343
438 14 11 04 343
439 7 03 03 343
440 8 03 06 343
441 9 03 07 343
442 SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED;
443 d a b c
444 1 07 03 343
445 10 04 03 343
446 11 04 06 343
447 12 05 03 343
448 13 11 03 343
449 14 11 04 343
450 7 03 03 343
451 8 03 06 343
452 9 03 07 343
453 connection default;
454 disconnect con1;
455 disconnect con2;
456 DROP TABLE t1, t2, t3;
457 DROP USER test@localhost;
458 DROP USER test2@localhost;
459 set @@global.read_only= @start_read_only;
460 set @@global.autocommit= @start_autocommit;
461 set default_storage_engine= default;