1DROP TABLE IF EXISTS t1; 2SET GLOBAL innodb_adaptive_hash_index = false; 3SET GLOBAL innodb_stats_persistent = false; 4CREATE TABLE t1 ( 5a00 CHAR(255) NOT NULL DEFAULT 'a', 6a01 CHAR(255) NOT NULL DEFAULT 'a', 7a02 CHAR(255) NOT NULL DEFAULT 'a', 8a03 CHAR(255) NOT NULL DEFAULT 'a', 9a04 CHAR(255) NOT NULL DEFAULT 'a', 10a05 CHAR(255) NOT NULL DEFAULT 'a', 11a06 CHAR(255) NOT NULL DEFAULT 'a', 12b INT NOT NULL DEFAULT 0 13) charset latin1 ENGINE = InnoDB; 14ALTER TABLE t1 ADD CONSTRAINT pkey PRIMARY KEY( 15a00, 16a01, 17a02, 18a03, 19a04, 20a05, 21a06 22); 23ANALYZE TABLE t1; 24Table Op Msg_type Msg_text 25test.t1 analyze status OK 26SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 27CLUST_INDEX_SIZE 281 29SET GLOBAL innodb_limit_optimistic_insert_debug = 7; 30INSERT INTO t1 (a00) VALUES ('aa'); 31INSERT INTO t1 (a00) VALUES ('ab'); 32INSERT INTO t1 (a00) VALUES ('ac'); 33INSERT INTO t1 (a00) VALUES ('ad'); 34INSERT INTO t1 (a00) VALUES ('ae'); 35INSERT INTO t1 (a00) VALUES ('af'); 36INSERT INTO t1 (a00) VALUES ('ag'); 37INSERT INTO t1 (a00) VALUES ('ah'); 38ANALYZE TABLE t1; 39Table Op Msg_type Msg_text 40test.t1 analyze status OK 41SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 42CLUST_INDEX_SIZE 433 44INSERT INTO t1 (a00) VALUES ('ai'); 45INSERT INTO t1 (a00) VALUES ('aj'); 46INSERT INTO t1 (a00) VALUES ('ak'); 47ANALYZE TABLE t1; 48Table Op Msg_type Msg_text 49test.t1 analyze status OK 50SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 51CLUST_INDEX_SIZE 524 53INSERT INTO t1 (a00) VALUES ('al'); 54INSERT INTO t1 (a00) VALUES ('am'); 55INSERT INTO t1 (a00) VALUES ('an'); 56INSERT INTO t1 (a00) VALUES ('ao'); 57INSERT INTO t1 (a00) VALUES ('ap'); 58INSERT INTO t1 (a00) VALUES ('aq'); 59INSERT INTO t1 (a00) VALUES ('ar'); 60ANALYZE TABLE t1; 61Table Op Msg_type Msg_text 62test.t1 analyze status OK 63SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 64CLUST_INDEX_SIZE 655 66INSERT INTO t1 (a00) VALUES ('as'); 67INSERT INTO t1 (a00) VALUES ('at'); 68INSERT INTO t1 (a00) VALUES ('au'); 69INSERT INTO t1 (a00) VALUES ('av'); 70INSERT INTO t1 (a00) VALUES ('aw'); 71INSERT INTO t1 (a00) VALUES ('ax'); 72INSERT INTO t1 (a00) VALUES ('ay'); 73ANALYZE TABLE t1; 74Table Op Msg_type Msg_text 75test.t1 analyze status OK 76SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 77CLUST_INDEX_SIZE 786 79INSERT INTO t1 (a00) VALUES ('az'); 80INSERT INTO t1 (a00) VALUES ('ba'); 81INSERT INTO t1 (a00) VALUES ('bb'); 82INSERT INTO t1 (a00) VALUES ('bc'); 83INSERT INTO t1 (a00) VALUES ('bd'); 84INSERT INTO t1 (a00) VALUES ('be'); 85INSERT INTO t1 (a00) VALUES ('bf'); 86ANALYZE TABLE t1; 87Table Op Msg_type Msg_text 88test.t1 analyze status OK 89SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 90CLUST_INDEX_SIZE 917 92INSERT INTO t1 (a00) VALUES ('bg'); 93INSERT INTO t1 (a00) VALUES ('bh'); 94INSERT INTO t1 (a00) VALUES ('bi'); 95INSERT INTO t1 (a00) VALUES ('bj'); 96INSERT INTO t1 (a00) VALUES ('bk'); 97INSERT INTO t1 (a00) VALUES ('bl'); 98INSERT INTO t1 (a00) VALUES ('bm'); 99ANALYZE TABLE t1; 100Table Op Msg_type Msg_text 101test.t1 analyze status OK 102SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 103CLUST_INDEX_SIZE 1048 105INSERT INTO t1 (a00) VALUES ('bn'); 106INSERT INTO t1 (a00) VALUES ('bo'); 107INSERT INTO t1 (a00) VALUES ('bp'); 108INSERT INTO t1 (a00) VALUES ('bq'); 109INSERT INTO t1 (a00) VALUES ('br'); 110INSERT INTO t1 (a00) VALUES ('bs'); 111INSERT INTO t1 (a00) VALUES ('bt'); 112ANALYZE TABLE t1; 113Table Op Msg_type Msg_text 114test.t1 analyze status OK 115SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 116CLUST_INDEX_SIZE 11711 118INSERT INTO t1 (a00) VALUES ('bu'); 119INSERT INTO t1 (a00) VALUES ('bv'); 120INSERT INTO t1 (a00) VALUES ('bw'); 121INSERT INTO t1 (a00) VALUES ('bx'); 122INSERT INTO t1 (a00) VALUES ('by'); 123INSERT INTO t1 (a00) VALUES ('bz'); 124INSERT INTO t1 (a00) VALUES ('ca'); 125INSERT INTO t1 (a00) VALUES ('cb'); 126INSERT INTO t1 (a00) VALUES ('cc'); 127INSERT INTO t1 (a00) VALUES ('cd'); 128INSERT INTO t1 (a00) VALUES ('ce'); 129INSERT INTO t1 (a00) VALUES ('cf'); 130INSERT INTO t1 (a00) VALUES ('cg'); 131INSERT INTO t1 (a00) VALUES ('ch'); 132INSERT INTO t1 (a00) VALUES ('ci'); 133INSERT INTO t1 (a00) VALUES ('cj'); 134INSERT INTO t1 (a00) VALUES ('ck'); 135INSERT INTO t1 (a00) VALUES ('cl'); 136INSERT INTO t1 (a00) VALUES ('cm'); 137INSERT INTO t1 (a00) VALUES ('cn'); 138INSERT INTO t1 (a00) VALUES ('co'); 139ANALYZE TABLE t1; 140Table Op Msg_type Msg_text 141test.t1 analyze status OK 142SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 143CLUST_INDEX_SIZE 14415 145INSERT INTO t1 (a00) VALUES ('cp'); 146INSERT INTO t1 (a00) VALUES ('cq'); 147INSERT INTO t1 (a00) VALUES ('cr'); 148INSERT INTO t1 (a00) VALUES ('cs'); 149INSERT INTO t1 (a00) VALUES ('ct'); 150INSERT INTO t1 (a00) VALUES ('cu'); 151INSERT INTO t1 (a00) VALUES ('cv'); 152INSERT INTO t1 (a00) VALUES ('cw'); 153INSERT INTO t1 (a00) VALUES ('cx'); 154INSERT INTO t1 (a00) VALUES ('cy'); 155INSERT INTO t1 (a00) VALUES ('cz'); 156INSERT INTO t1 (a00) VALUES ('da'); 157INSERT INTO t1 (a00) VALUES ('db'); 158INSERT INTO t1 (a00) VALUES ('dc'); 159INSERT INTO t1 (a00) VALUES ('dd'); 160INSERT INTO t1 (a00) VALUES ('de'); 161INSERT INTO t1 (a00) VALUES ('df'); 162INSERT INTO t1 (a00) VALUES ('dg'); 163INSERT INTO t1 (a00) VALUES ('dh'); 164INSERT INTO t1 (a00) VALUES ('di'); 165INSERT INTO t1 (a00) VALUES ('dj'); 166INSERT INTO t1 (a00) VALUES ('dk'); 167INSERT INTO t1 (a00) VALUES ('dl'); 168INSERT INTO t1 (a00) VALUES ('dm'); 169INSERT INTO t1 (a00) VALUES ('dn'); 170INSERT INTO t1 (a00) VALUES ('do'); 171INSERT INTO t1 (a00) VALUES ('dp'); 172INSERT INTO t1 (a00) VALUES ('dq'); 173INSERT INTO t1 (a00) VALUES ('dr'); 174INSERT INTO t1 (a00) VALUES ('ds'); 175INSERT INTO t1 (a00) VALUES ('dt'); 176INSERT INTO t1 (a00) VALUES ('du'); 177INSERT INTO t1 (a00) VALUES ('dv'); 178INSERT INTO t1 (a00) VALUES ('dw'); 179INSERT INTO t1 (a00) VALUES ('dx'); 180INSERT INTO t1 (a00) VALUES ('dy'); 181INSERT INTO t1 (a00) VALUES ('dz'); 182INSERT INTO t1 (a00) VALUES ('ea'); 183INSERT INTO t1 (a00) VALUES ('eb'); 184INSERT INTO t1 (a00) VALUES ('ec'); 185INSERT INTO t1 (a00) VALUES ('ed'); 186INSERT INTO t1 (a00) VALUES ('ee'); 187INSERT INTO t1 (a00) VALUES ('ef'); 188INSERT INTO t1 (a00) VALUES ('eg'); 189INSERT INTO t1 (a00) VALUES ('eh'); 190INSERT INTO t1 (a00) VALUES ('ei'); 191INSERT INTO t1 (a00) VALUES ('ej'); 192INSERT INTO t1 (a00) VALUES ('ek'); 193INSERT INTO t1 (a00) VALUES ('el'); 194ANALYZE TABLE t1; 195Table Op Msg_type Msg_text 196test.t1 analyze status OK 197SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 198CLUST_INDEX_SIZE 19923 200INSERT INTO t1 (a00) VALUES ('em'); 201INSERT INTO t1 (a00) VALUES ('en'); 202INSERT INTO t1 (a00) VALUES ('eo'); 203INSERT INTO t1 (a00) VALUES ('ep'); 204INSERT INTO t1 (a00) VALUES ('eq'); 205INSERT INTO t1 (a00) VALUES ('er'); 206INSERT INTO t1 (a00) VALUES ('es'); 207INSERT INTO t1 (a00) VALUES ('et'); 208INSERT INTO t1 (a00) VALUES ('eu'); 209INSERT INTO t1 (a00) VALUES ('ev'); 210INSERT INTO t1 (a00) VALUES ('ew'); 211INSERT INTO t1 (a00) VALUES ('ex'); 212INSERT INTO t1 (a00) VALUES ('ey'); 213INSERT INTO t1 (a00) VALUES ('ez'); 214INSERT INTO t1 (a00) VALUES ('fa'); 215INSERT INTO t1 (a00) VALUES ('fb'); 216INSERT INTO t1 (a00) VALUES ('fc'); 217INSERT INTO t1 (a00) VALUES ('fd'); 218INSERT INTO t1 (a00) VALUES ('fe'); 219INSERT INTO t1 (a00) VALUES ('ff'); 220INSERT INTO t1 (a00) VALUES ('fg'); 221INSERT INTO t1 (a00) VALUES ('fh'); 222INSERT INTO t1 (a00) VALUES ('fi'); 223INSERT INTO t1 (a00) VALUES ('fj'); 224INSERT INTO t1 (a00) VALUES ('fk'); 225INSERT INTO t1 (a00) VALUES ('fl'); 226INSERT INTO t1 (a00) VALUES ('fm'); 227INSERT INTO t1 (a00) VALUES ('fn'); 228INSERT INTO t1 (a00) VALUES ('fo'); 229INSERT INTO t1 (a00) VALUES ('fp'); 230INSERT INTO t1 (a00) VALUES ('fq'); 231INSERT INTO t1 (a00) VALUES ('fr'); 232INSERT INTO t1 (a00) VALUES ('fs'); 233INSERT INTO t1 (a00) VALUES ('ft'); 234INSERT INTO t1 (a00) VALUES ('fu'); 235INSERT INTO t1 (a00) VALUES ('fv'); 236INSERT INTO t1 (a00) VALUES ('fw'); 237INSERT INTO t1 (a00) VALUES ('fx'); 238INSERT INTO t1 (a00) VALUES ('fy'); 239INSERT INTO t1 (a00) VALUES ('fz'); 240INSERT INTO t1 (a00) VALUES ('ga'); 241INSERT INTO t1 (a00) VALUES ('gb'); 242INSERT INTO t1 (a00) VALUES ('gc'); 243INSERT INTO t1 (a00) VALUES ('gd'); 244INSERT INTO t1 (a00) VALUES ('ge'); 245INSERT INTO t1 (a00) VALUES ('gf'); 246INSERT INTO t1 (a00) VALUES ('gg'); 247INSERT INTO t1 (a00) VALUES ('gh'); 248ANALYZE TABLE t1; 249Table Op Msg_type Msg_text 250test.t1 analyze status OK 251SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 252CLUST_INDEX_SIZE 25329 254SET GLOBAL innodb_limit_optimistic_insert_debug = 0; 255# Test start 256SET DEBUG_SYNC = 'RESET'; 257INSERT INTO t1 (a00) VALUES ('bfa'); 258SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; 259INSERT INTO t1 (a00) VALUES ('bfb'); 260SET DEBUG_SYNC = 'now WAIT_FOR reached'; 261SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; 262a00 a01 263aa a 264SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; 265a00 a01 266aq a 267SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; 268a00 a01 269cp a 270SELECT a00,a01 FROM t1 WHERE a00 = 'el'; 271a00 a01 272el a 273SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; 274SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; 275SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; 276SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; 277SET DEBUG_SYNC = 'now WAIT_FOR lockwait1'; 278SET DEBUG_SYNC = 'now WAIT_FOR lockwait2'; 279SET DEBUG_SYNC = 'now SIGNAL continue'; 280a00 a01 281ar a 282a00 a01 283cn a 284ANALYZE TABLE t1; 285Table Op Msg_type Msg_text 286test.t1 analyze status OK 287SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 288CLUST_INDEX_SIZE 28930 290SET DEBUG_SYNC = 'RESET'; 291INSERT INTO t1 (a00) VALUES ('coa'); 292SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; 293INSERT INTO t1 (a00) VALUES ('cob'); 294SET DEBUG_SYNC = 'now WAIT_FOR reached'; 295SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; 296SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; 297SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; 298SELECT a00,a01 FROM t1 WHERE a00 = 'el'; 299SET DEBUG_SYNC = 'now WAIT_FOR lockwait1'; 300SET DEBUG_SYNC = 'now WAIT_FOR lockwait2'; 301SET DEBUG_SYNC = 'now SIGNAL continue'; 302a00 a01 303aa a 304a00 a01 305el a 306ANALYZE TABLE t1; 307Table Op Msg_type Msg_text 308test.t1 analyze status OK 309SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 310CLUST_INDEX_SIZE 31131 312SET DEBUG_SYNC = 'RESET'; 313INSERT INTO t1 (a00) VALUES ('gba'); 314SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; 315INSERT INTO t1 (a00) VALUES ('gbb'); 316SET DEBUG_SYNC = 'now WAIT_FOR reached'; 317SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; 318a00 a01 319aa a 320SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; 321a00 a01 322ek a 323SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; 324SELECT a00,a01 FROM t1 WHERE a00 = 'el'; 325SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; 326SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; 327SET DEBUG_SYNC = 'now WAIT_FOR lockwait1'; 328SET DEBUG_SYNC = 'now WAIT_FOR lockwait2'; 329SET DEBUG_SYNC = 'now SIGNAL continue'; 330a00 a01 331el a 332a00 a01 333gb a 334ANALYZE TABLE t1; 335Table Op Msg_type Msg_text 336test.t1 analyze status OK 337SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1'; 338CLUST_INDEX_SIZE 33932 340SET DEBUG_SYNC = 'RESET'; 341DROP TABLE t1; 342