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