github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/select_for_update.result (about) 1 create table su_01(c1 int not null,c2 varchar(25),c3 int,primary key(c1),unique index u1(c2)); 2 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 3 begin; 4 select * from su_01 where c1=1 for update; 5 c1 c2 c3 6 1 results 20 7 use select_for_update; 8 select * from su_01 where c1=1; 9 c1 c2 c3 10 1 results 20 11 update su_01 set c2='loo' where c1=2; 12 select * from su_01; 13 c1 c2 c3 14 1 results 20 15 2 loo 50 16 3 kelly 60 17 4 yellow 70 18 commit; 19 truncate table su_01; 20 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 21 begin; 22 select * from su_01 where c1=1 for update; 23 c1 c2 c3 24 1 results 20 25 use select_for_update; 26 select * from su_01 where c1=1; 27 c1 c2 c3 28 1 results 20 29 insert into su_01 values(6,'polly',70); 30 select * from su_01; 31 c1 c2 c3 32 1 results 20 33 2 plo 50 34 3 kelly 60 35 4 yellow 70 36 6 polly 70 37 commit; 38 truncate table su_01; 39 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 40 begin; 41 select * from su_01 where c1=1 for update; 42 c1 c2 c3 43 1 results 20 44 use select_for_update; 45 select * from su_01 where c1=1; 46 c1 c2 c3 47 1 results 20 48 delete from su_01 where c1=1; 49 select * from su_01; 50 c1 c2 c3 51 2 plo 50 52 3 kelly 60 53 4 yellow 70 54 commit; 55 insert into su_01 values(1,'results',20); 56 begin; 57 select * from su_01 where c1=1 for update; 58 c1 c2 c3 59 1 results 20 60 use select_for_update; 61 truncate table su_01 ; 62 select * from su_01; 63 c1 c2 c3 64 commit; 65 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 66 begin; 67 select * from su_01 where c1=1 for update; 68 c1 c2 c3 69 1 results 20 70 use select_for_update; 71 alter table su_01 drop index u1; 72 select * from su_01; 73 c1 c2 c3 74 1 results 20 75 2 plo 50 76 3 kelly 60 77 4 yellow 70 78 show create table su_01; 79 Table Create Table 80 su_01 CREATE TABLE `su_01` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 81 commit; 82 begin; 83 select * from su_01 where c1=1 for update; 84 c1 c2 c3 85 1 results 20 86 update su_01 set c2='desc' where c1=1; 87 update su_01 set c3=c3-1 where c1=1; 88 select * from su_01 where c1=1; 89 c1 c2 c3 90 1 desc 19 91 insert into su_01 values(5,'polly',80); 92 commit; 93 select * from su_01; 94 c1 c2 c3 95 1 desc 19 96 2 plo 50 97 3 kelly 60 98 4 yellow 70 99 5 polly 80 100 truncate table su_01; 101 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 102 begin; 103 select * from su_01 where c1=1 for update; 104 c1 c2 c3 105 1 results 20 106 use select_for_update; 107 drop table su_01; 108 select * from su_01; 109 SQL parser error: table "su_01" does not exist 110 commit; 111 create table su_01(c1 int not null,c2 varchar(25),c3 int,primary key(c1),unique index u1(c2)); 112 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 113 begin; 114 select * from su_01 where c1=7 for update; 115 c1 c2 c3 116 use select_for_update; 117 select * from su_01; 118 c1 c2 c3 119 1 results 20 120 2 plo 50 121 3 kelly 60 122 4 yellow 70 123 delete from su_01 where c1=7; 124 commit; 125 begin; 126 select * from su_01 where c1=7 for update; 127 c1 c2 c3 128 use select_for_update; 129 select * from su_01; 130 c1 c2 c3 131 1 results 20 132 2 plo 50 133 3 kelly 60 134 4 yellow 70 135 update su_01 set c3=c3*10 where c1=1; 136 commit; 137 drop table su_01; 138 create table su_01_1(c1 int not null,c2 varchar(25),c3 int,primary key(c1)); 139 insert into su_01_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 140 start transaction ; 141 select * from su_01_1 where c1>1 and c3<70 for update; 142 c1 c2 c3 143 2 plo 50 144 3 kelly 60 145 use select_for_update; 146 update su_01_1 set c3=c3+10 where c3=70; 147 select * from su_01_1; 148 c1 c2 c3 149 1 results 20 150 2 plo 50 151 3 kelly 60 152 4 yellow 80 153 commit; 154 select * from su_01_1; 155 c1 c2 c3 156 1 results 20 157 2 plo 50 158 3 kelly 60 159 4 yellow 80 160 start transaction; 161 select * from su_01_1 where c1>1 and c3<70 for update; 162 c1 c2 c3 163 2 plo 50 164 3 kelly 60 165 use select_for_update; 166 update su_01_1 set c3=c3-1 where c1=4; 167 select * from su_01_1; 168 c1 c2 c3 169 1 results 20 170 2 plo 50 171 3 kelly 60 172 4 yellow 79 173 rollback; 174 start transaction; 175 select * from su_01_1 where c1>1 and c3<70 for update; 176 c1 c2 c3 177 2 plo 50 178 3 kelly 60 179 begin; 180 use select_for_update; 181 update su_01_1 set c3=101 where c1=3; 182 select * from su_01_1; 183 c1 c2 c3 184 1 results 20 185 2 plo 50 186 3 kelly 101 187 4 yellow 79 188 rollback ; 189 rollback; 190 start transaction ; 191 select * from su_01_1 where c1>1 and c3<70 for update; 192 c1 c2 c3 193 2 plo 50 194 3 kelly 60 195 begin; 196 use select_for_update; 197 delete from su_01_1 where c1=1; 198 select * from su_01_1; 199 c1 c2 c3 200 2 plo 50 201 3 kelly 60 202 4 yellow 79 203 rollback ; 204 commit; 205 start transaction ; 206 select * from su_01_1 where c1>1 and c3<70 for update; 207 c1 c2 c3 208 2 plo 50 209 3 kelly 60 210 use select_for_update; 211 delete from su_01_1 where c2="plo"; 212 select * from su_01_1; 213 c1 c2 c3 214 1 results 20 215 3 kelly 60 216 4 yellow 79 217 rollback; 218 start transaction; 219 select * from su_01_1 where c1>1 and c3<70 for update; 220 c1 c2 c3 221 3 kelly 60 222 use select_for_update; 223 insert into su_01_1 values (10,'full',100); 224 select * from su_01_1; 225 c1 c2 c3 226 1 results 20 227 3 kelly 60 228 4 yellow 79 229 10 full 100 230 rollback; 231 start transaction; 232 select * from su_01_1 where c1>1 and c3<70 for update; 233 c1 c2 c3 234 3 kelly 60 235 use select_for_update; 236 truncate table su_01_1; 237 select * from su_01_1; 238 c1 c2 c3 239 rollback; 240 insert into su_01_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 241 start transaction; 242 select * from su_01_1 where c1>1 and c3<70 for update; 243 c1 c2 c3 244 2 plo 50 245 3 kelly 60 246 use select_for_update; 247 drop table su_01_1; 248 select * from su_01_1; 249 SQL parser error: table "su_01_1" does not exist 250 rollback; 251 create table su_02(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 252 insert into su_02 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 253 begin; 254 select * from su_02 where c3>35 for update; 255 c1 c2 c3 256 2 plo 50 257 3 kelly 60 258 4 yellow 70 259 use select_for_update; 260 select * from su_02 where c3>35; 261 c1 c2 c3 262 2 plo 50 263 3 kelly 60 264 4 yellow 70 265 insert into su_02 values (8,'results',100); 266 select * from su_02; 267 c1 c2 c3 268 1 results 20 269 2 plo 50 270 3 kelly 60 271 4 yellow 70 272 8 results 100 273 commit; 274 begin; 275 select * from su_02 where c3>35 for update; 276 c1 c2 c3 277 2 plo 50 278 3 kelly 60 279 4 yellow 70 280 8 results 100 281 use select_for_update; 282 select * from su_02 where c3>35; 283 c1 c2 c3 284 2 plo 50 285 3 kelly 60 286 4 yellow 70 287 8 results 100 288 delete from su_02 where c3<60; 289 select * from su_02; 290 c1 c2 c3 291 3 kelly 60 292 4 yellow 70 293 8 results 100 294 commit; 295 begin; 296 select * from su_02 where c3>35 for update; 297 c1 c2 c3 298 3 kelly 60 299 4 yellow 70 300 8 results 100 301 use select_for_update; 302 select * from su_02 where c3>35; 303 c1 c2 c3 304 3 kelly 60 305 4 yellow 70 306 8 results 100 307 update su_02 set c2='kitty' where c3=70; 308 select * from su_02; 309 c1 c2 c3 310 3 kelly 60 311 4 kitty 70 312 8 results 100 313 commit; 314 begin; 315 select * from su_02 where c3>35 for update; 316 c1 c2 c3 317 3 kelly 60 318 4 kitty 70 319 8 results 100 320 use select_for_update; 321 alter table su_02 drop index u1; 322 show create table su_02; 323 Table Create Table 324 su_02 CREATE TABLE `su_02` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL\n) 325 commit; 326 begin; 327 select * from su_02 where c3>35 for update; 328 c1 c2 c3 329 3 kelly 60 330 4 kitty 70 331 8 results 100 332 update su_02 set c3=c3-1 where c3=60; 333 insert into su_02 values(5,'polly',80); 334 select * from su_02; 335 c1 c2 c3 336 3 kelly 59 337 4 kitty 70 338 8 results 100 339 5 polly 80 340 commit; 341 select * from su_02; 342 c1 c2 c3 343 3 kelly 59 344 4 kitty 70 345 8 results 100 346 5 polly 80 347 truncate table su_02; 348 select * from su_02; 349 c1 c2 c3 350 insert into su_02 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 351 begin; 352 select * from su_02 where c3>35 for update; 353 c1 c2 c3 354 2 plo 50 355 3 kelly 60 356 4 yellow 70 357 use select_for_update; 358 drop table su_02; 359 commit; 360 drop table if exists su_02; 361 create table su_02_1(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 362 insert into su_02_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 363 start transaction; 364 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 365 c1 c2 c3 366 2 plo 50 367 4 yellow 70 368 use select_for_update; 369 update su_02_1 set c2='non' where c3=60; 370 select * from su_02_1; 371 c1 c2 c3 372 1 results 20 373 2 plo 50 374 3 non 60 375 4 yellow 70 376 commit; 377 start transaction; 378 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 379 c1 c2 c3 380 2 plo 50 381 3 non 60 382 4 yellow 70 383 use select_for_update; 384 update su_02_1 set c3=c3+100 where c2='results'; 385 select * from su_02_1; 386 c1 c2 c3 387 1 results 120 388 2 plo 50 389 3 non 60 390 4 yellow 70 391 commit; 392 start transaction; 393 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 394 c1 c2 c3 395 2 plo 50 396 3 non 60 397 4 yellow 70 398 use select_for_update; 399 delete from su_02_1 where c3=50; 400 select * from su_02_1; 401 c1 c2 c3 402 1 results 120 403 3 non 60 404 4 yellow 70 405 commit; 406 start transaction; 407 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 408 c1 c2 c3 409 3 non 60 410 4 yellow 70 411 use select_for_update; 412 alter table su_02_1 drop index u1; 413 show create table su_02_1; 414 Table Create Table 415 su_02_1 CREATE TABLE `su_02_1` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL\n) 416 commit; 417 start transaction; 418 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 419 c1 c2 c3 420 3 non 60 421 4 yellow 70 422 use select_for_update; 423 truncate table su_02_1; 424 select * from su_02_1; 425 c1 c2 c3 426 commit; 427 insert into su_02_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 428 start transaction; 429 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 430 c1 c2 c3 431 2 plo 50 432 4 yellow 70 433 use select_for_update; 434 drop table su_02_1; 435 select * from su_02_1; 436 SQL parser error: table "su_02_1" does not exist 437 commit; 438 create table su_02_2(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 439 insert into su_02_2 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 440 begin; 441 select * from su_02_2 where c3>55 and c3<65 for update; 442 c1 c2 c3 443 3 kelly 60 444 use select_for_update; 445 update su_02_2 set c1=c1+500 where c3=60; 446 select * from su_02_2; 447 c1 c2 c3 448 1 results 20 449 2 plo 50 450 503 kelly 60 451 4 yellow 70 452 commit; 453 begin; 454 select * from su_02_2 where c3>55 and c3<65 for update; 455 c1 c2 c3 456 503 kelly 60 457 use select_for_update; 458 update su_02_2 set c1=30 where c2 like 'yell%'; 459 select * from su_02_2; 460 c1 c2 c3 461 1 results 20 462 2 plo 50 463 503 kelly 60 464 30 yellow 70 465 commit; 466 create table su_03(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 467 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 468 begin; 469 select * from su_03 where c3 in(50,70) for update; 470 c1 c2 c3 471 2 plo 50 472 4 yellow 70 473 use select_for_update; 474 select * from su_03 where c3>35; 475 c1 c2 c3 476 2 plo 50 477 3 kelly 60 478 4 yellow 70 479 insert into su_03 values (8,'results',100); 480 select * from su_03 ; 481 c1 c2 c3 482 1 results 20 483 2 plo 50 484 3 kelly 60 485 4 yellow 70 486 8 results 100 487 commit; 488 begin; 489 select * from su_03 where c3 in(50,70) for update; 490 c1 c2 c3 491 2 plo 50 492 4 yellow 70 493 use select_for_update; 494 select * from su_03 where c3>35; 495 c1 c2 c3 496 2 plo 50 497 3 kelly 60 498 4 yellow 70 499 8 results 100 500 update su_03 set c2='kitty' where c3=70; 501 select * from su_03; 502 c1 c2 c3 503 1 results 20 504 2 plo 50 505 3 kelly 60 506 4 kitty 70 507 8 results 100 508 commit; 509 begin; 510 select * from su_03 where c3 in(50,70) for update; 511 c1 c2 c3 512 2 plo 50 513 4 kitty 70 514 use select_for_update; 515 alter table su_03 drop index u1; 516 show create table su_03; 517 Table Create Table 518 su_03 CREATE TABLE `su_03` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 519 commit; 520 begin; 521 select * from su_03 where c3 in(50,70) for update; 522 c1 c2 c3 523 2 plo 50 524 4 kitty 70 525 use select_for_update; 526 select * from su_03 where c3>35; 527 c1 c2 c3 528 2 plo 50 529 3 kelly 60 530 4 kitty 70 531 8 results 100 532 delete from su_03 where c3 between 10 and 70; 533 select * from su_03; 534 c1 c2 c3 535 8 results 100 536 commit; 537 truncate table su_03; 538 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 539 begin; 540 select * from su_03 where c3 in(50,70) for update; 541 c1 c2 c3 542 2 plo 50 543 4 yellow 70 544 update su_03 set c3=c3-1 where c3=70; 545 insert into su_03 values(5,'polly',80); 546 delete from su_03 where c3=50; 547 select * from su_03; 548 c1 c2 c3 549 1 results 20 550 3 kelly 60 551 4 yellow 69 552 5 polly 80 553 commit; 554 update su_03 set c3=c3-1 where c3=70; 555 select * from su_03; 556 c1 c2 c3 557 1 results 20 558 3 kelly 60 559 4 yellow 69 560 5 polly 80 561 truncate table su_03; 562 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 563 begin; 564 select * from su_03 where c3 in(50,70) for update; 565 c1 c2 c3 566 2 plo 50 567 4 yellow 70 568 use select_for_update; 569 drop table su_03; 570 commit; 571 drop table if exists su_03; 572 create table su_03(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 573 insert into su_03 values(1,'results',20); 574 begin; 575 select * from su_03 where c3 in(50,70) for update; 576 c1 c2 c3 577 use select_for_update; 578 alter table su_03 drop index u1; 579 show create table su_03; 580 Table Create Table 581 su_03 CREATE TABLE `su_03` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 582 commit; 583 drop table if exists su_03; 584 create table su_03_1(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 585 insert into su_03_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 586 start transaction ; 587 select c1 from su_03_1 where c3 between 10 and 30 for update; 588 c1 589 1 590 use select_for_update; 591 update su_03_1 set c2='high' where c2 like "res%"; 592 select * from su_03_1; 593 c1 c2 c3 594 1 high 20 595 2 plo 50 596 3 kelly 60 597 4 yellow 70 598 rollback; 599 start transaction ; 600 select c1 from su_03_1 where c3 between 10 and 30 for update; 601 c1 602 1 603 use select_for_update; 604 insert into su_03_1 values(10,'boo',120); 605 select * from su_03_1; 606 c1 c2 c3 607 1 high 20 608 2 plo 50 609 3 kelly 60 610 4 yellow 70 611 10 boo 120 612 rollback; 613 start transaction ; 614 select c1 from su_03_1 where c3 between 10 and 30 for update; 615 c1 616 1 617 begin; 618 use select_for_update; 619 update su_03_1 set c2='high' where c3>=120; 620 select * from su_03_1; 621 c1 c2 c3 622 1 high 20 623 2 plo 50 624 3 kelly 60 625 4 yellow 70 626 10 high 120 627 rollback; 628 select * from su_03_1; 629 c1 c2 c3 630 1 high 20 631 2 plo 50 632 3 kelly 60 633 4 yellow 70 634 10 boo 120 635 commit; 636 drop table su_03_1; 637 create table su_04(c1 int not null,c2 varchar(25),c3 int); 638 insert into su_04 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 639 begin; 640 select * from su_04 where c2='kelly' for update; 641 c1 c2 c3 642 3 kelly 60 643 use select_for_update; 644 update su_04 set c3=c3-1 where c2='kelly'; 645 select * from su_04; 646 c1 c2 c3 647 1 results 20 648 2 plo 50 649 3 kelly 59 650 4 yellow 70 651 update su_04 set c3=c3-1 where c2='kelly'; 652 insert into su_04 values (10,'mini',90); 653 select * from su_04; 654 c1 c2 c3 655 1 results 20 656 2 plo 50 657 3 kelly 58 658 4 yellow 70 659 10 mini 90 660 commit; 661 begin; 662 select * from su_04 where c2 in ('kelly') for update; 663 c1 c2 c3 664 3 kelly 58 665 start transaction; 666 use select_for_update; 667 select * from su_04 where c2='kelly'; 668 c1 c2 c3 669 3 kelly 58 670 delete from su_04 where c2='kelly'; 671 select * from su_04; 672 c1 c2 c3 673 1 results 20 674 2 plo 50 675 4 yellow 70 676 10 mini 90 677 commit; 678 select * from su_04; 679 c1 c2 c3 680 1 results 20 681 2 plo 50 682 4 yellow 70 683 10 mini 90 684 commit; 685 begin; 686 select * from su_04 where c2='kelly' for update; 687 c1 c2 c3 688 use select_for_update; 689 delete from su_04 where c1=4; 690 select * from su_04; 691 c1 c2 c3 692 1 results 20 693 2 plo 50 694 10 mini 90 695 update su_04 set c3=c3-1 where c2='kelly'; 696 select * from su_04; 697 c1 c2 c3 698 1 results 20 699 2 plo 50 700 10 mini 90 701 commit; 702 begin; 703 select * from su_04 where c2='results' for update; 704 c1 c2 c3 705 1 results 20 706 use select_for_update; 707 alter table su_04 add unique index a1(c1); 708 show create table su_04; 709 Table Create Table 710 su_04 CREATE TABLE `su_04` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` INT DEFAULT NULL,\nUNIQUE KEY `a1` (`c1`)\n) 711 commit; 712 begin; 713 select * from su_04 where c2='results' for update; 714 c1 c2 c3 715 1 results 20 716 insert into su_04 values(10,'tell',96); 717 Duplicate entry '10' for key '__mo_index_idx_col' 718 update su_04 set c2='wed'; 719 delete from su_04 where c1=2; 720 commit; 721 truncate table su_04; 722 insert into su_04 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 723 begin; 724 select * from su_04 where c2='kelly' for update; 725 c1 c2 c3 726 3 kelly 60 727 use select_for_update; 728 drop table su_04; 729 commit; 730 select * from su_04; 731 SQL parser error: table "su_04" does not exist 732 drop table if exists su_04; 733 create table su_05(c1 int not null primary key,c2 varchar(25),c3 decimal(6,2))partition by key(c1)partitions 4;; 734 insert into su_05 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',662.9),(6,'io',88.92); 735 select * from `%!%p0%!%su_05`; 736 c1 c2 c3 737 select * from `%!%p1%!%su_05`; 738 c1 c2 c3 739 4 mode 9.00 740 5 make 662.90 741 select * from `%!%p2%!%su_05`; 742 c1 c2 c3 743 3 mod 6.50 744 6 io 88.92 745 select * from `%!%p3%!%su_05`; 746 c1 c2 c3 747 1 mod 78.90 748 2 proto 0.34 749 begin; 750 select * from su_05 where c1>3 for update; 751 c1 c2 c3 752 5 make 662.90 753 4 mode 9.00 754 6 io 88.92 755 use select_for_update; 756 select * from su_05; 757 c1 c2 c3 758 1 mod 78.90 759 5 make 662.90 760 4 mode 9.00 761 3 mod 6.50 762 2 proto 0.34 763 6 io 88.92 764 update su_05 set c3=c3-1 where c1>2; 765 select * from su_05; 766 c1 c2 c3 767 1 mod 78.90 768 5 make 661.90 769 4 mode 8.00 770 3 mod 5.50 771 2 proto 0.34 772 6 io 87.92 773 commit; 774 update su_05 set c3=c3-1 where c1>2; 775 select * from su_05; 776 c1 c2 c3 777 1 mod 78.90 778 5 make 660.90 779 4 mode 7.00 780 3 mod 4.50 781 2 proto 0.34 782 6 io 86.92 783 begin; 784 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 785 c1 c2 c3 786 1 mod 78.90 787 3 mod 4.50 788 use select_for_update; 789 select * from su_05; 790 c1 c2 c3 791 1 mod 78.90 792 5 make 660.90 793 4 mode 7.00 794 3 mod 4.50 795 2 proto 0.34 796 6 io 86.92 797 delete from su_05 where c1=1; 798 select * from su_05; 799 c1 c2 c3 800 5 make 660.90 801 4 mode 7.00 802 3 mod 4.50 803 2 proto 0.34 804 6 io 86.92 805 commit; 806 begin; 807 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 808 c1 c2 c3 809 3 mod 4.50 810 use select_for_update; 811 select * from su_05; 812 c1 c2 c3 813 5 make 660.90 814 4 mode 7.00 815 3 mod 4.50 816 2 proto 0.34 817 6 io 86.92 818 truncate table su_05; 819 select * from su_05; 820 c1 c2 c3 821 commit; 822 insert into su_05 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',662.9),(6,'io',88.92); 823 begin; 824 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 825 c1 c2 c3 826 1 mod 78.90 827 3 mod 6.50 828 use select_for_update; 829 select * from su_05; 830 c1 c2 c3 831 1 mod 78.90 832 5 make 662.90 833 4 mode 9.00 834 3 mod 6.50 835 2 proto 0.34 836 6 io 88.92 837 insert into su_05 values(9,'kol',89.01); 838 select * from su_05; 839 c1 c2 c3 840 1 mod 78.90 841 5 make 662.90 842 9 kol 89.01 843 4 mode 9.00 844 3 mod 6.50 845 2 proto 0.34 846 6 io 88.92 847 commit; 848 begin; 849 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 850 c1 c2 c3 851 1 mod 78.90 852 3 mod 6.50 853 use select_for_update; 854 select * from su_05; 855 c1 c2 c3 856 1 mod 78.90 857 5 make 662.90 858 9 kol 89.01 859 4 mode 9.00 860 3 mod 6.50 861 2 proto 0.34 862 6 io 88.92 863 update su_05 set c2='polly' where c1=9; 864 select * from su_05; 865 c1 c2 c3 866 1 mod 78.90 867 5 make 662.90 868 9 polly 89.01 869 4 mode 9.00 870 3 mod 6.50 871 2 proto 0.34 872 6 io 88.92 873 commit; 874 drop table su_05; 875 create table su_05_1(c1 int auto_increment primary key,c2 varchar(25),c3 decimal(6,2))partition by key(c1)partitions 4;; 876 insert into su_05_1(c2,c3) values ('mod',78.9),('proto',0.34),('mod',6.5),('mode',9.0),('make',662.9),('io',88.92); 877 select * from `%!%p0%!%su_05_1`; 878 Table 'select_for_update.%!%p0%!%su_05_1' doesn't exist 879 select * from `%!%p1%!%su_05_1`; 880 c1 c2 c3 881 4 mode 9.00 882 5 make 662.90 883 select * from `%!%p2%!%su_05_1`; 884 c1 c2 c3 885 3 mod 6.50 886 6 io 88.92 887 select * from `%!%p3%!%su_05_1`; 888 c1 c2 c3 889 1 mod 78.90 890 2 proto 0.34 891 set autocommit=0; 892 select * from su_05_1 where c1=4 for update; 893 c1 c2 c3 894 4 mode 9.00 895 use select_for_update; 896 update su_05_1 set c2='polly' where c3=9.0; 897 Lock wait timeout exceeded; try restarting transaction 898 select * from su_05_1; 899 c1 c2 c3 900 1 mod 78.90 901 5 make 662.90 902 4 mode 9.00 903 3 mod 6.50 904 2 proto 0.34 905 6 io 88.92 906 commit; 907 select * from su_05_1 where c1=4 for update; 908 c1 c2 c3 909 4 mode 9.00 910 use select_for_update; 911 update su_05_1 set c3=c3-0.09 where c1=4; 912 Lock wait timeout exceeded; try restarting transaction 913 select * from su_05_1; 914 c1 c2 c3 915 1 mod 78.90 916 5 make 662.90 917 4 mode 9.00 918 3 mod 6.50 919 2 proto 0.34 920 6 io 88.92 921 commit; 922 select * from su_05_1 where c1=4 for update; 923 c1 c2 c3 924 4 mode 9.00 925 use select_for_update; 926 insert into su_05_1(c2,c3) values('xin',8.90); 927 select * from su_05_1; 928 c1 c2 c3 929 1 mod 78.90 930 5 make 662.90 931 4 mode 9.00 932 3 mod 6.50 933 7 xin 8.90 934 2 proto 0.34 935 6 io 88.92 936 commit; 937 select * from su_05_1 where c1=3 for update; 938 c1 c2 c3 939 3 mod 6.50 940 use select_for_update; 941 alter table su_05_1 add unique index s1(c3); 942 invalid input: can't add/drop column for partition table now 943 show create table su_05_1; 944 Table Create Table 945 su_05_1 CREATE TABLE `su_05_1` (\n`c1` INT NOT NULL AUTO_INCREMENT,\n`c2` VARCHAR(25) DEFAULT NULL,\n`c3` DECIMAL(6,2) DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) partition by key algorithm = 2 (c1) partitions 4 946 commit; 947 select * from su_05_1 where c1=3 for update; 948 c1 c2 c3 949 3 mod 6.50 950 use select_for_update; 951 truncate table su_05_1; 952 select * from su_05_1; 953 c1 c2 c3 954 commit; 955 insert into su_05_1(c2,c3) values ('mod',78.9),('proto',0.34),('mod',6.5),('mode',9.0),('make',662.9),('io',88.92); 956 select * from su_05_1 where c1=3 for update; 957 c1 c2 c3 958 3 mod 6.50 959 use select_for_update; 960 drop table su_05_1; 961 select * from su_05_1; 962 SQL parser error: table "su_05_1" does not exist 963 commit; 964 set autocommit=1; 965 create table su_06(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 966 insert into su_06 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 967 start transaction; 968 select * from su_06 where c1>=2 for update; 969 c1 c2 c3 970 2 plo 50 971 3 kelly 60 972 4 yellow 70 973 use select_for_update; 974 prepare stmt1 from 'update su_06 set c3=c3+1.09 where c1=?'; 975 set @var = 2; 976 execute stmt1 using @var; 977 select * from su_06; 978 c1 c2 c3 979 1 results 20 980 2 plo 51 981 3 kelly 60 982 4 yellow 70 983 commit; 984 start transaction; 985 select * from su_06 where c1>=2 for update; 986 c1 c2 c3 987 2 plo 51 988 3 kelly 60 989 4 yellow 70 990 use select_for_update; 991 prepare stmt1 from 'update su_06 set c3=c3+1.09 where c1=?'; 992 set @var = 1; 993 execute stmt1 using @var; 994 select * from su_06; 995 c1 c2 c3 996 1 results 21 997 2 plo 51 998 3 kelly 60 999 4 yellow 70 1000 commit; 1001 start transaction; 1002 select * from su_06 where c1>=2 for update; 1003 c1 c2 c3 1004 2 plo 51 1005 3 kelly 60 1006 4 yellow 70 1007 use select_for_update; 1008 prepare stmt1 from 'delete from su_06 where c3 in (?)'; 1009 set @var = 3; 1010 execute stmt1 using @var; 1011 select * from su_06; 1012 c1 c2 c3 1013 1 results 21 1014 2 plo 51 1015 3 kelly 60 1016 4 yellow 70 1017 commit; 1018 create table su_07(c1 int not null,c2 varchar(25),c3 int,primary key(c1),unique index u1(c2)); 1019 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 1020 begin; 1021 select * from su_07 where c1+2>=5 for update; 1022 c1 c2 c3 1023 3 kelly 60 1024 4 yellow 70 1025 7 results 20 1026 use select_for_update; 1027 select * from su_07 where c1=1; 1028 c1 c2 c3 1029 1 plo 50 1030 update su_07 set c2='loo' where c1=4; 1031 select * from su_07; 1032 c1 c2 c3 1033 1 plo 50 1034 3 kelly 60 1035 4 loo 70 1036 7 results 20 1037 commit; 1038 begin; 1039 select * from su_07 where c1+2>=5 for update; 1040 c1 c2 c3 1041 3 kelly 60 1042 4 loo 70 1043 7 results 20 1044 use select_for_update; 1045 select * from su_07 where c1=1; 1046 c1 c2 c3 1047 1 plo 50 1048 update su_07 set c2='cool' where c1=1; 1049 select * from su_07; 1050 c1 c2 c3 1051 7 results 20 1052 3 kelly 60 1053 4 loo 70 1054 1 cool 50 1055 commit; 1056 begin; 1057 select * from su_07 where c3-c1>20 for update; 1058 c1 c2 c3 1059 3 kelly 60 1060 4 loo 70 1061 1 cool 50 1062 use select_for_update; 1063 delete from su_07 where c1=1; 1064 select * from su_07; 1065 c1 c2 c3 1066 3 kelly 60 1067 4 loo 70 1068 7 results 20 1069 commit; 1070 truncate table su_07; 1071 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 1072 begin; 1073 select * from su_07 where c3/c1=20 for update; 1074 c1 c2 c3 1075 3 kelly 60 1076 use select_for_update; 1077 update su_07 set c3=c3/10 where c1=3; 1078 select * from su_07; 1079 c1 c2 c3 1080 1 plo 50 1081 3 kelly 6 1082 4 yellow 70 1083 7 results 20 1084 commit; 1085 begin; 1086 select * from su_07 where c3<c1*10 for update; 1087 c1 c2 c3 1088 3 kelly 6 1089 7 results 20 1090 use select_for_update; 1091 alter table su_07 drop index u1; 1092 select * from su_07; 1093 c1 c2 c3 1094 1 plo 50 1095 3 kelly 6 1096 4 yellow 70 1097 7 results 20 1098 commit; 1099 begin; 1100 select * from su_07 where c3<c1*10 for update; 1101 c1 c2 c3 1102 3 kelly 6 1103 7 results 20 1104 use select_for_update; 1105 truncate table su_07; 1106 select * from su_07; 1107 c1 c2 c3 1108 commit; 1109 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 1110 begin; 1111 select * from su_07 where c3>c1 for update; 1112 c1 c2 c3 1113 7 results 20 1114 1 plo 50 1115 3 kelly 60 1116 4 yellow 70 1117 use select_for_update; 1118 drop table su_07; 1119 select * from su_07; 1120 SQL parser error: table "su_07" does not exist 1121 commit;