Debian Codegraph @

mysql-test/suite/innodb/t/lock_contention.test

raw
1 #
2 # WL#6326: InnoDB: fix index->lock contention
3 #
4
5 --source include/have_debug.inc
6 --source include/have_debug_sync.inc
7
8 --disable_query_log
9 SET @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
10 SET @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index;
11 SET @old_innodb_stats_persistent = @@innodb_stats_persistent;
12 --enable_query_log
13 --disable_warnings
14 DROP TABLE IF EXISTS t1;
15 --enable_warnings
16
17 # Save the initial number of concurrent sessions
18 --source include/count_sessions.inc
19
20 SET GLOBAL innodb_adaptive_hash_index = false;
21 SET GLOBAL innodb_stats_persistent = false;
22
23 --connect (con1,localhost,root,,)
24 --connect (con2,localhost,root,,)
25 --connect (con3,localhost,root,,)
26
27 CREATE TABLE t1 (
28 a00 CHAR(255) NOT NULL DEFAULT 'a',
29 a01 CHAR(255) NOT NULL DEFAULT 'a',
30 a02 CHAR(255) NOT NULL DEFAULT 'a',
31 a03 CHAR(255) NOT NULL DEFAULT 'a',
32 a04 CHAR(255) NOT NULL DEFAULT 'a',
33 a05 CHAR(255) NOT NULL DEFAULT 'a',
34 a06 CHAR(255) NOT NULL DEFAULT 'a',
35 b INT NOT NULL DEFAULT 0
36 ) charset latin1 ENGINE = InnoDB;
37
38 ALTER TABLE t1 ADD CONSTRAINT pkey PRIMARY KEY(
39 a00,
40 a01,
41 a02,
42 a03,
43 a04,
44 a05,
45 a06
46 );
47
48 #
49 # Prepare primary key index tree to be used for this test.
50 #
51
52 # Only root (1)
53 ANALYZE TABLE t1;
54 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
55
56 # Make the first records sparse artificially,
57 # not to cause modify_tree by single node_ptr insert operation.
58 # * (7 - 2) records should be larger than a half of the page size
59 # * (7 + 2) records should be fit to the page
60 # (above t1 definition is already adjusted)
61 SET GLOBAL innodb_limit_optimistic_insert_debug = 7;
62
63 INSERT INTO t1 (a00) VALUES ('aa');
64 INSERT INTO t1 (a00) VALUES ('ab');
65 INSERT INTO t1 (a00) VALUES ('ac');
66 INSERT INTO t1 (a00) VALUES ('ad');
67 INSERT INTO t1 (a00) VALUES ('ae');
68 INSERT INTO t1 (a00) VALUES ('af');
69 INSERT INTO t1 (a00) VALUES ('ag');
70 INSERT INTO t1 (a00) VALUES ('ah');
71 # Raise root (1-2)
72 # (aa,ad)
73 # (aa,ab,ac)(ad,ae,af,ag,ah)
74 ANALYZE TABLE t1;
75 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
76
77 INSERT INTO t1 (a00) VALUES ('ai');
78 INSERT INTO t1 (a00) VALUES ('aj');
79 INSERT INTO t1 (a00) VALUES ('ak');
80 # Split leaf (1-3)
81 # (aa,ad,ak)
82 # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak)
83 ANALYZE TABLE t1;
84 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
85
86 INSERT INTO t1 (a00) VALUES ('al');
87 INSERT INTO t1 (a00) VALUES ('am');
88 INSERT INTO t1 (a00) VALUES ('an');
89 INSERT INTO t1 (a00) VALUES ('ao');
90 INSERT INTO t1 (a00) VALUES ('ap');
91 INSERT INTO t1 (a00) VALUES ('aq');
92 INSERT INTO t1 (a00) VALUES ('ar');
93 # Split leaf (1-4)
94 # (aa,ad,ak,ar)
95 # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar)
96 ANALYZE TABLE t1;
97 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
98
99 INSERT INTO t1 (a00) VALUES ('as');
100 INSERT INTO t1 (a00) VALUES ('at');
101 INSERT INTO t1 (a00) VALUES ('au');
102 INSERT INTO t1 (a00) VALUES ('av');
103 INSERT INTO t1 (a00) VALUES ('aw');
104 INSERT INTO t1 (a00) VALUES ('ax');
105 INSERT INTO t1 (a00) VALUES ('ay');
106 # Split leaf (1-5)
107 # (aa,ad,ak,ar,ay)
108 # (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar,as,at,au,av,aw,ax)(ay)
109 ANALYZE TABLE t1;
110 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
111
112 INSERT INTO t1 (a00) VALUES ('az');
113 INSERT INTO t1 (a00) VALUES ('ba');
114 INSERT INTO t1 (a00) VALUES ('bb');
115 INSERT INTO t1 (a00) VALUES ('bc');
116 INSERT INTO t1 (a00) VALUES ('bd');
117 INSERT INTO t1 (a00) VALUES ('be');
118 INSERT INTO t1 (a00) VALUES ('bf');
119 # Split leaf (1-6)
120 # (aa,ad,ak,ar,ay,bf)
121 # (aa,ab,ac)(ad..)(ak..)(ar,as,at,au,av,aw,ax)(ay,az,ba,bb,bc,bd,be)(bf)
122 ANALYZE TABLE t1;
123 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
124
125
126 INSERT INTO t1 (a00) VALUES ('bg');
127 INSERT INTO t1 (a00) VALUES ('bh');
128 INSERT INTO t1 (a00) VALUES ('bi');
129 INSERT INTO t1 (a00) VALUES ('bj');
130 INSERT INTO t1 (a00) VALUES ('bk');
131 INSERT INTO t1 (a00) VALUES ('bl');
132 INSERT INTO t1 (a00) VALUES ('bm');
133 # Split leaf (1-7)
134 # (aa,ad,ak,ar,ay,bf,bm)
135 # (aa,ab,ac)(ad..)(ak..)(ar..)(ay,az,ba,bb,bc,bd,be)(bf,bg,bh,bi,bj,bk,bl)(bm)
136 ANALYZE TABLE t1;
137 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
138
139 INSERT INTO t1 (a00) VALUES ('bn');
140 INSERT INTO t1 (a00) VALUES ('bo');
141 INSERT INTO t1 (a00) VALUES ('bp');
142 INSERT INTO t1 (a00) VALUES ('bq');
143 INSERT INTO t1 (a00) VALUES ('br');
144 INSERT INTO t1 (a00) VALUES ('bs');
145 INSERT INTO t1 (a00) VALUES ('bt');
146 # Raise root (1-2-8)
147 # (aa,ar)
148 # (aa,ad,ak) (ar,ay,bf,bm,bt)
149 # (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt)
150 ANALYZE TABLE t1;
151 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
152
153
154 INSERT INTO t1 (a00) VALUES ('bu');
155 INSERT INTO t1 (a00) VALUES ('bv');
156 INSERT INTO t1 (a00) VALUES ('bw');
157 INSERT INTO t1 (a00) VALUES ('bx');
158 INSERT INTO t1 (a00) VALUES ('by');
159 INSERT INTO t1 (a00) VALUES ('bz');
160 INSERT INTO t1 (a00) VALUES ('ca');
161
162 INSERT INTO t1 (a00) VALUES ('cb');
163 INSERT INTO t1 (a00) VALUES ('cc');
164 INSERT INTO t1 (a00) VALUES ('cd');
165 INSERT INTO t1 (a00) VALUES ('ce');
166 INSERT INTO t1 (a00) VALUES ('cf');
167 INSERT INTO t1 (a00) VALUES ('cg');
168 INSERT INTO t1 (a00) VALUES ('ch');
169
170 INSERT INTO t1 (a00) VALUES ('ci');
171 INSERT INTO t1 (a00) VALUES ('cj');
172 INSERT INTO t1 (a00) VALUES ('ck');
173 INSERT INTO t1 (a00) VALUES ('cl');
174 INSERT INTO t1 (a00) VALUES ('cm');
175 INSERT INTO t1 (a00) VALUES ('cn');
176 INSERT INTO t1 (a00) VALUES ('co');
177 # Split also at level 1 (1-3-11)
178 # (aa,ar,co)
179 # (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co)
180 # (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co)
181 ANALYZE TABLE t1;
182 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
183
184
185 INSERT INTO t1 (a00) VALUES ('cp');
186 INSERT INTO t1 (a00) VALUES ('cq');
187 INSERT INTO t1 (a00) VALUES ('cr');
188 INSERT INTO t1 (a00) VALUES ('cs');
189 INSERT INTO t1 (a00) VALUES ('ct');
190 INSERT INTO t1 (a00) VALUES ('cu');
191 INSERT INTO t1 (a00) VALUES ('cv');
192
193 INSERT INTO t1 (a00) VALUES ('cw');
194 INSERT INTO t1 (a00) VALUES ('cx');
195 INSERT INTO t1 (a00) VALUES ('cy');
196 INSERT INTO t1 (a00) VALUES ('cz');
197 INSERT INTO t1 (a00) VALUES ('da');
198 INSERT INTO t1 (a00) VALUES ('db');
199 INSERT INTO t1 (a00) VALUES ('dc');
200
201 INSERT INTO t1 (a00) VALUES ('dd');
202 INSERT INTO t1 (a00) VALUES ('de');
203 INSERT INTO t1 (a00) VALUES ('df');
204 INSERT INTO t1 (a00) VALUES ('dg');
205 INSERT INTO t1 (a00) VALUES ('dh');
206 INSERT INTO t1 (a00) VALUES ('di');
207 INSERT INTO t1 (a00) VALUES ('dj');
208
209 INSERT INTO t1 (a00) VALUES ('dk');
210 INSERT INTO t1 (a00) VALUES ('dl');
211 INSERT INTO t1 (a00) VALUES ('dm');
212 INSERT INTO t1 (a00) VALUES ('dn');
213 INSERT INTO t1 (a00) VALUES ('do');
214 INSERT INTO t1 (a00) VALUES ('dp');
215 INSERT INTO t1 (a00) VALUES ('dq');
216
217 INSERT INTO t1 (a00) VALUES ('dr');
218 INSERT INTO t1 (a00) VALUES ('ds');
219 INSERT INTO t1 (a00) VALUES ('dt');
220 INSERT INTO t1 (a00) VALUES ('du');
221 INSERT INTO t1 (a00) VALUES ('dv');
222 INSERT INTO t1 (a00) VALUES ('dw');
223 INSERT INTO t1 (a00) VALUES ('dx');
224
225 INSERT INTO t1 (a00) VALUES ('dy');
226 INSERT INTO t1 (a00) VALUES ('dz');
227 INSERT INTO t1 (a00) VALUES ('ea');
228 INSERT INTO t1 (a00) VALUES ('eb');
229 INSERT INTO t1 (a00) VALUES ('ec');
230 INSERT INTO t1 (a00) VALUES ('ed');
231 INSERT INTO t1 (a00) VALUES ('ee');
232
233 INSERT INTO t1 (a00) VALUES ('ef');
234 INSERT INTO t1 (a00) VALUES ('eg');
235 INSERT INTO t1 (a00) VALUES ('eh');
236 INSERT INTO t1 (a00) VALUES ('ei');
237 INSERT INTO t1 (a00) VALUES ('ej');
238 INSERT INTO t1 (a00) VALUES ('ek');
239 INSERT INTO t1 (a00) VALUES ('el');
240 # Split also at level 1 (1-4-18)
241 # (aa,ar,co,el)
242 # (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el)
243 # (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el)
244 ANALYZE TABLE t1;
245 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
246
247 INSERT INTO t1 (a00) VALUES ('em');
248 INSERT INTO t1 (a00) VALUES ('en');
249 INSERT INTO t1 (a00) VALUES ('eo');
250 INSERT INTO t1 (a00) VALUES ('ep');
251 INSERT INTO t1 (a00) VALUES ('eq');
252 INSERT INTO t1 (a00) VALUES ('er');
253 INSERT INTO t1 (a00) VALUES ('es');
254
255 INSERT INTO t1 (a00) VALUES ('et');
256 INSERT INTO t1 (a00) VALUES ('eu');
257 INSERT INTO t1 (a00) VALUES ('ev');
258 INSERT INTO t1 (a00) VALUES ('ew');
259 INSERT INTO t1 (a00) VALUES ('ex');
260 INSERT INTO t1 (a00) VALUES ('ey');
261 INSERT INTO t1 (a00) VALUES ('ez');
262
263 INSERT INTO t1 (a00) VALUES ('fa');
264 INSERT INTO t1 (a00) VALUES ('fb');
265 INSERT INTO t1 (a00) VALUES ('fc');
266 INSERT INTO t1 (a00) VALUES ('fd');
267 INSERT INTO t1 (a00) VALUES ('fe');
268 INSERT INTO t1 (a00) VALUES ('ff');
269 INSERT INTO t1 (a00) VALUES ('fg');
270
271 INSERT INTO t1 (a00) VALUES ('fh');
272 INSERT INTO t1 (a00) VALUES ('fi');
273 INSERT INTO t1 (a00) VALUES ('fj');
274 INSERT INTO t1 (a00) VALUES ('fk');
275 INSERT INTO t1 (a00) VALUES ('fl');
276 INSERT INTO t1 (a00) VALUES ('fm');
277 INSERT INTO t1 (a00) VALUES ('fn');
278
279 INSERT INTO t1 (a00) VALUES ('fo');
280 INSERT INTO t1 (a00) VALUES ('fp');
281 INSERT INTO t1 (a00) VALUES ('fq');
282 INSERT INTO t1 (a00) VALUES ('fr');
283 INSERT INTO t1 (a00) VALUES ('fs');
284 INSERT INTO t1 (a00) VALUES ('ft');
285 INSERT INTO t1 (a00) VALUES ('fu');
286
287 INSERT INTO t1 (a00) VALUES ('fv');
288 INSERT INTO t1 (a00) VALUES ('fw');
289 INSERT INTO t1 (a00) VALUES ('fx');
290 INSERT INTO t1 (a00) VALUES ('fy');
291 INSERT INTO t1 (a00) VALUES ('fz');
292 INSERT INTO t1 (a00) VALUES ('ga');
293 INSERT INTO t1 (a00) VALUES ('gb');
294
295 INSERT INTO t1 (a00) VALUES ('gc');
296 INSERT INTO t1 (a00) VALUES ('gd');
297 INSERT INTO t1 (a00) VALUES ('ge');
298 INSERT INTO t1 (a00) VALUES ('gf');
299 INSERT INTO t1 (a00) VALUES ('gg');
300 INSERT INTO t1 (a00) VALUES ('gh');
301
302
303 # Current tree form (1-4-24)
304 # (aa,ar,co,el)
305 # (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el..,gb)
306 # (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el..)..(gb..)
307 ANALYZE TABLE t1;
308 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
309
310
311
312 # Insert the rest of records normally
313 SET GLOBAL innodb_limit_optimistic_insert_debug = 0;
314
315
316 --echo # Test start
317
318 # (1) Insert records to leaf page (bf..) and cause modify_page.
319 # - root page is not X latched
320 # - latched from level 1 page (ar,ay,bf,bm,bt,ca,ch)
321
322 SET DEBUG_SYNC = 'RESET';
323
324 # Filling leaf page (bf..)
325 INSERT INTO t1 (a00) VALUES ('bfa');
326
327 --connection con1
328 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
329 # Cause modify_tree
330 --send
331 INSERT INTO t1 (a00) VALUES ('bfb');
332
333 --connection con2
334 SET DEBUG_SYNC = 'now WAIT_FOR reached';
335 # Not blocked searches
336 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
337 SELECT a00,a01 FROM t1 WHERE a00 = 'aq';
338 # "where a00 = 'co'" is blocked because searching from smaller ('co','a','a',..).
339 SELECT a00,a01 FROM t1 WHERE a00 = 'cp';
340 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
341
342 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
343 # Blocked
344 --send
345 SELECT a00,a01 FROM t1 WHERE a00 = 'ar';
346
347 --connection con3
348 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
349 # Blocked
350 --send
351 SELECT a00,a01 FROM t1 WHERE a00 = 'cn';
352
353 --connection default
354 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
355 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
356 SET DEBUG_SYNC = 'now SIGNAL continue';
357
358 --connection con1
359 --reap
360
361 --connection con2
362 --reap
363
364 --connection con3
365 --reap
366
367 --connection default
368
369 ANALYZE TABLE t1;
370 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
371
372
373
374 # (2) Insert records to leaf page (co..) and cause modify_page
375 # - root page is X latched, because node_ptr for 'co'
376 # is 1st record for (co,cv,dc,dj,dq,dx,ee)
377 #
378 # * ordinary pessimitic insert might be done by pessistic update
379 # and we should consider possibility node_ptr to be deleted.
380
381 SET DEBUG_SYNC = 'RESET';
382
383 # Filling leaf page (co..)
384 INSERT INTO t1 (a00) VALUES ('coa');
385
386 --connection con1
387 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
388 # Cause modify_tree
389 --send
390 INSERT INTO t1 (a00) VALUES ('cob');
391
392 --connection con2
393 SET DEBUG_SYNC = 'now WAIT_FOR reached';
394 # All searches are blocked because root page is X latched
395
396 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
397 # Blocked
398 --send
399 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
400
401 --connection con3
402 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
403 # Blocked
404 --send
405 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
406
407 --connection default
408 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
409 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
410 SET DEBUG_SYNC = 'now SIGNAL continue';
411
412 --connection con1
413 --reap
414
415 --connection con2
416 --reap
417
418 --connection con3
419 --reap
420
421 --connection default
422
423 ANALYZE TABLE t1;
424 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
425
426
427
428 # (3) Insert records to rightmost leaf page (gb..) and cause modify_page
429 # - root page is not X latched, because node_ptr for 'gb' is the last record
430 # of the level 1 though it is last record in the page.
431 # - lathed from level 1 page (el..,gb)
432
433 SET DEBUG_SYNC = 'RESET';
434
435 # Filling leaf page (gb..)
436 INSERT INTO t1 (a00) VALUES ('gba');
437
438 --connection con1
439 SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';
440 # Cause modify_tree
441 --send
442 INSERT INTO t1 (a00) VALUES ('gbb');
443
444 --connection con2
445 SET DEBUG_SYNC = 'now WAIT_FOR reached';
446 # Not blocked searches
447 SELECT a00,a01 FROM t1 WHERE a00 = 'aa';
448 SELECT a00,a01 FROM t1 WHERE a00 = 'ek';
449
450 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
451 # Blocked
452 --send
453 SELECT a00,a01 FROM t1 WHERE a00 = 'el';
454
455 --connection con3
456 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
457 # Blocked
458 --send
459 SELECT a00,a01 FROM t1 WHERE a00 = 'gb';
460
461 --connection default
462 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
463 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
464 SET DEBUG_SYNC = 'now SIGNAL continue';
465
466 --connection con1
467 --reap
468
469 --connection con2
470 --reap
471
472 --connection con3
473 --reap
474
475 --connection default
476 ANALYZE TABLE t1;
477 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
478
479
480
481 # Cleanup
482 SET DEBUG_SYNC = 'RESET';
483
484 --connection default
485 --disconnect con1
486 --disconnect con2
487 --disconnect con3
488
489 DROP TABLE t1;
490
491 --disable_query_log
492 SET GLOBAL innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
493 SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index;
494 SET GLOBAL innodb_stats_persistent = @old_innodb_stats_persistent;
495 --enable_query_log
496
497 # Wait till all disconnects are completed.
498 --source include/wait_until_count_sessions.inc