1CREATE TABLE gap1 (id1 INT, id2 INT, id3 INT, c1 INT, value INT, 2PRIMARY KEY (id1, id2, id3), 3INDEX i (c1)) ENGINE=tokudb; 4CREATE TABLE gap2 like gap1; 5CREATE TABLE gap3 (id INT, value INT, 6PRIMARY KEY (id), 7UNIQUE KEY ui(value)) ENGINE=tokudb; 8CREATE TABLE gap4 (id INT, value INT, 9PRIMARY KEY (id)) ENGINE=tokudb 10PARTITION BY HASH(id) PARTITIONS 2; 11insert into gap3 values (1,1), (2,2),(3,3),(4,4),(5,5); 12insert into gap4 values (1,1), (2,2),(3,3),(4,4),(5,5); 13set session autocommit=0; 14select * from gap1 limit 1 for update; 15id1 id2 id3 c1 value 160 0 1 1 1 17select * from gap1 where value != 100 limit 1 for update; 18id1 id2 id3 c1 value 190 0 1 1 1 20select * from gap1 where id1=1 for update; 21id1 id2 id3 c1 value 221 0 2 2 2 231 0 3 3 3 24select * from gap1 where id1=1 and id2= 1 for update; 25id1 id2 id3 c1 value 26select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update; 27id1 id2 id3 c1 value 28select * from gap1 where id1=1 and id2= 1 and id3 29between 1 and 3 for update; 30id1 id2 id3 c1 value 31select * from gap1 where id1=1 and id2= 1 order by id3 asc 32limit 1 for update; 33id1 id2 id3 c1 value 34select * from gap1 where id1=1 and id2= 1 order by id3 desc 35limit 1 for update; 36id1 id2 id3 c1 value 37select * from gap1 order by id1 asc limit 1 for update; 38id1 id2 id3 c1 value 390 0 1 1 1 40select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update; 41id1 id2 id3 c1 value 420 0 1 1 1 43select * from gap1 order by id1 desc limit 1 for update; 44id1 id2 id3 c1 value 45500 100 1000 1000 1000 46select * from gap1 order by id1 desc, id2 desc, id3 desc 47limit 1 for update; 48id1 id2 id3 c1 value 49500 100 1000 1000 1000 50select * from gap1 force index(i) where c1=1 for update; 51id1 id2 id3 c1 value 520 0 1 1 1 53select * from gap3 force index(ui) where value=1 for update; 54id value 551 1 56select * from gap1 where id1=1 and id2=1 and id3=1 for update; 57id1 id2 id3 c1 value 58select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update; 59id1 id2 id3 c1 value 60select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 61order by c1 for update; 62id1 id2 id3 c1 value 63select * from gap3 where id=1 for update; 64id value 651 1 66select * from gap4 where id=1 for update; 67id value 681 1 69select * from gap4 where id in (1, 2, 3) for update; 70id value 711 1 722 2 733 3 74select * from gap4 for update; 75id value 762 2 774 4 781 1 793 3 805 5 81select * from gap4 where id between 3 and 7 for update; 82id value 833 3 844 4 855 5 86set session autocommit=1; 87select * from gap1 limit 1 for update; 88id1 id2 id3 c1 value 890 0 1 1 1 90select * from gap1 where value != 100 limit 1 for update; 91id1 id2 id3 c1 value 920 0 1 1 1 93select * from gap1 where id1=1 for update; 94id1 id2 id3 c1 value 951 0 2 2 2 961 0 3 3 3 97select * from gap1 where id1=1 and id2= 1 for update; 98id1 id2 id3 c1 value 99select * from gap1 where id1=1 and id2= 1 and id3 != 1 for update; 100id1 id2 id3 c1 value 101select * from gap1 where id1=1 and id2= 1 and id3 102between 1 and 3 for update; 103id1 id2 id3 c1 value 104select * from gap1 where id1=1 and id2= 1 order by id3 asc 105limit 1 for update; 106id1 id2 id3 c1 value 107select * from gap1 where id1=1 and id2= 1 order by id3 desc 108limit 1 for update; 109id1 id2 id3 c1 value 110select * from gap1 order by id1 asc limit 1 for update; 111id1 id2 id3 c1 value 1120 0 1 1 1 113select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 for update; 114id1 id2 id3 c1 value 1150 0 1 1 1 116select * from gap1 order by id1 desc limit 1 for update; 117id1 id2 id3 c1 value 118500 100 1000 1000 1000 119select * from gap1 order by id1 desc, id2 desc, id3 desc 120limit 1 for update; 121id1 id2 id3 c1 value 122500 100 1000 1000 1000 123select * from gap1 force index(i) where c1=1 for update; 124id1 id2 id3 c1 value 1250 0 1 1 1 126select * from gap3 force index(ui) where value=1 for update; 127id value 1281 1 129select * from gap1 where id1=1 and id2=1 and id3=1 for update; 130id1 id2 id3 c1 value 131select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) for update; 132id1 id2 id3 c1 value 133select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 134order by c1 for update; 135id1 id2 id3 c1 value 136select * from gap3 where id=1 for update; 137id value 1381 1 139select * from gap4 where id=1 for update; 140id value 1411 1 142select * from gap4 where id in (1, 2, 3) for update; 143id value 1441 1 1452 2 1463 3 147select * from gap4 for update; 148id value 1492 2 1504 4 1511 1 1523 3 1535 5 154select * from gap4 where id between 3 and 7 for update; 155id value 1563 3 1574 4 1585 5 159set session autocommit=0; 160select * from gap1 limit 1 lock in share mode; 161id1 id2 id3 c1 value 1620 0 1 1 1 163select * from gap1 where value != 100 limit 1 lock in share mode; 164id1 id2 id3 c1 value 1650 0 1 1 1 166select * from gap1 where id1=1 lock in share mode; 167id1 id2 id3 c1 value 1681 0 2 2 2 1691 0 3 3 3 170select * from gap1 where id1=1 and id2= 1 lock in share mode; 171id1 id2 id3 c1 value 172select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode; 173id1 id2 id3 c1 value 174select * from gap1 where id1=1 and id2= 1 and id3 175between 1 and 3 lock in share mode; 176id1 id2 id3 c1 value 177select * from gap1 where id1=1 and id2= 1 order by id3 asc 178limit 1 lock in share mode; 179id1 id2 id3 c1 value 180select * from gap1 where id1=1 and id2= 1 order by id3 desc 181limit 1 lock in share mode; 182id1 id2 id3 c1 value 183select * from gap1 order by id1 asc limit 1 lock in share mode; 184id1 id2 id3 c1 value 1850 0 1 1 1 186select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode; 187id1 id2 id3 c1 value 1880 0 1 1 1 189select * from gap1 order by id1 desc limit 1 lock in share mode; 190id1 id2 id3 c1 value 191500 100 1000 1000 1000 192select * from gap1 order by id1 desc, id2 desc, id3 desc 193limit 1 lock in share mode; 194id1 id2 id3 c1 value 195500 100 1000 1000 1000 196select * from gap1 force index(i) where c1=1 lock in share mode; 197id1 id2 id3 c1 value 1980 0 1 1 1 199select * from gap3 force index(ui) where value=1 lock in share mode; 200id value 2011 1 202select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode; 203id1 id2 id3 c1 value 204select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode; 205id1 id2 id3 c1 value 206select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 207order by c1 lock in share mode; 208id1 id2 id3 c1 value 209select * from gap3 where id=1 lock in share mode; 210id value 2111 1 212select * from gap4 where id=1 lock in share mode; 213id value 2141 1 215select * from gap4 where id in (1, 2, 3) lock in share mode; 216id value 2171 1 2182 2 2193 3 220select * from gap4 lock in share mode; 221id value 2222 2 2234 4 2241 1 2253 3 2265 5 227select * from gap4 where id between 3 and 7 lock in share mode; 228id value 2293 3 2304 4 2315 5 232set session autocommit=1; 233select * from gap1 limit 1 lock in share mode; 234id1 id2 id3 c1 value 2350 0 1 1 1 236select * from gap1 where value != 100 limit 1 lock in share mode; 237id1 id2 id3 c1 value 2380 0 1 1 1 239select * from gap1 where id1=1 lock in share mode; 240id1 id2 id3 c1 value 2411 0 2 2 2 2421 0 3 3 3 243select * from gap1 where id1=1 and id2= 1 lock in share mode; 244id1 id2 id3 c1 value 245select * from gap1 where id1=1 and id2= 1 and id3 != 1 lock in share mode; 246id1 id2 id3 c1 value 247select * from gap1 where id1=1 and id2= 1 and id3 248between 1 and 3 lock in share mode; 249id1 id2 id3 c1 value 250select * from gap1 where id1=1 and id2= 1 order by id3 asc 251limit 1 lock in share mode; 252id1 id2 id3 c1 value 253select * from gap1 where id1=1 and id2= 1 order by id3 desc 254limit 1 lock in share mode; 255id1 id2 id3 c1 value 256select * from gap1 order by id1 asc limit 1 lock in share mode; 257id1 id2 id3 c1 value 2580 0 1 1 1 259select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 lock in share mode; 260id1 id2 id3 c1 value 2610 0 1 1 1 262select * from gap1 order by id1 desc limit 1 lock in share mode; 263id1 id2 id3 c1 value 264500 100 1000 1000 1000 265select * from gap1 order by id1 desc, id2 desc, id3 desc 266limit 1 lock in share mode; 267id1 id2 id3 c1 value 268500 100 1000 1000 1000 269select * from gap1 force index(i) where c1=1 lock in share mode; 270id1 id2 id3 c1 value 2710 0 1 1 1 272select * from gap3 force index(ui) where value=1 lock in share mode; 273id value 2741 1 275select * from gap1 where id1=1 and id2=1 and id3=1 lock in share mode; 276id1 id2 id3 c1 value 277select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) lock in share mode; 278id1 id2 id3 c1 value 279select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 280order by c1 lock in share mode; 281id1 id2 id3 c1 value 282select * from gap3 where id=1 lock in share mode; 283id value 2841 1 285select * from gap4 where id=1 lock in share mode; 286id value 2871 1 288select * from gap4 where id in (1, 2, 3) lock in share mode; 289id value 2901 1 2912 2 2923 3 293select * from gap4 lock in share mode; 294id value 2952 2 2964 4 2971 1 2983 3 2995 5 300select * from gap4 where id between 3 and 7 lock in share mode; 301id value 3023 3 3034 4 3045 5 305set session autocommit=0; 306select * from gap1 limit 1 ; 307id1 id2 id3 c1 value 3080 0 1 1 1 309select * from gap1 where value != 100 limit 1 ; 310id1 id2 id3 c1 value 3110 0 1 1 1 312select * from gap1 where id1=1 ; 313id1 id2 id3 c1 value 3141 0 2 2 2 3151 0 3 3 3 316select * from gap1 where id1=1 and id2= 1 ; 317id1 id2 id3 c1 value 318select * from gap1 where id1=1 and id2= 1 and id3 != 1 ; 319id1 id2 id3 c1 value 320select * from gap1 where id1=1 and id2= 1 and id3 321between 1 and 3 ; 322id1 id2 id3 c1 value 323select * from gap1 where id1=1 and id2= 1 order by id3 asc 324limit 1 ; 325id1 id2 id3 c1 value 326select * from gap1 where id1=1 and id2= 1 order by id3 desc 327limit 1 ; 328id1 id2 id3 c1 value 329select * from gap1 order by id1 asc limit 1 ; 330id1 id2 id3 c1 value 3310 0 1 1 1 332select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ; 333id1 id2 id3 c1 value 3340 0 1 1 1 335select * from gap1 order by id1 desc limit 1 ; 336id1 id2 id3 c1 value 337500 100 1000 1000 1000 338select * from gap1 order by id1 desc, id2 desc, id3 desc 339limit 1 ; 340id1 id2 id3 c1 value 341500 100 1000 1000 1000 342select * from gap1 force index(i) where c1=1 ; 343id1 id2 id3 c1 value 3440 0 1 1 1 345select * from gap3 force index(ui) where value=1 ; 346id value 3471 1 348select * from gap1 where id1=1 and id2=1 and id3=1 ; 349id1 id2 id3 c1 value 350select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ; 351id1 id2 id3 c1 value 352select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 353order by c1 ; 354id1 id2 id3 c1 value 355select * from gap3 where id=1 ; 356id value 3571 1 358select * from gap4 where id=1 ; 359id value 3601 1 361select * from gap4 where id in (1, 2, 3) ; 362id value 3631 1 3642 2 3653 3 366select * from gap4 ; 367id value 3682 2 3694 4 3701 1 3713 3 3725 5 373select * from gap4 where id between 3 and 7 ; 374id value 3753 3 3764 4 3775 5 378set session autocommit=1; 379select * from gap1 limit 1 ; 380id1 id2 id3 c1 value 3810 0 1 1 1 382select * from gap1 where value != 100 limit 1 ; 383id1 id2 id3 c1 value 3840 0 1 1 1 385select * from gap1 where id1=1 ; 386id1 id2 id3 c1 value 3871 0 2 2 2 3881 0 3 3 3 389select * from gap1 where id1=1 and id2= 1 ; 390id1 id2 id3 c1 value 391select * from gap1 where id1=1 and id2= 1 and id3 != 1 ; 392id1 id2 id3 c1 value 393select * from gap1 where id1=1 and id2= 1 and id3 394between 1 and 3 ; 395id1 id2 id3 c1 value 396select * from gap1 where id1=1 and id2= 1 order by id3 asc 397limit 1 ; 398id1 id2 id3 c1 value 399select * from gap1 where id1=1 and id2= 1 order by id3 desc 400limit 1 ; 401id1 id2 id3 c1 value 402select * from gap1 order by id1 asc limit 1 ; 403id1 id2 id3 c1 value 4040 0 1 1 1 405select * from gap1 order by id1 asc, id2 asc, id3 asc limit 1 ; 406id1 id2 id3 c1 value 4070 0 1 1 1 408select * from gap1 order by id1 desc limit 1 ; 409id1 id2 id3 c1 value 410500 100 1000 1000 1000 411select * from gap1 order by id1 desc, id2 desc, id3 desc 412limit 1 ; 413id1 id2 id3 c1 value 414500 100 1000 1000 1000 415select * from gap1 force index(i) where c1=1 ; 416id1 id2 id3 c1 value 4170 0 1 1 1 418select * from gap3 force index(ui) where value=1 ; 419id value 4201 1 421select * from gap1 where id1=1 and id2=1 and id3=1 ; 422id1 id2 id3 c1 value 423select * from gap1 where id1=1 and id2=1 and id3 in (1, 2, 3) ; 424id1 id2 id3 c1 value 425select * from gap1 where id1=1 and id2=1 and id3=1 and value=1 426order by c1 ; 427id1 id2 id3 c1 value 428select * from gap3 where id=1 ; 429id value 4301 1 431select * from gap4 where id=1 ; 432id value 4331 1 434select * from gap4 where id in (1, 2, 3) ; 435id value 4361 1 4372 2 4383 3 439select * from gap4 ; 440id value 4412 2 4424 4 4431 1 4443 3 4455 5 446select * from gap4 where id between 3 and 7 ; 447id value 4483 3 4494 4 4505 5 451set session autocommit=0; 452insert into gap1 (id1, id2, id3) values (-1,-1,-1); 453insert into gap1 (id1, id2, id3) values (-1,-1,-1) 454on duplicate key update value=100; 455update gap1 set value=100 where id1=1; 456update gap1 set value=100 where id1=1 and id2=1 and id3=1; 457delete from gap1 where id1=2; 458delete from gap1 where id1=-1 and id2=-1 and id3=-1; 459commit; 460set session autocommit=1; 461insert into gap1 (id1, id2, id3) values (-1,-1,-1); 462insert into gap1 (id1, id2, id3) values (-1,-1,-1) 463on duplicate key update value=100; 464update gap1 set value=100 where id1=1; 465update gap1 set value=100 where id1=1 and id2=1 and id3=1; 466delete from gap1 where id1=2; 467delete from gap1 where id1=-1 and id2=-1 and id3=-1; 468commit; 469drop table gap1, gap2, gap3, gap4; 470