github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/select_for_update.sql (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 -- pk , one rows lock, lock and unlock row update/delete/insert/truncate/alter/drop 4 begin; 5 select * from su_01 where c1=1 for update; 6 -- @session:id=1{ 7 use select_for_update; 8 select * from su_01 where c1=1; 9 update su_01 set c2='loo' where c1=2; 10 select * from su_01; 11 -- @session} 12 commit; 13 14 truncate table su_01; 15 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 16 17 begin; 18 select * from su_01 where c1=1 for update; 19 -- @session:id=1{ 20 use select_for_update; 21 select * from su_01 where c1=1; 22 insert into su_01 values(6,'polly',70); 23 select * from su_01; 24 -- @session} 25 commit; 26 27 truncate table su_01; 28 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 29 30 begin; 31 select * from su_01 where c1=1 for update; 32 -- @session:id=1{ 33 use select_for_update; 34 select * from su_01 where c1=1; 35 -- @wait:0:commit 36 delete from su_01 where c1=1; 37 select * from su_01; 38 -- @session} 39 commit; 40 41 insert into su_01 values(1,'results',20); 42 43 begin; 44 select * from su_01 where c1=1 for update; 45 -- @session:id=1{ 46 use select_for_update; 47 -- @wait:0:commit 48 truncate table su_01 ; 49 select * from su_01; 50 -- @session} 51 commit; 52 53 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 54 55 begin; 56 select * from su_01 where c1=1 for update; 57 -- @session:id=1{ 58 use select_for_update; 59 -- @wait:0:commit 60 alter table su_01 drop index u1; 61 select * from su_01; 62 show create table su_01; 63 -- @session} 64 commit; 65 66 begin; 67 select * from su_01 where c1=1 for update; 68 update su_01 set c2='desc' where c1=1; 69 update su_01 set c3=c3-1 where c1=1; 70 select * from su_01 where c1=1; 71 insert into su_01 values(5,'polly',80); 72 commit; 73 select * from su_01; 74 75 truncate table su_01; 76 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 77 78 begin; 79 select * from su_01 where c1=1 for update; 80 -- @session:id=1{ 81 use select_for_update; 82 -- @wait:0:commit 83 drop table su_01; 84 select * from su_01; 85 -- @session} 86 commit; 87 88 create table su_01(c1 int not null,c2 varchar(25),c3 int,primary key(c1),unique index u1(c2)); 89 insert into su_01 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 90 -- select pk not return data 91 begin; 92 select * from su_01 where c1=7 for update; 93 -- @session:id=1{ 94 use select_for_update; 95 select * from su_01; 96 delete from su_01 where c1=7; 97 -- @session} 98 commit; 99 100 begin; 101 select * from su_01 where c1=7 for update; 102 -- @session:id=1{ 103 use select_for_update; 104 select * from su_01; 105 update su_01 set c3=c3*10 where c1=1; 106 -- @session} 107 commit; 108 drop table su_01; 109 110 create table su_01_1(c1 int not null,c2 varchar(25),c3 int,primary key(c1)); 111 insert into su_01_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 112 -- pk , more rows lock,where pk and non pk 113 start transaction ; 114 select * from su_01_1 where c1>1 and c3<70 for update; 115 -- @session:id=1{ 116 use select_for_update; 117 update su_01_1 set c3=c3+10 where c3=70; 118 select * from su_01_1; 119 -- @session} 120 commit; 121 select * from su_01_1; 122 start transaction; 123 select * from su_01_1 where c1>1 and c3<70 for update; 124 -- @session:id=1{ 125 use select_for_update; 126 update su_01_1 set c3=c3-1 where c1=4; 127 select * from su_01_1; 128 -- @session} 129 rollback; 130 131 start transaction; 132 select * from su_01_1 where c1>1 and c3<70 for update; 133 -- @session:id=1{ 134 begin; 135 use select_for_update; 136 -- @wait:0:rollback 137 update su_01_1 set c3=101 where c1=3; 138 select * from su_01_1; 139 rollback ; 140 -- @session} 141 rollback; 142 143 start transaction ; 144 select * from su_01_1 where c1>1 and c3<70 for update; 145 -- @session:id=1{ 146 begin; 147 use select_for_update; 148 delete from su_01_1 where c1=1; 149 select * from su_01_1; 150 rollback ; 151 -- @session} 152 commit; 153 154 start transaction ; 155 select * from su_01_1 where c1>1 and c3<70 for update; 156 -- @session:id=1{ 157 use select_for_update; 158 -- @wait:0:rollback 159 delete from su_01_1 where c2="plo"; 160 select * from su_01_1; 161 -- @session} 162 rollback; 163 164 start transaction; 165 select * from su_01_1 where c1>1 and c3<70 for update; 166 -- @session:id=1{ 167 use select_for_update; 168 insert into su_01_1 values (10,'full',100); 169 select * from su_01_1; 170 -- @session} 171 rollback; 172 173 start transaction; 174 select * from su_01_1 where c1>1 and c3<70 for update; 175 -- @session:id=1{ 176 use select_for_update; 177 -- @wait:0:rollback 178 truncate table su_01_1; 179 select * from su_01_1; 180 -- @session} 181 rollback; 182 183 insert into su_01_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 184 185 start transaction; 186 select * from su_01_1 where c1>1 and c3<70 for update; 187 -- @session:id=1{ 188 use select_for_update; 189 -- @wait:0:rollback 190 drop table su_01_1; 191 select * from su_01_1; 192 -- @session} 193 rollback; 194 195 create table su_02(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 196 insert into su_02 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 197 -- unique index , more rows lock,lock and unlock row update/delete/insert/truncate/alter/drop 198 begin; 199 select * from su_02 where c3>35 for update; 200 -- @session:id=1{ 201 use select_for_update; 202 select * from su_02 where c3>35; 203 insert into su_02 values (8,'results',100); 204 select * from su_02; 205 -- @session} 206 commit; 207 208 begin; 209 select * from su_02 where c3>35 for update; 210 -- @session:id=1{ 211 use select_for_update; 212 select * from su_02 where c3>35; 213 -- @wait:0:commit 214 delete from su_02 where c3<60; 215 select * from su_02; 216 -- @session} 217 commit; 218 219 begin; 220 select * from su_02 where c3>35 for update; 221 -- @session:id=1{ 222 use select_for_update; 223 select * from su_02 where c3>35; 224 -- @wait:0:commit 225 update su_02 set c2='kitty' where c3=70; 226 select * from su_02; 227 -- @session} 228 commit; 229 230 begin; 231 select * from su_02 where c3>35 for update; 232 -- @session:id=1{ 233 use select_for_update; 234 -- @wait:0:commit 235 alter table su_02 drop index u1; 236 show create table su_02; 237 -- @session} 238 commit; 239 240 begin; 241 select * from su_02 where c3>35 for update; 242 update su_02 set c3=c3-1 where c3=60; 243 insert into su_02 values(5,'polly',80); 244 select * from su_02; 245 commit; 246 select * from su_02; 247 truncate table su_02; 248 select * from su_02; 249 250 insert into su_02 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 251 252 begin; 253 select * from su_02 where c3>35 for update; 254 -- @session:id=1{ 255 use select_for_update; 256 -- @wait:0:commit 257 drop table su_02; 258 -- @session} 259 commit; 260 261 drop table if exists su_02; 262 263 create table su_02_1(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 264 insert into su_02_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 265 -- unique index more rows lock 266 start transaction; 267 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 268 -- @session:id=1{ 269 use select_for_update; 270 update su_02_1 set c2='non' where c3=60; 271 select * from su_02_1; 272 -- @session} 273 commit; 274 275 start transaction; 276 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 277 -- @session:id=1{ 278 use select_for_update; 279 update su_02_1 set c3=c3+100 where c2='results'; 280 select * from su_02_1; 281 -- @session} 282 commit; 283 284 start transaction; 285 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 286 -- @session:id=1{ 287 use select_for_update; 288 -- @wait:0:commit 289 delete from su_02_1 where c3=50; 290 select * from su_02_1; 291 -- @session} 292 commit; 293 294 start transaction; 295 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 296 -- @session:id=1{ 297 use select_for_update; 298 -- @wait:0:commit 299 alter table su_02_1 drop index u1; 300 show create table su_02_1; 301 -- @session} 302 commit; 303 304 start transaction; 305 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 306 -- @session:id=1{ 307 use select_for_update; 308 -- @wait:0:commit 309 truncate table su_02_1; 310 select * from su_02_1; 311 -- @session} 312 commit; 313 314 insert into su_02_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 315 316 start transaction; 317 select * from su_02_1 where c3 between 25 and 85 and c2 !='kelly' for update; 318 -- @session:id=1{ 319 use select_for_update; 320 -- @wait:0:commit 321 drop table su_02_1; 322 select * from su_02_1; 323 -- @session} 324 commit; 325 326 create table su_02_2(c1 int not null,c2 varchar(25),c3 int,unique index u1(c3)); 327 insert into su_02_2 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 328 -- unique index one rows lock 329 begin; 330 select * from su_02_2 where c3>55 and c3<65 for update; 331 -- @session:id=1{ 332 use select_for_update; 333 -- @wait:0:commit 334 update su_02_2 set c1=c1+500 where c3=60; 335 select * from su_02_2; 336 -- @session} 337 commit; 338 339 begin; 340 select * from su_02_2 where c3>55 and c3<65 for update; 341 -- @session:id=1{ 342 use select_for_update; 343 update su_02_2 set c1=30 where c2 like 'yell%'; 344 select * from su_02_2; 345 -- @session} 346 commit; 347 348 create table su_03(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 349 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 350 -- secondary index , more rows lock, lock and unlock row update/delete/insert/truncate/alter/drop 351 begin; 352 select * from su_03 where c3 in(50,70) for update; 353 -- @session:id=1{ 354 use select_for_update; 355 select * from su_03 where c3>35; 356 insert into su_03 values (8,'results',100); 357 select * from su_03 ; 358 -- @session} 359 commit; 360 361 begin; 362 select * from su_03 where c3 in(50,70) for update; 363 -- @session:id=1{ 364 use select_for_update; 365 select * from su_03 where c3>35; 366 -- @wait:0:commit 367 update su_03 set c2='kitty' where c3=70; 368 select * from su_03; 369 -- @session} 370 commit; 371 372 begin; 373 select * from su_03 where c3 in(50,70) for update; 374 -- @session:id=1{ 375 use select_for_update; 376 -- @wait:0:commit 377 alter table su_03 drop index u1; 378 show create table su_03; 379 -- @session} 380 commit; 381 382 begin; 383 select * from su_03 where c3 in(50,70) for update; 384 -- @session:id=1{ 385 use select_for_update; 386 select * from su_03 where c3>35; 387 -- @wait:0:commit 388 delete from su_03 where c3 between 10 and 70; 389 select * from su_03; 390 -- @session} 391 commit; 392 393 truncate table su_03; 394 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 395 396 begin; 397 select * from su_03 where c3 in(50,70) for update; 398 update su_03 set c3=c3-1 where c3=70; 399 insert into su_03 values(5,'polly',80); 400 delete from su_03 where c3=50; 401 select * from su_03; 402 commit; 403 update su_03 set c3=c3-1 where c3=70; 404 select * from su_03; 405 406 truncate table su_03; 407 insert into su_03 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 408 409 begin; 410 select * from su_03 where c3 in(50,70) for update; 411 -- @session:id=1{ 412 use select_for_update; 413 -- @wait:0:commit 414 drop table su_03; 415 -- @session} 416 commit; 417 drop table if exists su_03; 418 419 -- select 0 rows, alter table drop index if wait lock 420 create table su_03(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 421 insert into su_03 values(1,'results',20); 422 begin; 423 select * from su_03 where c3 in(50,70) for update; 424 -- @session:id=1{ 425 use select_for_update; 426 -- @wait:0:commit 427 alter table su_03 drop index u1; 428 show create table su_03; 429 -- @session} 430 commit; 431 drop table if exists su_03; 432 433 create table su_03_1(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 434 insert into su_03_1 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 435 -- secondary index , one row lock 436 start transaction ; 437 select c1 from su_03_1 where c3 between 10 and 30 for update; 438 -- @session:id=1{ 439 use select_for_update; 440 -- @wait:0:commit 441 update su_03_1 set c2='high' where c2 like "res%"; 442 select * from su_03_1; 443 -- @session} 444 rollback; 445 446 start transaction ; 447 select c1 from su_03_1 where c3 between 10 and 30 for update; 448 -- @session:id=1{ 449 use select_for_update; 450 insert into su_03_1 values(10,'boo',120); 451 select * from su_03_1; 452 -- @session} 453 rollback; 454 455 start transaction ; 456 select c1 from su_03_1 where c3 between 10 and 30 for update; 457 -- @session:id=1{ 458 begin; 459 use select_for_update; 460 update su_03_1 set c2='high' where c3>=120; 461 select * from su_03_1; 462 rollback; 463 -- @session} 464 select * from su_03_1; 465 commit; 466 drop table su_03_1; 467 468 create table su_04(c1 int not null,c2 varchar(25),c3 int); 469 insert into su_04 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 470 -- unconstrained column, one rows lock 471 begin; 472 select * from su_04 where c2='kelly' for update; 473 -- @session:id=1{ 474 use select_for_update; 475 -- @wait:0:commit 476 update su_04 set c3=c3-1 where c2='kelly'; 477 select * from su_04; 478 -- @session} 479 update su_04 set c3=c3-1 where c2='kelly'; 480 insert into su_04 values (10,'mini',90); 481 select * from su_04; 482 commit; 483 484 begin; 485 select * from su_04 where c2 in ('kelly') for update; 486 -- @session:id=1{ 487 start transaction; 488 use select_for_update; 489 select * from su_04 where c2='kelly'; 490 -- @wait:0:commit 491 delete from su_04 where c2='kelly'; 492 select * from su_04; 493 commit; 494 -- @session} 495 select * from su_04; 496 commit; 497 -- add truncate case 498 begin; 499 select * from su_04 where c2='kelly' for update; 500 -- @session:id=1{ 501 use select_for_update; 502 delete from su_04 where c1=4; 503 select * from su_04; 504 -- @session} 505 update su_04 set c3=c3-1 where c2='kelly'; 506 select * from su_04; 507 commit; 508 509 begin; 510 select * from su_04 where c2='results' for update; 511 -- @session:id=1{ 512 use select_for_update; 513 -- @wait:0:commit 514 alter table su_04 add unique index a1(c1); 515 show create table su_04; 516 -- @session} 517 commit; 518 519 begin; 520 select * from su_04 where c2='results' for update; 521 insert into su_04 values(10,'tell',96); 522 update su_04 set c2='wed'; 523 delete from su_04 where c1=2; 524 commit; 525 526 truncate table su_04; 527 insert into su_04 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 528 529 begin; 530 select * from su_04 where c2='kelly' for update; 531 -- @session:id=1{ 532 use select_for_update; 533 -- @wait:0:commit 534 drop table su_04; 535 -- @session} 536 commit; 537 select * from su_04; 538 539 drop table if exists su_04; 540 541 create table su_05(c1 int not null primary key,c2 varchar(25),c3 decimal(6,2))partition by key(c1)partitions 4;; 542 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); 543 select * from `%!%p0%!%su_05`; 544 select * from `%!%p1%!%su_05`; 545 select * from `%!%p2%!%su_05`; 546 select * from `%!%p3%!%su_05`; 547 -- ddl partition, pk ,more rows lock 548 begin; 549 select * from su_05 where c1>3 for update; 550 -- @session:id=1{ 551 use select_for_update; 552 select * from su_05; 553 -- @wait:0:commit 554 update su_05 set c3=c3-1 where c1>2; 555 select * from su_05; 556 -- @session} 557 commit; 558 update su_05 set c3=c3-1 where c1>2; 559 select * from su_05; 560 561 begin; 562 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 563 -- @session:id=1{ 564 use select_for_update; 565 select * from su_05; 566 -- @wait:0:commit 567 delete from su_05 where c1=1; 568 select * from su_05; 569 -- @session} 570 commit; 571 begin; 572 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 573 -- @session:id=1{ 574 use select_for_update; 575 select * from su_05; 576 -- @wait:0:commit 577 truncate table su_05; 578 select * from su_05; 579 -- @session} 580 commit; 581 582 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); 583 584 begin; 585 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 586 -- @session:id=1{ 587 use select_for_update; 588 select * from su_05; 589 insert into su_05 values(9,'kol',89.01); 590 select * from su_05; 591 -- @session} 592 commit; 593 594 begin; 595 select * from su_05 where c1 in(1,3,6) and c2 !='io' for update; 596 -- @session:id=1{ 597 use select_for_update; 598 select * from su_05; 599 update su_05 set c2='polly' where c1=9; 600 select * from su_05; 601 -- @session} 602 commit; 603 drop table su_05; 604 -- @bvt:issue#11009 605 create table su_05_1(c1 int auto_increment primary key,c2 varchar(25),c3 decimal(6,2))partition by key(c1)partitions 4;; 606 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); 607 select * from `%!%p0%!%su_05_1`; 608 select * from `%!%p1%!%su_05_1`; 609 select * from `%!%p2%!%su_05_1`; 610 select * from `%!%p3%!%su_05_1`; 611 -- ddl partition, pk ,one rows lock 612 set autocommit=0; 613 select * from su_05_1 where c1=4 for update; 614 -- @session:id=1{ 615 use select_for_update; 616 update su_05_1 set c2='polly' where c3=9.0; 617 select * from su_05_1; 618 -- @session} 619 commit; 620 621 select * from su_05_1 where c1=4 for update; 622 -- @session:id=1{ 623 use select_for_update; 624 update su_05_1 set c3=c3-0.09 where c1=4; 625 select * from su_05_1; 626 -- @session} 627 commit; 628 629 select * from su_05_1 where c1=4 for update; 630 -- @session:id=1{ 631 use select_for_update; 632 insert into su_05_1(c2,c3) values('xin',8.90); 633 select * from su_05_1; 634 -- @session} 635 commit; 636 637 select * from su_05_1 where c1=3 for update; 638 -- @session:id=1{ 639 use select_for_update; 640 alter table su_05_1 add unique index s1(c3); 641 show create table su_05_1; 642 -- @session} 643 commit; 644 645 select * from su_05_1 where c1=3 for update; 646 -- @session:id=1{ 647 use select_for_update; 648 -- @wait:0:commit 649 truncate table su_05_1; 650 select * from su_05_1; 651 -- @session} 652 commit; 653 654 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); 655 656 select * from su_05_1 where c1=3 for update; 657 -- @session:id=1{ 658 use select_for_update; 659 -- @wait:0:commit 660 drop table su_05_1; 661 select * from su_05_1; 662 -- @session} 663 commit; 664 -- @bvt:issue 665 set autocommit=1; 666 667 create table su_06(c1 int not null,c2 varchar(25),c3 int,primary key(c1),key u1(c3)); 668 insert into su_06 values(1,'results',20),(2,'plo',50),(3,'kelly',60),(4,'yellow',70); 669 start transaction; 670 select * from su_06 where c1>=2 for update; 671 -- @session:id=1{ 672 use select_for_update; 673 prepare stmt1 from 'update su_06 set c3=c3+1.09 where c1=?'; 674 set @var = 2; 675 -- @wait:0:commit 676 execute stmt1 using @var; 677 select * from su_06; 678 -- @session} 679 commit; 680 681 start transaction; 682 select * from su_06 where c1>=2 for update; 683 -- @session:id=1{ 684 use select_for_update; 685 prepare stmt1 from 'update su_06 set c3=c3+1.09 where c1=?'; 686 set @var = 1; 687 execute stmt1 using @var; 688 select * from su_06; 689 -- @session} 690 commit; 691 692 start transaction; 693 select * from su_06 where c1>=2 for update; 694 -- @session:id=1{ 695 use select_for_update; 696 prepare stmt1 from 'delete from su_06 where c3 in (?)'; 697 set @var = 3; 698 execute stmt1 using @var; 699 select * from su_06; 700 -- @session} 701 commit; 702 703 create table su_07(c1 int not null,c2 varchar(25),c3 int,primary key(c1),unique index u1(c2)); 704 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 705 -- pk ,more rows lock, expression 706 begin; 707 select * from su_07 where c1+2>=5 for update; 708 -- @session:id=1{ 709 use select_for_update; 710 select * from su_07 where c1=1; 711 -- @wait:0:commit 712 update su_07 set c2='loo' where c1=4; 713 select * from su_07; 714 -- @session} 715 commit; 716 717 begin; 718 select * from su_07 where c1+2>=5 for update; 719 -- @session:id=1{ 720 use select_for_update; 721 select * from su_07 where c1=1; 722 update su_07 set c2='cool' where c1=1; 723 select * from su_07; 724 -- @session} 725 commit; 726 727 begin; 728 select * from su_07 where c3-c1>20 for update; 729 -- @session:id=1{ 730 use select_for_update; 731 -- @wait:0:commit 732 delete from su_07 where c1=1; 733 select * from su_07; 734 -- @session} 735 commit; 736 737 truncate table su_07; 738 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 739 740 begin; 741 select * from su_07 where c3/c1=20 for update; 742 -- @session:id=1{ 743 use select_for_update; 744 -- @wait:0:commit 745 update su_07 set c3=c3/10 where c1=3; 746 select * from su_07; 747 -- @session} 748 commit; 749 750 begin; 751 select * from su_07 where c3<c1*10 for update; 752 -- @session:id=1{ 753 use select_for_update; 754 -- @wait:0:commit 755 alter table su_07 drop index u1; 756 select * from su_07; 757 -- @session} 758 commit; 759 760 begin; 761 select * from su_07 where c3<c1*10 for update; 762 -- @session:id=1{ 763 use select_for_update; 764 -- @wait:0:commit 765 truncate table su_07; 766 select * from su_07; 767 -- @session} 768 commit; 769 770 insert into su_07 values(7,'results',20),(1,'plo',50),(3,'kelly',60),(4,'yellow',70); 771 772 begin; 773 select * from su_07 where c3>c1 for update; 774 -- @session:id=1{ 775 use select_for_update; 776 -- @wait:0:commit 777 drop table su_07; 778 select * from su_07; 779 -- @session} 780 commit;