1DROP TABLE IF EXISTS test; 2connect con1,localhost,root,,; 3SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 4connect con2,localhost,root,,; 5SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 6connect con3,localhost,root,,; 7SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 8connection con1; 9create table test (id int primary key, value int) engine=rocksdb; 10connection con1; 11truncate table test; 12insert into test (id, value) values (1, 10), (2, 20); 13begin; 14connection con2; 15begin; 16connection con3; 17begin; 18connection con1; 19select * from test; 20id value 211 10 222 20 23update test set value = 101 where id = 1; 24connection con2; 25select * from test; 26id value 271 10 282 20 29connection con1; 30rollback; 31connection con2; 32select * from test; 33id value 341 10 352 20 36commit; 37connection con1; 38truncate table test; 39insert into test (id, value) values (1, 10), (2, 20); 40begin; 41connection con2; 42begin; 43connection con3; 44begin; 45connection con1; 46update test set value = 101 where id = 1; 47connection con2; 48select * from test; 49id value 501 10 512 20 52connection con1; 53update test set value = 11 where id = 1; 54commit; 55connection con2; 56select * from test; 57id value 581 11 592 20 60commit; 61connection con1; 62truncate table test; 63insert into test (id, value) values (1, 10), (2, 20); 64begin; 65connection con2; 66begin; 67connection con3; 68begin; 69connection con1; 70update test set value = 11 where id = 1; 71connection con2; 72update test set value = 22 where id = 2; 73connection con1; 74select * from test where id = 2; 75id value 762 20 77connection con2; 78select * from test where id = 1; 79id value 801 10 81connection con1; 82commit; 83connection con2; 84commit; 85connection con1; 86truncate table test; 87insert into test (id, value) values (1, 10), (2, 20); 88begin; 89connection con2; 90begin; 91connection con3; 92begin; 93connection con1; 94update test set value = 11 where id = 1; 95update test set value = 19 where id = 2; 96connection con2; 97update test set value = 12 where id = 1; 98connection con1; 99commit; 100connection con2; 101connection con3; 102select * from test; 103id value 1041 11 1052 19 106connection con2; 107update test set value = 18 where id = 2; 108connection con3; 109select * from test; 110id value 1111 11 1122 19 113connection con2; 114commit; 115connection con3; 116select * from test; 117id value 1181 12 1192 18 120commit; 121connection con1; 122truncate table test; 123insert into test (id, value) values (1, 10), (2, 20); 124begin; 125connection con2; 126begin; 127connection con3; 128begin; 129connection con1; 130select * from test where value = 30; 131id value 132connection con2; 133insert into test (id, value) values(3, 30); 134commit; 135connection con1; 136select * from test where value % 3 = 0; 137id value 1383 30 139commit; 140connection con1; 141truncate table test; 142insert into test (id, value) values (1, 10), (2, 20); 143begin; 144connection con2; 145begin; 146connection con3; 147begin; 148connection con1; 149update test set value = value + 10; 150connection con2; 151select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; 152select * from test; 153id value 1541 10 1552 20 156delete from test where value = 20; 157connection con1; 158commit; 159connection con2; 160select * from test; 161id value 1622 30 163commit; 164connection con1; 165truncate table test; 166insert into test (id, value) values (1, 10), (2, 20); 167begin; 168connection con2; 169begin; 170connection con3; 171begin; 172connection con1; 173select * from test where id = 1; 174id value 1751 10 176connection con2; 177select * from test where id = 1; 178id value 1791 10 180connection con1; 181update test set value = 11 where id = 1; 182connection con2; 183update test set value = 12 where id = 1; 184connection con1; 185commit; 186connection con2; 187select * from test; 188id value 1891 12 1902 20 191commit; 192connection con1; 193truncate table test; 194insert into test (id, value) values (1, 10), (2, 20); 195begin; 196connection con2; 197begin; 198connection con3; 199begin; 200connection con1; 201select * from test where id = 1; 202id value 2031 10 204connection con2; 205select * from test where id = 1; 206id value 2071 10 208select * from test where id = 2; 209id value 2102 20 211update test set value = 12 where id = 1; 212update test set value = 18 where id = 2; 213commit; 214connection con1; 215select * from test where id = 2; 216id value 2172 18 218commit; 219connection con1; 220truncate table test; 221insert into test (id, value) values (1, 10), (2, 20); 222begin; 223connection con2; 224begin; 225connection con3; 226begin; 227connection con1; 228select * from test where value % 5 = 0; 229id value 2301 10 2312 20 232connection con2; 233update test set value = 12 where value = 10; 234commit; 235connection con1; 236select * from test where value % 3 = 0; 237id value 2381 12 239commit; 240connection con1; 241truncate table test; 242insert into test (id, value) values (1, 10), (2, 20); 243begin; 244connection con2; 245begin; 246connection con3; 247begin; 248connection con1; 249select * from test where id = 1; 250id value 2511 10 252connection con2; 253select * from test; 254id value 2551 10 2562 20 257update test set value = 12 where id = 1; 258update test set value = 18 where id = 2; 259commit; 260connection con1; 261delete from test where value = 20; 262select * from test where id = 2; 263id value 2642 18 265commit; 266connection con1; 267truncate table test; 268insert into test (id, value) values (1, 10), (2, 20); 269begin; 270connection con2; 271begin; 272connection con3; 273begin; 274connection con1; 275select * from test where id in (1,2); 276id value 2771 10 2782 20 279connection con2; 280select * from test where id in (1,2); 281id value 2821 10 2832 20 284connection con1; 285update test set value = 11 where id = 1; 286connection con2; 287update test set value = 21 where id = 2; 288connection con1; 289commit; 290connection con2; 291commit; 292connection con1; 293truncate table test; 294insert into test (id, value) values (1, 10), (2, 20); 295begin; 296connection con2; 297begin; 298connection con3; 299begin; 300connection con1; 301select * from test where value % 3 = 0; 302id value 303connection con2; 304select * from test where value % 3 = 0; 305id value 306connection con1; 307insert into test (id, value) values(3, 30); 308connection con2; 309insert into test (id, value) values(4, 42); 310connection con1; 311commit; 312connection con2; 313commit; 314select * from test where value % 3 = 0; 315id value 3163 30 3174 42 318connection con1; 319select * from test where value % 3 = 0; 320id value 3213 30 3224 42 323connection default; 324drop table test; 325disconnect con1; 326disconnect con2; 327disconnect con3; 328DROP TABLE IF EXISTS test; 329connect con1,localhost,root,,; 330SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 331connect con2,localhost,root,,; 332SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 333connect con3,localhost,root,,; 334SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 335connection con1; 336create table test (id int primary key, value int) engine=rocksdb; 337connection con1; 338truncate table test; 339insert into test (id, value) values (1, 10), (2, 20); 340begin; 341connection con2; 342begin; 343connection con3; 344begin; 345connection con1; 346select * from test; 347id value 3481 10 3492 20 350update test set value = 101 where id = 1; 351connection con2; 352select * from test; 353id value 3541 10 3552 20 356connection con1; 357rollback; 358connection con2; 359select * from test; 360id value 3611 10 3622 20 363commit; 364connection con1; 365truncate table test; 366insert into test (id, value) values (1, 10), (2, 20); 367begin; 368connection con2; 369begin; 370connection con3; 371begin; 372connection con1; 373update test set value = 101 where id = 1; 374connection con2; 375select * from test; 376id value 3771 10 3782 20 379connection con1; 380update test set value = 11 where id = 1; 381commit; 382connection con2; 383select * from test; 384id value 3851 10 3862 20 387commit; 388connection con1; 389truncate table test; 390insert into test (id, value) values (1, 10), (2, 20); 391begin; 392connection con2; 393begin; 394connection con3; 395begin; 396connection con1; 397update test set value = 11 where id = 1; 398connection con2; 399update test set value = 22 where id = 2; 400connection con1; 401select * from test where id = 2; 402id value 4032 20 404connection con2; 405select * from test where id = 1; 406id value 4071 10 408connection con1; 409commit; 410connection con2; 411commit; 412connection con1; 413truncate table test; 414insert into test (id, value) values (1, 10), (2, 20); 415begin; 416connection con2; 417begin; 418connection con3; 419begin; 420connection con1; 421update test set value = 11 where id = 1; 422update test set value = 19 where id = 2; 423connection con2; 424update test set value = 12 where id = 1; 425connection con1; 426commit; 427connection con2; 428connection con3; 429select * from test; 430id value 4311 11 4322 19 433connection con2; 434update test set value = 18 where id = 2; 435connection con3; 436select * from test; 437id value 4381 11 4392 19 440connection con2; 441commit; 442connection con3; 443select * from test; 444id value 4451 11 4462 19 447commit; 448connection con1; 449truncate table test; 450insert into test (id, value) values (1, 10), (2, 20); 451begin; 452connection con2; 453begin; 454connection con3; 455begin; 456connection con1; 457select * from test where value = 30; 458id value 459connection con2; 460insert into test (id, value) values(3, 30); 461commit; 462connection con1; 463select * from test where value % 3 = 0; 464id value 465commit; 466connection con1; 467truncate table test; 468insert into test (id, value) values (1, 10), (2, 20); 469begin; 470connection con2; 471begin; 472connection con3; 473begin; 474connection con1; 475update test set value = value + 10; 476connection con2; 477select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; 478select * from test; 479id value 4801 10 4812 20 482delete from test where value = 20; 483connection con1; 484commit; 485connection con2; 486ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) 487select variable_value-@a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors'; 488variable_value-@a 4891 490commit; 491connection con1; 492truncate table test; 493insert into test (id, value) values (1, 10), (2, 20); 494begin; 495connection con2; 496begin; 497connection con3; 498begin; 499connection con1; 500select * from test where id = 1; 501id value 5021 10 503connection con2; 504select * from test where id = 1; 505id value 5061 10 507connection con1; 508update test set value = 11 where id = 1; 509connection con2; 510update test set value = 12 where id = 1; 511connection con1; 512commit; 513connection con2; 514ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) 515commit; 516connection con1; 517truncate table test; 518insert into test (id, value) values (1, 10), (2, 20); 519begin; 520connection con2; 521begin; 522connection con3; 523begin; 524connection con1; 525select * from test where id = 1; 526id value 5271 10 528connection con2; 529select * from test where id = 1; 530id value 5311 10 532select * from test where id = 2; 533id value 5342 20 535update test set value = 12 where id = 1; 536update test set value = 18 where id = 2; 537commit; 538connection con1; 539select * from test where id = 2; 540id value 5412 20 542commit; 543connection con1; 544truncate table test; 545insert into test (id, value) values (1, 10), (2, 20); 546begin; 547connection con2; 548begin; 549connection con3; 550begin; 551connection con1; 552select * from test where value % 5 = 0; 553id value 5541 10 5552 20 556connection con2; 557update test set value = 12 where value = 10; 558commit; 559connection con1; 560select * from test where value % 3 = 0; 561id value 562commit; 563connection con1; 564truncate table test; 565insert into test (id, value) values (1, 10), (2, 20); 566begin; 567connection con2; 568begin; 569connection con3; 570begin; 571connection con1; 572select * from test where id = 1; 573id value 5741 10 575connection con2; 576select * from test; 577id value 5781 10 5792 20 580update test set value = 12 where id = 1; 581update test set value = 18 where id = 2; 582commit; 583connection con1; 584delete from test where value = 20; 585ERROR 40001: Deadlock found when trying to get lock; try restarting transaction (snapshot conflict) 586commit; 587connection con1; 588truncate table test; 589insert into test (id, value) values (1, 10), (2, 20); 590begin; 591connection con2; 592begin; 593connection con3; 594begin; 595connection con1; 596select * from test where id in (1,2); 597id value 5981 10 5992 20 600connection con2; 601select * from test where id in (1,2); 602id value 6031 10 6042 20 605connection con1; 606update test set value = 11 where id = 1; 607connection con2; 608update test set value = 21 where id = 2; 609connection con1; 610commit; 611connection con2; 612commit; 613connection con1; 614truncate table test; 615insert into test (id, value) values (1, 10), (2, 20); 616begin; 617connection con2; 618begin; 619connection con3; 620begin; 621connection con1; 622select * from test where value % 3 = 0; 623id value 624connection con2; 625select * from test where value % 3 = 0; 626id value 627connection con1; 628insert into test (id, value) values(3, 30); 629connection con2; 630insert into test (id, value) values(4, 42); 631connection con1; 632commit; 633connection con2; 634commit; 635select * from test where value % 3 = 0; 636id value 6373 30 6384 42 639connection con1; 640select * from test where value % 3 = 0; 641id value 6423 30 6434 42 644connection default; 645drop table test; 646disconnect con1; 647disconnect con2; 648disconnect con3; 649