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