1SET DEBUG_SYNC= 'RESET'; 2drop table if exists t1,t2,t3; 3create table t1 (i int); 4create table t2 (i int); 5connection: default 6lock tables t2 read; 7connection: con1 8set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; 9alter table t1 rename t3; 10connection: default 11set debug_sync= 'now WAIT_FOR parked'; 12connection: con2 13set debug_sync='mdl_acquire_lock_wait SIGNAL go'; 14drop table t1,t2; 15connection: con1 16connection: default 17unlock tables; 18connection: con2 19ERROR 42S02: Unknown table 'test.t1' 20drop table t3; 21SET DEBUG_SYNC= 'RESET'; 22# 23# Basic test coverage for type-of-operation aware metadata locks. 24# 25drop table if exists t1, t2, t3; 26set debug_sync= 'RESET'; 27create table t1 (c1 int); 28# 29# A) First let us check compatibility rules between differend kinds of 30# type-of-operation aware metadata locks. 31# Of course, these rules are already covered by the tests scattered 32# across the test suite. But it still makes sense to have one place 33# which covers all of them. 34# 35# 1) Acquire S (simple shared) lock on the table (by using HANDLER): 36# 37handler t1 open; 38# 39# Switching to connection 'mdl_con1'. 40# Check that S, SH, SR, SW and SWLP locks are compatible with it. 41handler t1 open t; 42handler t close; 43select column_name from information_schema.columns where 44table_schema='test' and table_name='t1'; 45column_name 46c1 47select count(*) from t1; 48count(*) 490 50insert into t1 values (1); 51insert low_priority into t1 values (1); 52# Check that SU lock is compatible with it. To do this use ALTER TABLE 53# which will fail when constructing .frm and thus obtaining SU metadata 54# lock. 55alter table t1 add index (not_exist); 56ERROR 42000: Key column 'not_exist' doesn't exist in table 57# Check that SRO lock is compatible with S lock. 58lock table t1 read; 59select count(*) from t1; 60count(*) 612 62unlock tables; 63# Check that SNW lock is compatible with it. To do this use ALTER TABLE 64# which will fail during copying the table and thus obtaining SNW metadata 65# lock. 66alter table t1 add primary key (c1); 67ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 68# Check that SNRW lock is compatible with S lock. 69lock table t1 write; 70insert into t1 values (1); 71unlock tables; 72# Check that X lock is incompatible with S lock. 73# Sending: 74rename table t1 to t2;; 75# 76# Switching to connection 'mdl_con2'. 77# Check that the above RENAME is blocked because of S lock. 78# 79# Switching to connection 'default'. 80# Unblock RENAME TABLE. 81handler t1 close; 82# 83# Switching to connection 'mdl_con1'. 84# Reaping RENAME TABLE. 85# Restore the original state of the things. 86rename table t2 to t1; 87# 88# Switching to connection 'default'. 89handler t1 open; 90# 91# Switching to connection 'mdl_con1'. 92# Check that upgrade from SNW to X is blocked by presence of S lock. 93# Sending: 94alter table t1 add column c2 int;; 95# 96# Switching to connection 'mdl_con2'. 97# Check that the above ALTER TABLE is blocked because of S lock. 98# 99# Switching to connection 'default'. 100# Unblock ALTER TABLE. 101handler t1 close; 102# 103# Switching to connection 'mdl_con1'. 104# Reaping ALTER TABLE. 105# Restore the original state of the things. 106alter table t1 drop column c2; 107# 108# Switching to connection 'default'. 109handler t1 open; 110# 111# Switching to connection 'mdl_con1'. 112# Check that upgrade from SNRW to X is blocked by presence of S lock. 113lock table t1 write; 114# Sending: 115alter table t1 add column c2 int;; 116# 117# Switching to connection 'mdl_con2'. 118# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked 119# because of S lock. 120# 121# Switching to connection 'default'. 122# Unblock ALTER TABLE. 123handler t1 close; 124# 125# Switching to connection 'mdl_con1'. 126# Reaping ALTER TABLE. 127# Restore the original state of the things. 128alter table t1 drop column c2; 129unlock tables; 130# 131# Switching to connection 'default'. 132# 133# 2) Acquire SH (shared high-priority) lock on the table. 134# We have to involve DEBUG_SYNC facility for this as usually 135# such kind of locks are short-lived. 136# 137set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 138# Sending: 139select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 140# 141# Switching to connection 'mdl_con1'. 142set debug_sync= 'now WAIT_FOR locked'; 143# Check that S, SH, SR, SW and SWLP locks are compatible with it. 144handler t1 open; 145handler t1 close; 146select column_name from information_schema.columns where 147table_schema='test' and table_name='t1'; 148column_name 149c1 150select count(*) from t1; 151count(*) 1523 153insert into t1 values (1), (1); 154delete low_priority from t1 limit 1; 155# Check that SU lock is compatible with it. To do this use ALTER TABLE 156# which will fail when constructing .frm and thus obtaining SU metadata 157# lock. 158alter table t1 add index (not_exist); 159ERROR 42000: Key column 'not_exist' doesn't exist in table 160# Check that SRO lock is compatible with SH lock. 161lock table t1 read; 162select count(*) from t1; 163count(*) 1644 165unlock tables; 166# Check that SNW lock is compatible with it. To do this use ALTER TABLE 167# which will fail during copying the table and thus obtaining SNW metadata 168# lock. 169alter table t1 add primary key (c1); 170ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 171# Check that SNRW lock is compatible with SH lock. 172lock table t1 write; 173delete from t1 limit 1; 174unlock tables; 175# Check that X lock is incompatible with SH lock. 176# Sending: 177rename table t1 to t2;; 178# 179# Switching to connection 'mdl_con2'. 180# Check that the above RENAME is blocked because of SH lock. 181# Unblock RENAME TABLE. 182set debug_sync= 'now SIGNAL finish'; 183# 184# Switching to connection 'default'. 185# Reaping SELECT ... FROM I_S. 186table_name table_type auto_increment table_comment 187t1 BASE TABLE NULL 188# 189# Switching to connection 'mdl_con1'. 190# Reaping RENAME TABLE. 191# Restore the original state of the things. 192rename table t2 to t1; 193# 194# Switching to connection 'default'. 195set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 196# Sending: 197select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 198# 199# Switching to connection 'mdl_con1'. 200set debug_sync= 'now WAIT_FOR locked'; 201# Check that upgrade from SNW to X is blocked by presence of SH lock. 202# Sending: 203alter table t1 add column c2 int;; 204# 205# Switching to connection 'mdl_con2'. 206# Check that the above ALTER TABLE is blocked because of SH lock. 207# Unblock RENAME TABLE. 208set debug_sync= 'now SIGNAL finish'; 209# 210# Switching to connection 'default'. 211# Reaping SELECT ... FROM I_S. 212table_name table_type auto_increment table_comment 213t1 BASE TABLE NULL 214# 215# Switching to connection 'mdl_con1'. 216# Reaping ALTER TABLE. 217# Restore the original state of the things. 218alter table t1 drop column c2; 219# 220# Switching to connection 'default'. 221set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 222select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 223# 224# Switching to connection 'mdl_con1'. 225set debug_sync= 'now WAIT_FOR locked'; 226# Check that upgrade from SNRW to X is blocked by presence of S lock. 227lock table t1 write; 228# Sending: 229alter table t1 add column c2 int;; 230# 231# Switching to connection 'mdl_con2'. 232# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked 233# because of S lock. 234# Unblock RENAME TABLE. 235set debug_sync= 'now SIGNAL finish'; 236# 237# Switching to connection 'default'. 238# Reaping SELECT ... FROM I_S. 239table_name table_type auto_increment table_comment 240t1 BASE TABLE NULL 241# 242# Switching to connection 'mdl_con1'. 243# Reaping ALTER TABLE. 244# Restore the original state of the things. 245alter table t1 drop column c2; 246unlock tables; 247# 248# Switching to connection 'default'. 249# 250# 251# 3) Acquire SR lock on the table. 252# 253# 254begin; 255select count(*) from t1; 256count(*) 2573 258# 259# Switching to connection 'mdl_con1'. 260# Check that S, SH, SR, SW and SWLP locks are compatible with it. 261handler t1 open; 262handler t1 close; 263select column_name from information_schema.columns where 264table_schema='test' and table_name='t1'; 265column_name 266c1 267select count(*) from t1; 268count(*) 2693 270insert into t1 values (1), (1); 271delete low_priority from t1 limit 1; 272# Check that SU lock is compatible with it. To do this use ALTER TABLE 273# which will fail when constructing .frm and thus obtaining SU metadata 274# lock. 275alter table t1 add index (not_exist); 276ERROR 42000: Key column 'not_exist' doesn't exist in table 277# Check that SRO lock is compatible with SR lock. 278lock table t1 read; 279select count(*) from t1; 280count(*) 2814 282unlock tables; 283# Check that SNW lock is compatible with it. To do this use ALTER TABLE 284# which will fail during copying the table and thus obtaining SNW metadata 285# lock. 286alter table t1 add primary key (c1); 287ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 288# Check that SNRW lock is not compatible with SR lock. 289# Sending: 290lock table t1 write;; 291# 292# Switching to connection 'default'. 293# Check that the above LOCK TABLES is blocked because of SR lock. 294# Unblock LOCK TABLES. 295commit; 296# 297# Switching to connection 'mdl_con1'. 298# Reaping LOCK TABLES. 299delete from t1 limit 1; 300unlock tables; 301# 302# Switching to connection 'default'. 303begin; 304select count(*) from t1; 305count(*) 3063 307# 308# Switching to connection 'mdl_con1'. 309# Check that X lock is incompatible with SR lock. 310# Sending: 311rename table t1 to t2;; 312# 313# Switching to connection 'mdl_con2'. 314# Check that the above RENAME is blocked because of SR lock. 315# 316# Switching to connection 'default'. 317# Unblock RENAME TABLE. 318commit; 319# 320# Switching to connection 'mdl_con1'. 321# Reaping RENAME TABLE. 322# Restore the original state of the things. 323rename table t2 to t1; 324# 325# Switching to connection 'default'. 326begin; 327select count(*) from t1; 328count(*) 3293 330# 331# Switching to connection 'mdl_con1'. 332# Check that upgrade from SNW to X is blocked by presence of SR lock. 333# Sending: 334alter table t1 add column c2 int;; 335# 336# Switching to connection 'mdl_con2'. 337# Check that the above ALTER TABLE is blocked because of SR lock. 338# 339# Switching to connection 'default'. 340# Unblock ALTER TABLE. 341commit; 342# 343# Switching to connection 'mdl_con1'. 344# Reaping ALTER TABLE. 345# Restore the original state of the things. 346alter table t1 drop column c2; 347# 348# There is no need to check that upgrade from SNRW to X is blocked 349# by presence of SR lock because SNRW is incompatible with SR anyway. 350# 351# 352# Switching to connection 'default'. 353# 354# 355# 4) Acquire SW lock on the table. 356# 357# 358begin; 359insert into t1 values (1); 360# 361# Switching to connection 'mdl_con1'. 362# Check that S, SH, SR, SW and SWLP locks are compatible with it. 363handler t1 open; 364handler t1 close; 365select column_name from information_schema.columns where 366table_schema='test' and table_name='t1'; 367column_name 368c1 369# Disable result log to make test robust against 370# effects of concurrent insert. 371select * from t1; 372insert into t1 values (1); 373delete low_priority from t1 limit 1; 374# Check that SU lock is compatible with it. To do this use ALTER TABLE 375# which will fail when constructing .frm and thus obtaining SU metadata 376# lock. 377alter table t1 add index (not_exist); 378ERROR 42000: Key column 'not_exist' doesn't exist in table 379# Check that SRO lock is not compatible with SW lock; 380# Sending: 381lock table t1 read;; 382# Switching to connection 'default'. 383# Check that the above LOCK TABLE READ is blocked because of SW lock. 384# Unblock LOCK TABLE READ. 385commit; 386# 387# Switching to connection 'mdl_con1'. 388# Reaping LOCK TABLE READ. 389unlock tables; 390# 391# Switching to connection 'default'. 392begin; 393insert into t1 values (1); 394# 395# Switching to connection 'mdl_con1'. 396# Check that SNW lock is not compatible with SW lock. 397# Again we use ALTER TABLE which fails during copying 398# the table to avoid upgrade of SNW -> X. 399# Sending: 400alter table t1 add primary key (c1);; 401# 402# Switching to connection 'default'. 403# Check that the above ALTER TABLE is blocked because of SW lock. 404# Unblock ALTER TABLE. 405commit; 406# 407# Switching to connection 'mdl_con1'. 408# Reaping ALTER TABLE. 409ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 410# 411# Switching to connection 'default'. 412begin; 413insert into t1 values (1); 414# 415# Switching to connection 'mdl_con1'. 416# Check that SNRW lock is not compatible with SW lock. 417# Sending: 418lock table t1 write;; 419# 420# Switching to connection 'default'. 421# Check that the above LOCK TABLES is blocked because of SW lock. 422# Unblock LOCK TABLES. 423commit; 424# 425# Switching to connection 'mdl_con1'. 426# Reaping LOCK TABLES. 427delete from t1 limit 2; 428unlock tables; 429# 430# Switching to connection 'default'. 431begin; 432insert into t1 values (1); 433# 434# Switching to connection 'mdl_con1'. 435# Check that X lock is incompatible with SW lock. 436# Sending: 437rename table t1 to t2;; 438# 439# Switching to connection 'mdl_con2'. 440# Check that the above RENAME is blocked because of SW lock. 441# 442# Switching to connection 'default'. 443# Unblock RENAME TABLE. 444commit; 445# 446# Switching to connection 'mdl_con1'. 447# Reaping RENAME TABLE. 448# Restore the original state of the things. 449rename table t2 to t1; 450# 451# There is no need to check that upgrade from SNW/SNRW to X is 452# blocked by presence of SW lock because SNW/SNRW is incompatible 453# with SW anyway. 454# 455# 456# Switching to connection 'default'. 457# 458# 459# 5) Acquire SWLP lock on the table. 460# 461# 462begin; 463insert low_priority into t1 values (1), (1); 464# 465# Switching to connection 'mdl_con1'. 466# Check that S, SH, SR, SW and SWLP locks are compatible with it. 467handler t1 open; 468handler t1 close; 469select column_name from information_schema.columns where 470table_schema='test' and table_name='t1'; 471column_name 472c1 473# Disable result log to make test robust against 474# effects of concurrent insert. 475select * from t1; 476delete from t1 limit 1; 477delete low_priority from t1 limit 1; 478# Check that SU lock is compatible with it. To do this use ALTER TABLE 479# which will fail when constructing .frm and thus obtaining SU metadata 480# lock. 481alter table t1 add index (not_exist); 482ERROR 42000: Key column 'not_exist' doesn't exist in table 483# Check that SRO lock is not compatible with SWLP lock; 484# Sending: 485lock table t1 read;; 486# Switching to connection 'default'. 487# Check that the above LOCK TABLE READ is blocked because of SWLP lock. 488# Unblock LOCK TABLE READ. 489commit; 490# 491# Switching to connection 'mdl_con1'. 492# Reaping LOCK TABLE READ. 493unlock tables; 494# 495# Switching to connection 'default'. 496begin; 497insert low_priority into t1 values (1); 498# 499# Switching to connection 'mdl_con1'. 500# Check that SNW lock is not compatible with SWLP lock. 501# Again we use ALTER TABLE which fails during copying 502# the table to avoid upgrade of SNW -> X. 503# Sending: 504alter table t1 add primary key (c1);; 505# 506# Switching to connection 'default'. 507# Check that the above ALTER TABLE is blocked because of SWLP lock. 508# Unblock ALTER TABLE. 509commit; 510# 511# Switching to connection 'mdl_con1'. 512# Reaping ALTER TABLE. 513ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 514# 515# Switching to connection 'default'. 516begin; 517delete low_priority from t1 limit 2; 518# 519# Switching to connection 'mdl_con1'. 520# Check that SNRW lock is not compatible with SWLP lock. 521# Sending: 522lock table t1 write;; 523# 524# Switching to connection 'default'. 525# Check that the above LOCK TABLES is blocked because of SWLP lock. 526# Unblock LOCK TABLES. 527commit; 528# 529# Switching to connection 'mdl_con1'. 530# Reaping LOCK TABLES. 531unlock tables; 532# 533# Switching to connection 'default'. 534begin; 535insert low_priority into t1 values (1); 536# 537# Switching to connection 'mdl_con1'. 538# Check that X lock is incompatible with SWLP lock. 539# Sending: 540rename table t1 to t2;; 541# 542# Switching to connection 'mdl_con2'. 543# Check that the above RENAME is blocked because of SW lock. 544# 545# Switching to connection 'default'. 546# Unblock RENAME TABLE. 547commit; 548# 549# Switching to connection 'mdl_con1'. 550# Reaping RENAME TABLE. 551# Restore the original state of the things. 552rename table t2 to t1; 553# 554# There is no need to check that upgrade from SNW/SNRW to X is 555# blocked by presence of SWLP lock because SNW/SNRW is incompatible 556# with SWLP anyway. 557# 558# 559# Switching to connection 'default'. 560# 561# 562# 6) Acquire SU lock on the table. We have to use DEBUG_SYNC for 563# this, to prevent SU from being immediately upgraded to X. 564# 565set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 566# Sending: 567alter table t1 add primary key (c1);; 568# 569# Switching to connection 'mdl_con1'. 570set debug_sync= 'now WAIT_FOR locked'; 571# Check that S, SH, SR, SW and SWLP locks are compatible with it. 572handler t1 open; 573handler t1 close; 574select column_name from information_schema.columns where 575table_schema='test' and table_name='t1'; 576column_name 577c1 578select count(*) from t1; 579count(*) 5805 581insert into t1 values (1); 582delete low_priority from t1 limit 2; 583# Check that SU lock is incompatible with SU lock. 584# Sending: 585alter table t1 add primary key (c1);; 586# 587# Switching to connection 'mdl_con2'. 588# Check that the above ALTER is blocked because of SU lock. 589# Unblock ALTERs. 590set debug_sync= 'now SIGNAL finish'; 591# 592# Switching to connection 'default'. 593# Reaping first ALTER TABLE. 594ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 595# 596# Switching to connection 'mdl_con1'. 597# Reaping another ALTER TABLE. 598ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 599# 600# Switching to connection 'default'. 601set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 602# Sending: 603alter table t1 add primary key (c1);; 604# 605# Switching to connection 'mdl_con1'. 606set debug_sync= 'now WAIT_FOR locked'; 607# Check that SRO lock is compatible with SU lock. 608lock tables t1 read; 609unlock tables; 610# Check that SNRW lock is incompatible with SU lock. 611# Sending: 612lock table t1 write;; 613# 614# Switching to connection 'mdl_con2'. 615# Check that the above LOCK TABLES is blocked because of SU lock. 616# Unblock ALTER and thus LOCK TABLES. 617set debug_sync= 'now SIGNAL finish'; 618# 619# Switching to connection 'default'. 620# Reaping ALTER TABLE. 621ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 622# 623# Switching to connection 'mdl_con1'. 624# Reaping LOCK TABLES 625insert into t1 values (1); 626unlock tables; 627# 628# Switching to connection 'default'. 629set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 630# Sending: 631alter table t1 add primary key (c1);; 632# 633# Switching to connection 'mdl_con1'. 634set debug_sync= 'now WAIT_FOR locked'; 635# Check that X lock is incompatible with SU lock. 636# Sending: 637rename table t1 to t2;; 638# 639# Switching to connection 'mdl_con2'. 640# Check that the above RENAME is blocked because of SU lock. 641# Unblock ALTER and thus RENAME TABLE. 642set debug_sync= 'now SIGNAL finish'; 643# 644# Switching to connection 'default'. 645# Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending 646# X-lock. In this case ALTER TABLE should be chosen as victim. 647# Reaping ALTER TABLE. 648ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 649# 650# Switching to connection 'mdl_con1'. 651# Reaping RENAME TABLE 652# Revert back to original state of things. 653rename table t2 to t1; 654# 655# There is no need to check that upgrade from SNW/SNRW to X is 656# blocked by presence of another SU lock because SNW/SNRW is 657# incompatible with SU anyway. 658# 659# Switching to connection 'default'. 660# 661# 662# 7) Acquire SRO lock on the table. 663# 664# 665lock table t1 read; 666# 667# Switching to connection 'mdl_con1'. 668# Check that S, SH and SR locks are compatible with it. 669handler t1 open; 670handler t1 close; 671select column_name from information_schema.columns where 672table_schema='test' and table_name='t1'; 673column_name 674c1 675select count(*) from t1; 676count(*) 6775 678# Check that SW lock is incompatible with SRO lock. 679# Sending: 680delete from t1 limit 2;; 681# 682# Switching to connection 'default'. 683# Check that the above DELETE is blocked because of SRO lock. 684# Unblock DELETE. 685unlock tables; 686# 687# Switching to connection 'mdl_con1'. 688# Reaping DELETE. 689# 690# Switching to connection 'default'. 691lock table t1 read; 692# 693# Switching to connection 'mdl_con1'. 694# Check that SWLP lock is incompatible with SRO lock. 695# Sending: 696insert low_priority into t1 values (1);; 697# 698# Switching to connection 'default'. 699# Check that the above INSERT is blocked because of SRO lock. 700# Unblock INSERT. 701unlock tables; 702# 703# Switching to connection 'mdl_con1'. 704# Reaping INSERT. 705# 706# Switching to connection 'default'. 707lock table t1 read; 708# 709# Switching to connection 'mdl_con1'. 710# Check that SU lock is compatible with SRO lock. 711alter table t1 add index (not_exist); 712ERROR 42000: Key column 'not_exist' doesn't exist in table 713# Check that SRO lock is compatible with SRO lock. 714lock table t1 read; 715unlock tables; 716# Check that SNW lock is compatible with SRO lock. 717# Again we use ALTER TABLE which fails during copying 718# the table to avoid upgrade of SNW -> X. 719alter table t1 add primary key (c1); 720ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 721# Check that SNRW lock is incompatible with SRO lock. 722# Sending: 723lock table t1 write;; 724# 725# Switching to connection 'default'. 726# Check that the above LOCK TABLES is blocked because of SRO lock. 727# Unblock waiting LOCK TABLES. 728unlock tables; 729# 730# Switching to connection 'mdl_con1'. 731# Reaping LOCK TABLES 732insert into t1 values (1); 733unlock tables; 734# 735# Switching to connection 'default'. 736lock table t1 read; 737# 738# Switching to connection 'mdl_con1'. 739# Check that X lock is incompatible with SRO lock. 740# Sending: 741rename table t1 to t2;; 742# 743# Switching to connection 'default'. 744# Check that the above RENAME is blocked because of SRO lock. 745# Unblock RENAME TABLE 746unlock tables; 747# 748# Switching to connection 'mdl_con1'. 749# Reaping RENAME TABLE 750# Revert back to original state of things. 751rename table t2 to t1; 752# 753# Switching to connection 'default'. 754lock table t1 read; 755# 756# Switching to connection 'mdl_con1'. 757# Check that upgrade from SNW to X is blocked by presence of SRO lock. 758# Sending: 759alter table t1 add column c2 int;; 760# 761# Switching to connection 'mdl_con2'. 762# Check that the above ALTER TABLE is blocked because of SRO lock. 763# 764# Switching to connection 'default'. 765# Unblock ALTER TABLE. 766unlock tables; 767# 768# Switching to connection 'mdl_con1'. 769# Reaping ALTER TABLE. 770# Restore the original state of the things. 771alter table t1 drop column c2; 772# 773# There is no need to check that upgrade from SNRW to X is 774# blocked by presence of SRO lock because SNRW is incompatible 775# with SRO anyway. 776# 777# Switching to connection 'default'. 778# 779# 780# 8) Acquire SNW lock on the table. We have to use DEBUG_SYNC for 781# this, to prevent SNW from being immediately upgraded to X. 782# 783set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 784# Sending: 785alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 786# 787# Switching to connection 'mdl_con1'. 788set debug_sync= 'now WAIT_FOR locked'; 789# Check that S, SH and SR locks are compatible with it. 790handler t1 open; 791handler t1 close; 792select column_name from information_schema.columns where 793table_schema='test' and table_name='t1'; 794column_name 795c1 796select count(*) from t1; 797count(*) 7985 799# Check that SW lock is incompatible with SNW lock. 800# Sending: 801delete from t1 limit 1;; 802# 803# Switching to connection 'mdl_con2'. 804# Check that the above DELETE is blocked because of SNW lock. 805# Unblock ALTER and thus DELETE. 806set debug_sync= 'now SIGNAL finish'; 807# 808# Switching to connection 'default'. 809# Reaping ALTER TABLE. 810ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 811# 812# Switching to connection 'mdl_con1'. 813# Reaping DELETE. 814# 815# Switching to connection 'default'. 816set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 817# Sending: 818alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 819# 820# Switching to connection 'mdl_con1'. 821set debug_sync= 'now WAIT_FOR locked'; 822# Check that SWLP lock is incompatible with SNW lock. 823# Sending: 824delete low_priority from t1 limit 1;; 825# 826# Switching to connection 'mdl_con2'. 827# Check that the above DELETE is blocked because of SNW lock. 828# Unblock ALTER. 829set debug_sync= 'now SIGNAL finish'; 830# 831# Switching to connection 'default'. 832# Reaping ALTER TABLE. 833ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 834# 835# Switching to connection 'mdl_con1'. 836# Reaping DELETE. 837# 838# Switching to connection 'default'. 839set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 840# Sending: 841alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 842# 843# Switching to connection 'mdl_con1'. 844set debug_sync= 'now WAIT_FOR locked'; 845# Check that SU lock is incompatible with SNW lock. 846# Sending: 847alter table t1 add primary key (c1);; 848# 849# Switching to connection 'mdl_con2'. 850# Check that the above ALTER is blocked because of SNW lock. 851# Unblock ALTERs. 852set debug_sync= 'now SIGNAL finish'; 853# 854# Switching to connection 'default'. 855# Reaping first ALTER TABLE. 856ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 857# 858# Switching to connection 'mdl_con1'. 859# Reaping another ALTER TABLE. 860ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 861# Switching to connection 'default'. 862set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 863# Sending: 864alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 865# 866# Switching to connection 'mdl_con1'. 867set debug_sync= 'now WAIT_FOR locked'; 868# Check that SRO lock is compatible with SNW lock. 869lock tables t1 read; 870unlock tables; 871# 872# Note that we can't easily check SNW vs SNW locks since 873# SNW is only used by ALTER TABLE after upgrading from SU 874# and SU is also incompatible with SNW. 875# 876# Check that SNRW lock is incompatible with SNW lock. 877# Sending: 878lock table t1 write;; 879# 880# Switching to connection 'mdl_con2'. 881# Check that the above LOCK TABLES is blocked because of SNW lock. 882# Unblock ALTER and thus LOCK TABLES. 883set debug_sync= 'now SIGNAL finish'; 884# 885# Switching to connection 'default'. 886# Reaping ALTER TABLE. 887ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 888# 889# Switching to connection 'mdl_con1'. 890# Reaping LOCK TABLES 891insert into t1 values (1); 892unlock tables; 893# 894# Switching to connection 'default'. 895set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 896# Sending: 897alter table t1 add primary key (c1), algorithm=copy, lock=shared;; 898# 899# Switching to connection 'mdl_con1'. 900set debug_sync= 'now WAIT_FOR locked'; 901# Check that X lock is incompatible with SNW lock. 902# Sending: 903rename table t1 to t2;; 904# 905# Switching to connection 'mdl_con2'. 906# Check that the above RENAME is blocked because of SNW lock. 907# Unblock ALTER and thus RENAME TABLE. 908set debug_sync= 'now SIGNAL finish'; 909# 910# Switching to connection 'default'. 911# Reaping ALTER TABLE. 912ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 913# 914# Switching to connection 'mdl_con1'. 915# Reaping RENAME TABLE 916# Revert back to original state of things. 917rename table t2 to t1; 918# 919# There is no need to check that upgrade from SNW/SNRW to X is 920# blocked by presence of another SNW lock because SNW/SNRW is 921# incompatible with SNW anyway. 922# 923# Switching to connection 'default'. 924# 925# 926# 9) Acquire SNRW lock on the table. 927# 928# 929lock table t1 write; 930# 931# Switching to connection 'mdl_con1'. 932# Check that S and SH locks are compatible with it. 933handler t1 open; 934handler t1 close; 935select column_name from information_schema.columns where 936table_schema='test' and table_name='t1'; 937column_name 938c1 939# Check that SR lock is incompatible with SNRW lock. 940# Sending: 941select count(*) from t1;; 942# 943# Switching to connection 'default'. 944# Check that the above SELECT is blocked because of SNRW lock. 945# Unblock SELECT. 946unlock tables; 947# 948# Switching to connection 'mdl_con1'. 949# Reaping SELECT. 950count(*) 9514 952# 953# Switching to connection 'default'. 954lock table t1 write; 955# 956# Switching to connection 'mdl_con1'. 957# Check that SW lock is incompatible with SNRW lock. 958# Sending: 959delete from t1 limit 2;; 960# 961# Switching to connection 'default'. 962# Check that the above DELETE is blocked because of SNRW lock. 963# Unblock DELETE. 964unlock tables; 965# 966# Switching to connection 'mdl_con1'. 967# Reaping DELETE. 968# 969# Switching to connection 'default'. 970lock table t1 write; 971# 972# Switching to connection 'mdl_con1'. 973# Check that SWLP lock is incompatible with SNRW lock. 974# Sending: 975insert low_priority into t1 values (1);; 976# 977# Switching to connection 'default'. 978# Check that the above INSERT is blocked because of SNRW lock. 979# Unblock INSERT. 980unlock tables; 981# 982# Switching to connection 'mdl_con1'. 983# Reaping INSERT. 984# 985# Switching to connection 'default'. 986lock table t1 write; 987# 988# Switching to connection 'mdl_con1'. 989# Check that SU lock is incompatible with SNRW lock. 990# Sending: 991alter table t1 add primary key (c1);; 992# 993# Switching to connection 'default'. 994# Check that the above ALTER is blocked because of SNRW lock. 995# Unblock ALTER. 996unlock tables; 997# 998# Switching to connection 'mdl_con1'. 999# Reaping ALTER TABLE. 1000ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1001# 1002# Switching to connection 'default'. 1003lock table t1 write; 1004# 1005# Switching to connection 'mdl_con1'. 1006# Check that SRO lock is incompatible with SNRW lock. 1007# Sending: 1008lock table t1 read;; 1009# 1010# Switching to connection 'default'. 1011# Check that the above LOCK TABLE READ is blocked because of SNRW lock. 1012# Unblock LOCK TABLE READ. 1013unlock tables; 1014# 1015# Switching to connection 'mdl_con1'. 1016# Reaping LOCK TABLE READ. 1017unlock tables; 1018# 1019# Note that we can't easily check SNW vs SNRW locks since 1020# SNW is only used by ALTER TABLE after upgrading from SU 1021# and SU is also incompatible with SNRW. 1022# 1023# Switching to connection 'default'. 1024lock table t1 write; 1025# 1026# Switching to connection 'mdl_con1'. 1027# Check that SNRW lock is incompatible with SNRW lock. 1028# Sending: 1029lock table t1 write;; 1030# 1031# Switching to connection 'default'. 1032# Check that the above LOCK TABLES is blocked because of SNRW lock. 1033# Unblock waiting LOCK TABLES. 1034unlock tables; 1035# 1036# Switching to connection 'mdl_con1'. 1037# Reaping LOCK TABLES 1038insert into t1 values (1); 1039unlock tables; 1040# 1041# Switching to connection 'default'. 1042lock table t1 write; 1043# 1044# Switching to connection 'mdl_con1'. 1045# Check that X lock is incompatible with SNRW lock. 1046# Sending: 1047rename table t1 to t2;; 1048# 1049# Switching to connection 'default'. 1050# Check that the above RENAME is blocked because of SNRW lock. 1051# Unblock RENAME TABLE 1052unlock tables; 1053# 1054# Switching to connection 'mdl_con1'. 1055# Reaping RENAME TABLE 1056# Revert back to original state of things. 1057rename table t2 to t1; 1058# 1059# There is no need to check that upgrade from SNW/SNRW to X is 1060# blocked by presence of another SNRW lock because SNW/SNRW is 1061# incompatible with SNRW anyway. 1062# 1063# Switching to connection 'default'. 1064# 1065# 1066# 10) Now do the same round of tests for X lock. We use additional 1067# table to get long-lived lock of this type. 1068# 1069create table t2 (c1 int); 1070# 1071# Switching to connection 'mdl_con2'. 1072# Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked 1073# after acquiring X lock on t1. 1074lock tables t2 read; 1075# 1076# Switching to connection 'default'. 1077# Sending: 1078rename table t1 to t2;; 1079# 1080# Switching to connection 'mdl_con1'. 1081# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1082# Check that S lock in incompatible with X lock. 1083# Sending: 1084handler t1 open;; 1085# 1086# Switching to connection 'mdl_con2'. 1087# Check that the above HANDLER statement is blocked because of X lock. 1088# Unblock RENAME TABLE 1089unlock tables; 1090# 1091# Switching to connection 'default'. 1092# Reaping RENAME TABLE. 1093ERROR 42S01: Table 't2' already exists 1094# 1095# Switching to connection 'mdl_con1'. 1096# Reaping HANDLER. 1097handler t1 close; 1098# 1099# Switching to connection 'mdl_con2'. 1100# Prepare for blocking RENAME TABLE. 1101lock tables t2 read; 1102# 1103# Switching to connection 'default'. 1104# Sending: 1105rename table t1 to t2;; 1106# 1107# Switching to connection 'mdl_con1'. 1108# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1109# Check that SH lock in incompatible with X lock. 1110# Sending: 1111select column_name from information_schema.columns where table_schema='test' and table_name='t1';; 1112# 1113# Switching to connection 'mdl_con2'. 1114# Check that the above SELECT ... FROM I_S ... statement is blocked 1115# because of X lock. 1116# Unblock RENAME TABLE 1117unlock tables; 1118# 1119# Switching to connection 'default'. 1120# Reaping RENAME TABLE. 1121ERROR 42S01: Table 't2' already exists 1122# 1123# Switching to connection 'mdl_con1'. 1124# Reaping SELECT ... FROM I_S. 1125column_name 1126c1 1127# 1128# Switching to connection 'mdl_con2'. 1129# Prepare for blocking RENAME TABLE. 1130lock tables t2 read; 1131# 1132# Switching to connection 'default'. 1133# Sending: 1134rename table t1 to t2;; 1135# 1136# Switching to connection 'mdl_con1'. 1137# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1138# Check that SR lock in incompatible with X lock. 1139# Sending: 1140select count(*) from t1;; 1141# 1142# Switching to connection 'mdl_con2'. 1143# Check that the above SELECT statement is blocked 1144# because of X lock. 1145# Unblock RENAME TABLE 1146unlock tables; 1147# 1148# Switching to connection 'default'. 1149# Reaping RENAME TABLE. 1150ERROR 42S01: Table 't2' already exists 1151# 1152# Switching to connection 'mdl_con1'. 1153# Reaping SELECT. 1154count(*) 11554 1156# 1157# Switching to connection 'mdl_con2'. 1158# Prepare for blocking RENAME TABLE. 1159lock tables t2 read; 1160# 1161# Switching to connection 'default'. 1162# Sending: 1163rename table t1 to t2;; 1164# 1165# Switching to connection 'mdl_con1'. 1166# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1167# Check that SW lock in incompatible with X lock. 1168# Sending: 1169delete from t1 limit 2;; 1170# 1171# Switching to connection 'mdl_con2'. 1172# Check that the above DELETE statement is blocked 1173# because of X lock. 1174# Unblock RENAME TABLE 1175unlock tables; 1176# 1177# Switching to connection 'default'. 1178# Reaping RENAME TABLE. 1179ERROR 42S01: Table 't2' already exists 1180# 1181# Switching to connection 'mdl_con1'. 1182# Reaping DELETE. 1183# 1184# Switching to connection 'mdl_con2'. 1185# Prepare for blocking RENAME TABLE. 1186lock tables t2 read; 1187# 1188# Switching to connection 'default'. 1189# Sending: 1190rename table t1 to t2;; 1191# 1192# Switching to connection 'mdl_con1'. 1193# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1194# Check that SWLP lock in incompatible with X lock. 1195# Sending: 1196insert low_priority into t1 values (1);; 1197# 1198# Switching to connection 'mdl_con2'. 1199# Check that the above INSERT statement is blocked 1200# because of X lock. 1201# Unblock RENAME TABLE 1202unlock tables; 1203# 1204# Switching to connection 'default'. 1205# Reaping RENAME TABLE. 1206ERROR 42S01: Table 't2' already exists 1207# 1208# Switching to connection 'mdl_con1'. 1209# Reaping INSERT. 1210# 1211# Switching to connection 'mdl_con2'. 1212# Prepare for blocking RENAME TABLE. 1213lock tables t2 read; 1214# 1215# Switching to connection 'default'. 1216# Sending: 1217rename table t1 to t2;; 1218# 1219# Switching to connection 'mdl_con1'. 1220# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1221# Check that SU lock is incompatible with X lock. 1222# Sending: 1223alter table t1 add primary key (c1);; 1224# 1225# Switching to connection 'mdl_con2'. 1226# Check that the above ALTER statement is blocked 1227# because of X lock. 1228# Unblock RENAME TABLE 1229unlock tables; 1230# 1231# Switching to connection 'default'. 1232# Reaping RENAME TABLE 1233ERROR 42S01: Table 't2' already exists 1234# 1235# Switching to connection 'mdl_con1'. 1236# Reaping ALTER. 1237ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1238# 1239# Switching to connection 'mdl_con2'. 1240# Prepare for blocking RENAME TABLE. 1241lock tables t2 read; 1242# 1243# Switching to connection 'default'. 1244# Sending: 1245rename table t1 to t2;; 1246# 1247# Switching to connection 'mdl_con1'. 1248# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1249# Check that SRO lock is incompatible with X lock. 1250# Sending: 1251lock table t1 read;; 1252# 1253# Switching to connection 'mdl_con2'. 1254# Check that the above LOCK TABLE READ statement is blocked 1255# because of X lock. 1256# Unblock RENAME TABLE 1257unlock tables; 1258# 1259# Switching to connection 'default'. 1260# Reaping RENAME TABLE 1261ERROR 42S01: Table 't2' already exists 1262# 1263# Switching to connection 'mdl_con1'. 1264# Reaping LOCK TABLE READ. 1265unlock tables; 1266# 1267# Note that we can't easily check SNW vs X locks since 1268# SNW is only used by ALTER TABLE after upgrading from SU 1269# and SU is also incompatible with X. 1270# 1271# Switching to connection 'mdl_con2'. 1272# Prepare for blocking RENAME TABLE. 1273lock tables t2 read; 1274# 1275# Switching to connection 'default'. 1276# Sending: 1277rename table t1 to t2;; 1278# 1279# Switching to connection 'mdl_con1'. 1280# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1281# Check that SNRW lock is incompatible with X lock. 1282# Sending: 1283lock table t1 write;; 1284# 1285# Switching to connection 'mdl_con2'. 1286# Check that the above LOCK TABLE statement is blocked 1287# because of X lock. 1288# Unblock RENAME TABLE 1289unlock tables; 1290# 1291# Switching to connection 'default'. 1292# Reaping RENAME TABLE 1293ERROR 42S01: Table 't2' already exists 1294# 1295# Switching to connection 'mdl_con1'. 1296# Reaping LOCK TABLE. 1297unlock tables; 1298# 1299# Switching to connection 'mdl_con2'. 1300# Prepare for blocking RENAME TABLE. 1301lock tables t2 read; 1302# 1303# Switching to connection 'default'. 1304# Sending: 1305rename table t1 to t2;; 1306# 1307# Switching to connection 'mdl_con1'. 1308# Check that RENAME has acquired X lock on t1 and is waiting for t2. 1309# Check that X lock is incompatible with X lock. 1310# Sending: 1311rename table t1 to t3;; 1312# 1313# Switching to connection 'mdl_con2'. 1314# Check that the above RENAME statement is blocked 1315# because of X lock. 1316# Unblock RENAME TABLE 1317unlock tables; 1318# 1319# Switching to connection 'default'. 1320# Reaping RENAME TABLE 1321ERROR 42S01: Table 't2' already exists 1322# 1323# Switching to connection 'mdl_con1'. 1324# Reaping RENAME. 1325rename table t3 to t1; 1326# 1327# B) Now let us test compatibility in cases when both locks 1328# are pending. I.e. let us test rules for priorities between 1329# different types of metadata locks. 1330# 1331# Note: No tests for pending SU lock as this lock requires 1332# even stronger active or pending lock. 1333# 1334# 1335# Switching to connection 'mdl_con2'. 1336# 1337# 1) Check compatibility for pending SW lock. 1338# 1339# Acquire SRO lock in order to create pending SW lock later. 1340lock table t1 read; 1341# 1342# Switching to connection 'default'. 1343# Add pending SW lock. 1344# Sending: 1345insert into t1 values (1);; 1346# 1347# Switching to connection 'mdl_con1'. 1348# Check that INSERT is waiting with pending SW lock. 1349# Check that S, SH, SR locks are compatible with pending SW 1350handler t1 open t; 1351handler t close; 1352select column_name from information_schema.columns where 1353table_schema='test' and table_name='t1'; 1354column_name 1355c1 1356select count(*) from t1; 1357count(*) 13583 1359# Can't check if SW and SWLP are compatible with pending SW 1360# as they are not compatible with active SRO. 1361# 1362# Check that SU lock is compatible with pending SW lock 1363alter table t1 add index (not_exist); 1364ERROR 42000: Key column 'not_exist' doesn't exist in table 1365# Check that SRO lock is not compatible with pending SW lock 1366# Sending: 1367lock table t1 read;; 1368# 1369# Switching to connection 'mdl_con2'. 1370# Check that LOCK TABLE is waiting due to pending SW lock. 1371unlock tables; 1372# 1373# Switching to connection 'default'. 1374# Reaping INSERT 1375# 1376# Switching to connection 'mdl_con1'. 1377# Reaping 2nd LOCK TABLE READ 1378unlock tables; 1379# 1380# Switching to connection 'mdl_con2'. 1381# 1382# Acquire SRO lock in order to create pending SW lock later. 1383lock table t1 read; 1384# 1385# Switching to connection 'default'. 1386# Add pending SW lock. 1387# Sending: 1388delete from t1 limit 2;; 1389# 1390# Switching to connection 'mdl_con1'. 1391# Check that DELETE is waiting with pending SW lock. 1392# Check that SNW lock is compatible with pending SW lock 1393alter table t1 add primary key (c1); 1394ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1395# 1396# We can't do similar check for SNRW and X locks because 1397# they will also be blocked by active SRO lock. 1398# 1399# 1400# Switching to connection 'mdl_con2'. 1401unlock tables; 1402# 1403# Switching to connection 'default'. 1404# Reaping DELETE. 1405# 1406# Switching to connection 'mdl_con2'. 1407# 1408# 2) Check compatibility for pending SWLP lock. 1409# 1410# Acquire SRO lock in order to create pending SWLP lock later. 1411lock table t1 read; 1412# 1413# Switching to connection 'default'. 1414# Add pending SWLP lock. 1415# Sending: 1416insert low_priority into t1 values (1);; 1417# 1418# Switching to connection 'mdl_con1'. 1419# Check that INSERT is waiting with pending SWLP lock. 1420# Check that S, SH, SR locks are compatible with pending SWLP 1421handler t1 open t; 1422handler t close; 1423select column_name from information_schema.columns where 1424table_schema='test' and table_name='t1'; 1425column_name 1426c1 1427select count(*) from t1; 1428count(*) 14292 1430# Can't check if SW and SWLP are compatible with pending SWLP 1431# as they are not compatible with active SRO. 1432# 1433# Check that SU lock is compatible with pending SWLP lock 1434alter table t1 add index (not_exist); 1435ERROR 42000: Key column 'not_exist' doesn't exist in table 1436# Check that SRO lock is compatible with pending SWLP lock 1437lock table t1 read; 1438unlock tables; 1439# Check that SNW lock is compatible with pending SWLP lock 1440alter table t1 add primary key (c1); 1441ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1442# 1443# We can't do similar check for SNRW and X locks because 1444# they will also be blocked by active SRO lock. 1445# 1446# 1447# Switching to connection 'mdl_con2'. 1448unlock tables; 1449# 1450# Switching to connection 'default'. 1451# Reaping INSERT. 1452# 1453# Switching to connection 'mdl_con2'. 1454# 1455# 3) Check compatibility for pending SRO lock. 1456# 1457# Acquire SW lock in order to create pending SRO lock later. 1458begin; 1459insert into t1 values (1); 1460# 1461# Switching to connection 'default'. 1462# Add pending SRO lock. 1463# Sending: 1464lock table t1 read;; 1465# 1466# Switching to connection 'mdl_con1'. 1467# Check that LOCK TABLE READ is waiting with pending SRO lock. 1468# Check that S, SH, SR, SW locks are compatible with pending SRO 1469handler t1 open t; 1470handler t close; 1471select column_name from information_schema.columns where 1472table_schema='test' and table_name='t1'; 1473column_name 1474c1 1475select count(*) from t1; 1476count(*) 14774 1478delete from t1 limit 1; 1479# Check that SWLP is incompatible with pending SRO 1480# Sending: 1481delete low_priority from t1 limit 1;; 1482# 1483# Switching to connection 'mdl_con2'. 1484# Check that DELETE is waiting with pending SRO lock. 1485# Unblock LOCK TABLE READ 1486commit; 1487# 1488# Switching to connection 'default'. 1489# Reaping LOCK TABLE READ 1490unlock tables; 1491# 1492# Switching to connection 'mdl_con1'. 1493# Reaping DELETE 1494# 1495# Switching to connection 'mdl_con2'. 1496begin; 1497insert into t1 values (1); 1498# 1499# Switching to connection 'default'. 1500# Add pending SRO lock. 1501# Sending: 1502lock table t1 read;; 1503# 1504# Switching to connection 'mdl_con1'. 1505# Check that LOCK TABLE READ is waiting with pending SRO lock. 1506# Check that SU lock is compatible with pending SRO lock 1507alter table t1 add index (not_exist); 1508ERROR 42000: Key column 'not_exist' doesn't exist in table 1509# 1510# We can't do similar check for SRO, SNW, SNRW and X locks because 1511# they will also be blocked by active SW lock. 1512# 1513# 1514# Switching to connection 'mdl_con2'. 1515# Unblock LOCK TABLE READ. 1516commit; 1517# 1518# Switching to connection 'default'. 1519# Reaping LOCK TABLE READ. 1520unlock tables; 1521# 1522# Switching to connection 'mdl_con2'. 1523# 1524# 4) Check compatibility for pending SNW lock. 1525# 1526# Acquire SW lock in order to create pending SNW lock later. 1527begin; 1528insert into t1 values (1); 1529# 1530# Switching to connection 'default'. 1531# Add pending SNW lock. 1532# Sending: 1533alter table t1 add primary key (c1);; 1534# 1535# Switching to connection 'mdl_con1'. 1536# Check that ALTER TABLE is waiting with pending SNW lock. 1537# Check that S, SH and SR locks are compatible with pending SNW 1538handler t1 open t; 1539handler t close; 1540select column_name from information_schema.columns where 1541table_schema='test' and table_name='t1'; 1542column_name 1543c1 1544select count(*) from t1; 1545count(*) 15464 1547# Check that SW is incompatible with pending SNW 1548# Sending: 1549delete from t1 limit 1;; 1550# 1551# Switching to connection 'mdl_con2'. 1552# Check that the above DELETE is blocked because of pending SNW lock. 1553# Unblock ALTER TABLE. 1554commit; 1555# 1556# Switching to connection 'default'. 1557# Reaping ALTER. 1558ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1559# 1560# Switching to connection 'mdl_con1'. 1561# Reaping DELETE. 1562# 1563# Switching to connection 'mdl_con2'. 1564# Acquire SW lock in order to create pending SNW lock later. 1565begin; 1566insert into t1 values (1); 1567# 1568# Switching to connection 'default'. 1569# Add pending SNW lock. 1570# Sending: 1571alter table t1 add primary key (c1);; 1572# 1573# Switching to connection 'mdl_con1'. 1574# Check that ALTER TABLE is waiting with pending SNW lock. 1575# Check that SWLP is incompatible with pending SNW 1576# Sending: 1577delete low_priority from t1 limit 1;; 1578# 1579# Switching to connection 'mdl_con2'. 1580# Check that the above DELETE is blocked because of pending SNW lock. 1581# Unblock ALTER TABLE. 1582commit; 1583# 1584# Switching to connection 'default'. 1585# Reaping ALTER. 1586ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1587# 1588# Switching to connection 'mdl_con1'. 1589# Reaping DELETE. 1590# 1591# We can't do similar check for SU as ALTER first acquire SU 1592# before upgrade to SNW. 1593# We can't do similar check for SRO, SNW, SNRW and X locks because 1594# they will also be blocked by active SW lock. 1595# 1596# 1597# Switching to connection 'mdl_con2'. 1598# 1599# 5) Check compatibility for pending SNRW lock. 1600# 1601# Acquire SR lock in order to create pending SNRW lock. 1602begin; 1603select count(*) from t1; 1604count(*) 16053 1606# 1607# Switching to connection 'default'. 1608# Add pending SNRW lock. 1609# Sending: 1610lock table t1 write;; 1611# 1612# Switching to connection 'mdl_con1'. 1613# Check that LOCK TABLE is waiting with pending SNRW lock. 1614# Check that S and SH locks are compatible with pending SNRW 1615handler t1 open t; 1616handler t close; 1617select column_name from information_schema.columns where 1618table_schema='test' and table_name='t1'; 1619column_name 1620c1 1621# Check that SR is incompatible with pending SNRW 1622# Sending: 1623select count(*) from t1;; 1624# 1625# Switching to connection 'mdl_con2'. 1626# Check that the above SELECT is blocked because of pending SNRW lock. 1627# Unblock LOCK TABLE. 1628commit; 1629# 1630# Switching to connection 'default'. 1631# Reaping LOCK TABLE. 1632unlock tables; 1633# 1634# Switching to connection 'mdl_con1'. 1635# Reaping SELECT. 1636count(*) 16373 1638# Restore pending SNRW lock. 1639# 1640# Switching to connection 'mdl_con2'. 1641begin; 1642select count(*) from t1; 1643count(*) 16443 1645# 1646# Switching to connection 'default'. 1647# Sending: 1648lock table t1 write;; 1649# 1650# Switching to connection 'mdl_con1'. 1651# Check that LOCK TABLE is waiting with pending SNRW lock. 1652# Check that SW is incompatible with pending SNRW 1653# Sending: 1654insert into t1 values (1),(1);; 1655# 1656# Switching to connection 'mdl_con2'. 1657# Check that the above INSERT is blocked because of pending SNRW lock. 1658# Unblock LOCK TABLE. 1659commit; 1660# 1661# Switching to connection 'default'. 1662# Reaping LOCK TABLE. 1663unlock tables; 1664# 1665# Switching to connection 'mdl_con1'. 1666# Reaping INSERT. 1667# Restore pending SNRW lock. 1668# 1669# Switching to connection 'mdl_con2'. 1670begin; 1671select count(*) from t1; 1672count(*) 16735 1674# 1675# Switching to connection 'default'. 1676# Sending: 1677lock table t1 write;; 1678# 1679# Switching to connection 'mdl_con1'. 1680# Check that LOCK TABLE is waiting with pending SNRW lock. 1681# Check that SWLP is incompatible with pending SNRW 1682# Sending: 1683delete low_priority from t1 limit 1;; 1684# 1685# Switching to connection 'mdl_con2'. 1686# Check that the above DELETE is blocked because of pending SNRW lock. 1687# Unblock LOCK TABLE. 1688commit; 1689# 1690# Switching to connection 'default'. 1691# Reaping LOCK TABLE. 1692unlock tables; 1693# 1694# Switching to connection 'mdl_con1'. 1695# Reaping DELETE. 1696# Restore pending SNRW lock. 1697# 1698# Switching to connection 'mdl_con2'. 1699begin; 1700select count(*) from t1; 1701count(*) 17024 1703# 1704# Switching to connection 'default'. 1705# Sending: 1706lock table t1 write;; 1707# 1708# Switching to connection 'mdl_con1'. 1709# Check that LOCK TABLE is waiting with pending SNRW lock. 1710# Check that SU lock is compatible with pending SNRW lock 1711alter table t1 add index (not_exist); 1712ERROR 42000: Key column 'not_exist' doesn't exist in table 1713# Check that SRO is incompatible with pending SNRW 1714# Sending: 1715lock table t1 read;; 1716# 1717# Switching to connection 'mdl_con2'. 1718# Check that the above LOCK TABLE READ is blocked because 1719# of pending SNRW lock. 1720# Unblock LOCK TABLE. 1721commit; 1722# 1723# Switching to connection 'default'. 1724# Reaping LOCK TABLE. 1725unlock tables; 1726# 1727# Switching to connection 'mdl_con1'. 1728# Reaping LOCK TABLE READ. 1729unlock tables; 1730# Restore pending SNRW lock. 1731# 1732# Switching to connection 'mdl_con2'. 1733begin; 1734select count(*) from t1; 1735count(*) 17364 1737# 1738# Switching to connection 'default'. 1739# Sending: 1740lock table t1 write;; 1741# 1742# Switching to connection 'mdl_con1'. 1743# Check that LOCK TABLE is waiting with pending SNRW lock. 1744# Check that SNW is compatible with pending SNRW 1745# So ALTER TABLE statements are not starved by LOCK TABLEs. 1746alter table t1 add primary key (c1); 1747ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1748# 1749# Switching to connection 'mdl_con2'. 1750# Unblock LOCK TABLE. 1751commit; 1752# 1753# Switching to connection 'default'. 1754# Reaping LOCK TABLE. 1755unlock tables; 1756# 1757# We can't do similar check for SNRW and X locks because 1758# they will also be blocked by active SR lock. 1759# 1760# 1761# Switching to connection 'mdl_con2'. 1762# 1763# 6) Check compatibility for pending X lock. 1764# 1765# Acquire SR lock in order to create pending X lock. 1766begin; 1767select count(*) from t1; 1768count(*) 17694 1770# 1771# Switching to connection 'default'. 1772# Add pending X lock. 1773# Sending: 1774rename table t1 to t2;; 1775# 1776# Switching to connection 'mdl_con1'. 1777# Check that RENAME TABLE is waiting with pending X lock. 1778# Check that SH locks are compatible with pending X 1779select column_name from information_schema.columns where 1780table_schema='test' and table_name='t1'; 1781column_name 1782c1 1783# Check that S is incompatible with pending X 1784# Sending: 1785handler t1 open;; 1786# 1787# Switching to connection 'mdl_con2'. 1788# Check that the above HANDLER OPEN is blocked because of pending X lock. 1789# Unblock RENAME TABLE. 1790commit; 1791# 1792# Switching to connection 'default'. 1793# Reaping RENAME TABLE. 1794ERROR 42S01: Table 't2' already exists 1795# 1796# Switching to connection 'mdl_con1'. 1797# Reaping HANDLER t1 OPEN. 1798handler t1 close; 1799# Restore pending X lock. 1800# 1801# Switching to connection 'mdl_con2'. 1802begin; 1803select count(*) from t1; 1804count(*) 18054 1806# 1807# Switching to connection 'default'. 1808# Add pending X lock. 1809# Sending: 1810rename table t1 to t2;; 1811# 1812# Switching to connection 'mdl_con1'. 1813# Check that RENAME TABLE is waiting with pending X lock. 1814# Check that SR is incompatible with pending X 1815# Sending: 1816select count(*) from t1;; 1817# 1818# Switching to connection 'mdl_con2'. 1819# Check that the above SELECT is blocked because of pending X lock. 1820# Unblock RENAME TABLE. 1821commit; 1822# 1823# Switching to connection 'default'. 1824# Reaping RENAME TABLE. 1825ERROR 42S01: Table 't2' already exists 1826# 1827# Switching to connection 'mdl_con1'. 1828# Reaping SELECT. 1829count(*) 18304 1831# Restore pending X lock. 1832# 1833# Switching to connection 'mdl_con2'. 1834begin; 1835select count(*) from t1; 1836count(*) 18374 1838# 1839# Switching to connection 'default'. 1840# Add pending X lock. 1841# Sending: 1842rename table t1 to t2;; 1843# 1844# Switching to connection 'mdl_con1'. 1845# Check that RENAME TABLE is waiting with pending X lock. 1846# Check that SW is incompatible with pending X 1847# Sending: 1848delete from t1 limit 2;; 1849# 1850# Switching to connection 'mdl_con2'. 1851# Check that the above DELETE is blocked because of pending X lock. 1852# Unblock RENAME TABLE. 1853commit; 1854# 1855# Switching to connection 'default'. 1856# Reaping RENAME TABLE. 1857ERROR 42S01: Table 't2' already exists 1858# 1859# Switching to connection 'mdl_con1'. 1860# Reaping DELETE. 1861# Restore pending X lock. 1862# 1863# Switching to connection 'mdl_con2'. 1864begin; 1865select count(*) from t1; 1866count(*) 18672 1868# 1869# Switching to connection 'default'. 1870# Add pending X lock. 1871# Sending: 1872rename table t1 to t2;; 1873# 1874# Switching to connection 'mdl_con1'. 1875# Check that RENAME TABLE is waiting with pending X lock. 1876# Check that SWLP is incompatible with pending X 1877# Sending: 1878insert low_priority into t1 values (1);; 1879# 1880# Switching to connection 'mdl_con2'. 1881# Check that the above INSERT is blocked because of pending X lock. 1882# Unblock RENAME TABLE. 1883commit; 1884# 1885# Switching to connection 'default'. 1886# Reaping RENAME TABLE. 1887ERROR 42S01: Table 't2' already exists 1888# 1889# Switching to connection 'mdl_con1'. 1890# Reaping INSERT. 1891# Restore pending X lock. 1892# 1893# Switching to connection 'mdl_con2'. 1894begin; 1895select count(*) from t1; 1896count(*) 18973 1898# 1899# Switching to connection 'default'. 1900# Add pending X lock. 1901# Sending: 1902rename table t1 to t2;; 1903# 1904# Switching to connection 'mdl_con1'. 1905# Check that RENAME TABLE is waiting with pending X lock. 1906# Check that SU is incompatible with pending X 1907# Sending: 1908alter table t1 add index (not_exist);; 1909# 1910# Switching to connection 'mdl_con2'. 1911# Check that the above ALTER TABLE is blocked 1912# because of pending X lock. 1913# Unblock RENAME TABLE. 1914commit; 1915# 1916# Switching to connection 'default'. 1917# Reaping RENAME TABLE. 1918ERROR 42S01: Table 't2' already exists 1919# 1920# Switching to connection 'mdl_con1'. 1921# Reaping ALTER TABLE. 1922ERROR 42000: Key column 'not_exist' doesn't exist in table 1923# Restore pending X lock. 1924# 1925# Switching to connection 'mdl_con2'. 1926begin; 1927select count(*) from t1; 1928count(*) 19293 1930# 1931# Switching to connection 'default'. 1932# Add pending X lock. 1933# Sending: 1934rename table t1 to t2;; 1935# 1936# Switching to connection 'mdl_con1'. 1937# Check that RENAME TABLE is waiting with pending X lock. 1938# Check that SRO is incompatible with pending X 1939# Sending: 1940lock table t1 read;; 1941# 1942# Switching to connection 'mdl_con2'. 1943# Check that the above LOCK TABLE READ is blocked 1944# because of pending X lock. 1945# Unblock RENAME TABLE. 1946commit; 1947# 1948# Switching to connection 'default'. 1949# Reaping RENAME TABLE. 1950ERROR 42S01: Table 't2' already exists 1951# 1952# Switching to connection 'mdl_con1'. 1953# Reaping LOCK TABLE READ. 1954unlock tables; 1955# Restore pending X lock. 1956# 1957# Switching to connection 'mdl_con2'. 1958begin; 1959select count(*) from t1; 1960count(*) 19613 1962# 1963# Switching to connection 'default'. 1964# Add pending X lock. 1965# Sending: 1966rename table t1 to t2;; 1967# 1968# Switching to connection 'mdl_con1'. 1969# Check that RENAME TABLE is waiting with pending X lock. 1970# Check that SNW is incompatible with pending X 1971# Sending: 1972alter table t1 add primary key (c1);; 1973# 1974# Switching to connection 'mdl_con2'. 1975# Check that the above ALTER TABLE is blocked because of pending X lock. 1976# Unblock RENAME TABLE. 1977commit; 1978# 1979# Switching to connection 'default'. 1980# Reaping RENAME TABLE. 1981ERROR 42S01: Table 't2' already exists 1982# 1983# Switching to connection 'mdl_con1'. 1984# Reaping ALTER TABLE. 1985ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1986# Restore pending X lock. 1987# 1988# Switching to connection 'mdl_con2'. 1989handler t1 open; 1990# 1991# Switching to connection 'default'. 1992# Add pending X lock. 1993# Sending: 1994rename table t1 to t2;; 1995# 1996# Switching to connection 'mdl_con1'. 1997# Check that RENAME TABLE is waiting with pending X lock. 1998# Check that SNRW is incompatible with pending X 1999# Sending: 2000lock table t1 write;; 2001# 2002# Switching to connection 'mdl_con3'. 2003# Check that the above LOCK TABLES is blocked because of pending X lock. 2004# 2005# Switching to connection 'mdl_con2'. 2006# Unblock RENAME TABLE. 2007handler t1 close; 2008# 2009# Switching to connection 'default'. 2010# Reaping RENAME TABLE. 2011ERROR 42S01: Table 't2' already exists 2012# 2013# Switching to connection 'mdl_con1'. 2014# Reaping LOCK TABLES. 2015unlock tables; 2016# 2017# Switching to connection 'default'. 2018# 2019# 2020# C) Now let us test how type-of-operation locks are handled in 2021# transactional context. Obviously we are mostly interested 2022# in conflicting types of locks. 2023# 2024# Note: No tests for active/pending SU lock since 2025# ALTER TABLE is in its own transaction. 2026# No tests for active/pending SRO lock since 2027# it is pretty similar to SNW lock in this 2028# respect. 2029# 2030# 2031# 1) Let us check how various locks used within transactional 2032# context interact with active/pending SNW lock. 2033# 2034# We start with case when we are acquiring lock on the table 2035# which was not used in the transaction before. 2036begin; 2037select count(*) from t1; 2038count(*) 20393 2040# 2041# Switching to connection 'mdl_con1'. 2042# Create an active SNW lock on t2. 2043# We have to use DEBUG_SYNC facility as otherwise SNW lock 2044# will be immediately released (or upgraded to X lock). 2045insert into t2 values (1), (1); 2046set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 2047# Sending: 2048alter table t2 add primary key (c1), algorithm=copy, lock=shared;; 2049# 2050# Switching to connection 'default'. 2051set debug_sync= 'now WAIT_FOR locked'; 2052# SR lock should be acquired without any waiting. 2053select count(*) from t2; 2054count(*) 20552 2056commit; 2057# Now let us check that we will wait in case of SW lock. 2058begin; 2059select count(*) from t1; 2060count(*) 20613 2062# Sending: 2063insert into t2 values (1);; 2064# 2065# Switching to connection 'mdl_con2'. 2066# Check that the above INSERT is blocked. 2067# Unblock ALTER TABLE and thus INSERT. 2068set debug_sync= 'now SIGNAL finish'; 2069# 2070# Switching to connection 'mdl_con1'. 2071# Reap ALTER TABLE. 2072ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 2073# 2074# Switching to connection 'default'. 2075# Reap INSERT. 2076commit; 2077# 2078# Now let us see what happens when we are acquiring lock on the table 2079# which is already used in transaction. 2080# 2081# *) First, case when transaction which has SR lock on the table also 2082# locked in SNW mode acquires yet another SR lock and then tries 2083# to acquire SW lock. 2084begin; 2085select count(*) from t1; 2086count(*) 20873 2088# 2089# Switching to connection 'mdl_con1'. 2090# Create an active SNW lock on t1. 2091set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 2092# Sending: 2093alter table t1 add primary key (c1), algorithm=copy, lock=shared;; 2094# 2095# Switching to connection 'default'. 2096set debug_sync= 'now WAIT_FOR locked'; 2097# We should still be able to get SR lock without waiting. 2098select count(*) from t1; 2099count(*) 21003 2101# Since the above ALTER TABLE is not upgrading SNW lock to X by waiting 2102# for SW lock we won't create deadlock. 2103# So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. 2104# Sending: 2105insert into t1 values (1);; 2106# 2107# Switching to connection 'mdl_con2'. 2108# Check that the above INSERT is blocked. 2109# Unblock ALTER TABLE and thus INSERT. 2110set debug_sync= 'now SIGNAL finish'; 2111# 2112# Switching to connection 'mdl_con1'. 2113# Reap ALTER TABLE. 2114ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 2115# 2116# Switching to connection 'default'. 2117# Reap INSERT. 2118commit; 2119# 2120# **) Now test in which transaction that has SW lock on the table 2121# against which there is pending SNW lock acquires SR and SW 2122# locks on this table. 2123# 2124begin; 2125insert into t1 values (1); 2126# 2127# Switching to connection 'mdl_con1'. 2128# Create pending SNW lock on t1. 2129# Sending: 2130alter table t1 add primary key (c1);; 2131# 2132# Switching to connection 'default'. 2133# Wait until ALTER TABLE starts waiting for SNW lock. 2134# We should still be able to get both SW and SR locks without waiting. 2135select count(*) from t1; 2136count(*) 21375 2138delete from t1 limit 1; 2139# Unblock ALTER TABLE. 2140commit; 2141# 2142# Switching to connection 'mdl_con1'. 2143# Reap ALTER TABLE. 2144ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 2145# 2146# Switching to connection 'default'. 2147# 2148# 2) Now similar tests for active SNW lock which is being upgraded 2149# to X lock. 2150# 2151# Again we start with case when we are acquiring lock on the 2152# table which was not used in the transaction before. 2153begin; 2154select count(*) from t1; 2155count(*) 21564 2157# 2158# Switching to connection 'mdl_con2'. 2159# Start transaction which will prevent SNW -> X upgrade from 2160# completing immediately. 2161begin; 2162select count(*) from t2; 2163count(*) 21643 2165# 2166# Switching to connection 'mdl_con1'. 2167# Create SNW lock pending upgrade to X on t2. 2168# Sending: 2169alter table t2 add column c2 int;; 2170# 2171# Switching to connection 'default'. 2172# Wait until ALTER TABLE starts waiting X lock. 2173# Check that attempt to acquire SR lock on t2 causes waiting. 2174# Sending: 2175select count(*) from t2;; 2176# 2177# Switching to connection 'mdl_con2'. 2178# Check that the above SELECT is blocked. 2179# Unblock ALTER TABLE. 2180commit; 2181# 2182# Switching to connection 'mdl_con1'. 2183# Reap ALTER TABLE. 2184# 2185# Switching to connection 'default'. 2186# Reap SELECT. 2187count(*) 21883 2189commit; 2190# Do similar check for SW lock. 2191begin; 2192select count(*) from t1; 2193count(*) 21944 2195# 2196# Switching to connection 'mdl_con2'. 2197# Start transaction which will prevent SNW -> X upgrade from 2198# completing immediately. 2199begin; 2200select count(*) from t2; 2201count(*) 22023 2203# 2204# Switching to connection 'mdl_con1'. 2205# Create SNW lock pending upgrade to X on t2. 2206# Sending: 2207alter table t2 drop column c2;; 2208# 2209# Switching to connection 'default'. 2210# Wait until ALTER TABLE starts waiting X lock. 2211# Check that attempt to acquire SW lock on t2 causes waiting. 2212# Sending: 2213insert into t2 values (1);; 2214# 2215# Switching to connection 'mdl_con2'. 2216# Check that the above INSERT is blocked. 2217# Unblock ALTER TABLE. 2218commit; 2219# 2220# Switching to connection 'mdl_con1'. 2221# Reap ALTER TABLE. 2222# 2223# Switching to connection 'default'. 2224# Reap INSERT. 2225commit; 2226# 2227# Test for the case in which we are acquiring lock on the table 2228# which is already used in transaction. 2229# 2230begin; 2231select count(*) from t1; 2232count(*) 22334 2234# 2235# Switching to connection 'mdl_con1'. 2236# Create SNW lock pending upgrade to X. 2237# Sending: 2238alter table t1 add column c2 int;; 2239# 2240# Switching to connection 'default'. 2241# Wait until ALTER TABLE starts waiting X lock. 2242# Check that transaction is still able to acquire SR lock. 2243select count(*) from t1; 2244count(*) 22454 2246# Waiting trying to acquire SW lock will cause deadlock and 2247# therefore should cause an error. 2248delete from t1 limit 1; 2249ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2250# Unblock ALTER TABLE. 2251commit; 2252# 2253# Switching to connection 'mdl_con1'. 2254# Reap ALTER TABLE. 2255# 2256# Switching to connection 'default'. 2257# 2258# 3) Check how various locks used within transactional context 2259# interact with active/pending SNRW lock. 2260# 2261# Once again we start with case when we are acquiring lock on 2262# the table which was not used in the transaction before. 2263begin; 2264select count(*) from t1; 2265count(*) 22664 2267# 2268# Switching to connection 'mdl_con1'. 2269lock table t2 write; 2270# 2271# Switching to connection 'default'. 2272# Attempt to acquire SR should be blocked. It should 2273# not cause errors as it does not creates deadlock. 2274# Sending: 2275select count(*) from t2;; 2276# 2277# Switching to connection 'mdl_con1'. 2278# Check that the above SELECT is blocked 2279# Unblock SELECT. 2280unlock tables; 2281# 2282# Switching to connection 'default'. 2283# Reap SELECT. 2284count(*) 22854 2286commit; 2287# Repeat the same test for SW lock. 2288begin; 2289select count(*) from t1; 2290count(*) 22914 2292# 2293# Switching to connection 'mdl_con1'. 2294lock table t2 write; 2295# 2296# Switching to connection 'default'. 2297# Again attempt to acquire SW should be blocked and should 2298# not cause any errors. 2299# Sending: 2300delete from t2 limit 1;; 2301# 2302# Switching to connection 'mdl_con1'. 2303# Check that the above DELETE is blocked 2304# Unblock DELETE. 2305unlock tables; 2306# 2307# Switching to connection 'default'. 2308# Reap DELETE. 2309commit; 2310# 2311# Now coverage for the case in which we are acquiring lock on 2312# the table which is already used in transaction and against 2313# which there is a pending SNRW lock request. 2314# 2315# *) Let us start with case when transaction has only a SR lock. 2316# 2317begin; 2318select count(*) from t1; 2319count(*) 23204 2321# 2322# Switching to connection 'mdl_con1'. 2323# Sending: 2324lock table t1 write;; 2325# 2326# Switching to connection 'default'. 2327# Wait until LOCK TABLE is blocked creating pending request for X lock. 2328# Check that another instance of SR lock is granted without waiting. 2329select count(*) from t1; 2330count(*) 23314 2332# Attempt to wait for SW lock will lead to deadlock, thus 2333# the below statement should end with ER_LOCK_DEADLOCK error. 2334delete from t1 limit 1; 2335ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2336# Unblock LOCK TABLES. 2337commit; 2338# 2339# Switching to connection 'mdl_con1'. 2340# Reap LOCK TABLES. 2341unlock tables; 2342# 2343# Switching to connection 'default'. 2344# 2345# **) Now case when transaction has a SW lock. 2346# 2347begin; 2348delete from t1 limit 1; 2349# 2350# Switching to connection 'mdl_con1'. 2351# Sending: 2352lock table t1 write;; 2353# 2354# Switching to connection 'default'. 2355# Wait until LOCK TABLE is blocked creating pending request for X lock. 2356# Check that both SR and SW locks are granted without waiting 2357# and errors. 2358select count(*) from t1; 2359count(*) 23603 2361insert into t1 values (1, 1); 2362# Unblock LOCK TABLES. 2363commit; 2364# 2365# Switching to connection 'mdl_con1'. 2366# Reap LOCK TABLES. 2367unlock tables; 2368# 2369# Switching to connection 'default'. 2370# 2371# 4) Check how various locks used within transactional context 2372# interact with active/pending X lock. 2373# 2374# As usual we start with case when we are acquiring lock on 2375# the table which was not used in the transaction before. 2376begin; 2377select count(*) from t1; 2378count(*) 23794 2380# 2381# Switching to connection 'mdl_con2'. 2382# Start transaction which will prevent X lock from going away 2383# immediately. 2384begin; 2385select count(*) from t2; 2386count(*) 23873 2388# 2389# Switching to connection 'mdl_con1'. 2390# Create pending X lock on t2. 2391# Sending: 2392rename table t2 to t3;; 2393# 2394# Switching to connection 'default'. 2395# Wait until RENAME TABLE starts waiting with pending X lock. 2396# Check that attempt to acquire SR lock on t2 causes waiting. 2397# Sending: 2398select count(*) from t2;; 2399# 2400# Switching to connection 'mdl_con2'. 2401# Check that the above SELECT is blocked. 2402# Unblock RENAME TABLE. 2403commit; 2404# 2405# Switching to connection 'mdl_con1'. 2406# Reap RENAME TABLE. 2407# 2408# Switching to connection 'default'. 2409# Reap SELECT. 2410ERROR 42S02: Table 'test.t2' doesn't exist 2411commit; 2412rename table t3 to t2; 2413# The same test for SW lock. 2414begin; 2415select count(*) from t1; 2416count(*) 24174 2418# 2419# Switching to connection 'mdl_con2'. 2420# Start transaction which will prevent X lock from going away 2421# immediately. 2422begin; 2423select count(*) from t2; 2424count(*) 24253 2426# 2427# Switching to connection 'mdl_con1'. 2428# Create pending X lock on t2. 2429# Sending: 2430rename table t2 to t3;; 2431# 2432# Switching to connection 'default'. 2433# Wait until RENAME TABLE starts waiting with pending X lock. 2434# Check that attempt to acquire SW lock on t2 causes waiting. 2435# Sending: 2436delete from t2 limit 1;; 2437# 2438# Switching to connection 'mdl_con2'. 2439# Check that the above DELETE is blocked. 2440# Unblock RENAME TABLE. 2441commit; 2442# 2443# Switching to connection 'mdl_con1'. 2444# Reap RENAME TABLE. 2445# 2446# Switching to connection 'default'. 2447# Reap DELETE. 2448ERROR 42S02: Table 'test.t2' doesn't exist 2449commit; 2450rename table t3 to t2; 2451# 2452# Coverage for the case in which we are acquiring lock on 2453# the table which is already used in transaction and against 2454# which there is a pending X lock request. 2455# 2456# *) The first case is when transaction has only a SR lock. 2457# 2458begin; 2459select count(*) from t1; 2460count(*) 24614 2462# 2463# Switching to connection 'mdl_con1'. 2464# Sending: 2465rename table t1 to t2;; 2466# 2467# Switching to connection 'default'. 2468# Wait until RENAME TABLE is blocked creating pending request for X lock. 2469# Check that another instance of SR lock is granted without waiting. 2470select count(*) from t1; 2471count(*) 24724 2473# Attempt to wait for SW lock will lead to deadlock, thus 2474# the below statement should end with ER_LOCK_DEADLOCK error. 2475delete from t1 limit 1; 2476ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2477# Unblock RENAME TABLE. 2478commit; 2479# 2480# Switching to connection 'mdl_con1'. 2481# Reap RENAME TABLE. 2482ERROR 42S01: Table 't2' already exists 2483# 2484# Switching to connection 'default'. 2485# 2486# **) The second case is when transaction has a SW lock. 2487# 2488begin; 2489delete from t1 limit 1; 2490# 2491# Switching to connection 'mdl_con1'. 2492# Sending: 2493rename table t1 to t2;; 2494# 2495# Switching to connection 'default'. 2496# Wait until RENAME TABLE is blocked creating pending request for X lock. 2497# Check that both SR and SW locks are granted without waiting 2498# and errors. 2499select count(*) from t1; 2500count(*) 25013 2502insert into t1 values (1, 1); 2503# Unblock RENAME TABLE. 2504commit; 2505# 2506# Switching to connection 'mdl_con1'. 2507# Reap RENAME TABLE. 2508ERROR 42S01: Table 't2' already exists 2509# 2510# Switching to connection 'default'. 2511# Clean-up. 2512set debug_sync= 'RESET'; 2513drop table t1, t2; 2514# 2515# Additional coverage for some scenarios in which use of S and SR 2516# metadata locks by HANDLER statement might have caused deadlocks. 2517# 2518drop table if exists t1, t2; 2519create table t1 (i int); 2520create table t2 (j int); 2521insert into t1 values (1); 2522# 2523# First, check scenario in which we upgrade SNRW lock to X lock 2524# on a table while having HANDLER READ trying to acquire SR 2525# on the same table. 2526# 2527handler t1 open; 2528# 2529# Switching to connection 'handler_con1'. 2530lock table t1 write; 2531# Upgrade SNRW to X lock. 2532# Sending: 2533alter table t1 add column j int;; 2534# 2535# Switching to connection 'handler_con2'. 2536# Wait until ALTER is blocked during upgrade. 2537# 2538# Switching to connection 'default'. 2539# The below statement should not cause deadlock. 2540handler t1 read first;; 2541# 2542# Switching to connection 'handler_con1'. 2543# Reap ALTER TABLE. 2544unlock tables; 2545# 2546# Switching to connection 'default'. 2547# Reap HANDLER READ. 2548i j 25491 NULL 2550handler t1 close; 2551# 2552# Now, check scenario in which upgrade of SNRW lock to X lock 2553# can be blocked by HANDLER which is open in connection currently 2554# waiting to get SW lock owned by connection doing upgrade. 2555# 2556handler t1 open; 2557# 2558# Switching to connection 'handler_con1'. 2559lock table t1 write, t2 read; 2560# 2561# Switching to connection 'default'. 2562# Execute statement which will be blocked on SRO lock 2563# owned by connection 'handler_con1'. 2564# Sending: 2565insert into t2 values (1);; 2566# 2567# Switching to connection 'handler_con1'. 2568# Wait until INSERT is blocked due to SRO lock. 2569# Sending 'alter table t1 drop column j'. It should not cause 2570# deadlock. 2571alter table t1 drop column j; 2572# Switching to connection 'handler_con2'. 2573# Wait until ALTER is blocked during upgrade. 2574# 2575# Switching to connection 'default'. 2576# Reap INSERT. 2577ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2578handler t1 close; 2579# 2580# Switching to connection 'handler_con1'. 2581# Reaping 'alter table t1 drop column j' 2582unlock tables; 2583# Switching to connection 'default'. 2584# Then, check the scenario in which upgrade of SNRW lock to X 2585# lock is blocked by HANDLER which is open in connection currently 2586# waiting to get SW lock on the same table. 2587# 2588handler t1 open; 2589# 2590# Switching to connection 'handler_con1'. 2591lock table t1 write; 2592# 2593# Switching to connection 'default'. 2594# The below insert should be blocked because active SNRW lock on 't1'. 2595# Sending: 2596insert into t1 values (1);; 2597# 2598# Switching to connection 'handler_con1'. 2599# Wait until INSERT is blocked because of SNRW lock. 2600# The below ALTER TABLE will be blocked because of presence of HANDLER. 2601# Sending: 2602alter table t1 add column j int;; 2603# 2604# Switching to connection 'default'. 2605# INSERT should be chosen as victim for resolving deadlock. 2606# Reaping INSERT. 2607ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2608# Close HANDLER to unblock ALTER TABLE. 2609handler t1 close; 2610# 2611# Switching to connection 'handler_con1'. 2612# Reaping ALTER TABLE. 2613unlock tables; 2614# 2615# Switching to connection 'default'. 2616# 2617# Then, test in which upgrade of SNRW lock to X lock is blocked 2618# by HANDLER which is open in connection currently waiting to get 2619# SR lock on the table on which lock is upgraded. 2620# 2621handler t1 open; 2622# 2623# Switching to connection 'handler_con1'. 2624lock table t1 write, t2 write; 2625# 2626# Switching to connection 'default'. 2627# The below insert should be blocked because active SNRW lock on 't1'. 2628# Sending: 2629insert into t2 values (1);; 2630# 2631# Switching to connection 'handler_con1'. 2632# Wait until INSERT is blocked because of SNRW lock. 2633# The below ALTER TABLE will be blocked because of presence of HANDLER. 2634# Sending: 2635alter table t1 drop column j;; 2636# 2637# Switching to connection 'default'. 2638# INSERT should be chosen as victim for resolving deadlock. 2639# Reaping INSERT. 2640ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2641# Close HANDLER to unblock ALTER TABLE. 2642handler t1 close; 2643# 2644# Switching to connection 'handler_con1'. 2645# Reaping ALTER TABLE. 2646unlock tables; 2647# 2648# Switching to connection 'default'. 2649# 2650# Finally, check scenario in which upgrade of SNRW lock to X lock 2651# can be blocked by HANDLER which is open in connection currently 2652# waiting to get thr_lock.c lock owned by connection doing upgrade. 2653# 2654handler t1 open; 2655# 2656# Switching to connection 'handler_con1'. 2657lock table t1 write, t2 read local; 2658# 2659# Switching to connection 'default'. 2660# Execute statement which will be blocked on thr_lock.c lock 2661# owned by connection 'handler_con1'. 2662# Sending: 2663update t2 set j=3;; 2664# 2665# Switching to connection 'handler_con1'. 2666# Wait until UPDATE is blocked due to thr_lock.c lock. 2667# Sending 'alter table t1 add column j'. It should not cause 2668# deadlock. 2669alter table t1 add column j int; 2670# Switching to connection 'handler_con2'. 2671# Wait until ALTER is blocked during upgrade. 2672# 2673# Switching to connection 'default'. 2674# Reap UPDATE. 2675ERROR HY000: Wait on a lock was aborted due to a pending exclusive lock 2676handler t1 close; 2677# 2678# Switching to connection 'handler_con1'. 2679# Reaping 'alter table t1 drop column j' 2680unlock tables; 2681# 2682# Also cover situation when HANDLER READ is aborted while waiting on 2683# thr_lock.c lock in order to avoid deadlock due to another connection 2684# performing DDL on another table open by HANDLER in the first one. 2685# 2686# Switching to connection 'handler_con1'. 2687handler t1 open; 2688handler t2 open; 2689# 2690# Switching to connection 'default'. 2691lock table t1 write, t2 read local; 2692# 2693# Switching to connection 'handler_con2'. 2694# Execute statement which will be blocked on thr_lock.c lock 2695# owned by connection 'default' and will block further attempts 2696# to read from 't2. 2697# Sending: 2698update t2 set j=3;; 2699# 2700# Switching to connection 'default'. 2701# Wait until UPDATE is blocked due to thr_lock.c lock. 2702# 2703# Switching to connection 'handler_con1'. 2704# Sending 2705handler t2 read first;; 2706# 2707# Switching to connection 'default'. 2708# Wait until HANDLER READ is blocked due to pending thr_lock.c lock. 2709# ALTER TABLE t1 should not block. HANDLER t2 READ should get aborted 2710# from its wait on thr_lock.c lock and 'handler_con1' should re-open 2711# tables opened by HANDLER statemets. 2712alter table t1 drop column j; 2713unlock tables; 2714# 2715# Switching to connection 'handler_con2'. 2716# Reap UPDATE 2717# 2718# Switching to connection 'handler_con1'. 2719# Reap HANDLER READ 2720j 2721handler t1 close; 2722handler t2 close; 2723# Switching to connection 'default'. 2724# Clean-up. 2725drop tables t1, t2; 2726# 2727# Test coverage for basic deadlock detection in metadata 2728# locking subsystem. 2729# 2730drop tables if exists t0, t1, t2, t3, t4, t5; 2731set debug_sync= 'RESET'; 2732create table t1 (i int); 2733create table t2 (j int); 2734create table t3 (k int); 2735create table t4 (k int); 2736# 2737# Test for the case in which no deadlock occurs. 2738# 2739# 2740# Switching to connection 'deadlock_con1'. 2741begin; 2742insert into t1 values (1); 2743# 2744# Switching to connection 'deadlock_con2'. 2745begin; 2746insert into t2 values (1); 2747# 2748# Switching to connection 'default'. 2749# Send: 2750rename table t2 to t0, t3 to t2, t0 to t3;; 2751# 2752# Switching to connection 'deadlock_con1'. 2753# Wait until the above RENAME TABLE is blocked because it has to wait 2754# for 'deadlock_con2' which holds shared metadata lock on 't2'. 2755# The below statement should wait for exclusive metadata lock 2756# on 't2' to go away and should not produce ER_LOCK_DEADLOCK 2757# as no deadlock is possible in this situation. 2758# Send: 2759select * from t2;; 2760# 2761# Switching to connection 'deadlock_con2'. 2762# Wait until the above SELECT * FROM t2 is starts waiting 2763# for an exclusive metadata lock to go away. 2764# 2765# Unblock RENAME TABLE by releasing shared metadata lock on t2. 2766commit; 2767# 2768# Switching to connection 'default'. 2769# Reap RENAME TABLE. 2770# 2771# Switching to connection 'deadlock_con1'. 2772# Reap SELECT. 2773k 2774# 2775# Switching to connection 'default'. 2776# 2777# Let us check that in the process of waiting for conflicting lock 2778# on table 't2' to go away transaction in connection 'deadlock_con1' 2779# has not released metadata lock on table 't1'. 2780# Send: 2781rename table t1 to t0, t3 to t1, t0 to t3;; 2782# 2783# Switching to connection 'deadlock_con1'. 2784# Wait until the above RENAME TABLE is blocked because it has to wait 2785# for 'deadlock_con1' which should still hold shared metadata lock on 2786# table 't1'. 2787# Commit transaction to unblock RENAME TABLE. 2788commit; 2789# 2790# Switching to connection 'default'. 2791# Reap RENAME TABLE. 2792# 2793# Test for case when deadlock occurs and should be detected immediately. 2794# 2795# 2796# Switching to connection 'deadlock_con1'. 2797begin; 2798insert into t2 values (2); 2799# 2800# Switching to connection 'default'. 2801# Send: 2802rename table t2 to t0, t1 to t2, t0 to t1;; 2803# 2804# Switching to connection 'deadlock_con1'. 2805# Wait until the above RENAME TABLE is blocked because it has to wait 2806# for 'deadlock_con1' which holds shared metadata lock on 't2'. 2807# 2808# The below statement should not wait as doing so will cause deadlock. 2809# Instead it should fail and emit ER_LOCK_DEADLOCK statement and 2810# transaction should be rolled back. 2811select * from t1; 2812ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2813# 2814# Switching to connection 'default'. 2815# Reap RENAME TABLE. 2816# 2817# Test for the case in which deadlock also occurs but not immediately. 2818# 2819# 2820# Switching to connection 'deadlock_con1'. 2821begin; 2822insert into t2 values (1); 2823# 2824# Switching to connection 'default'. 2825lock table t1 write; 2826# 2827# Switching to connection 'deadlock_con1'. 2828# The below SELECT statement should wait for metadata lock 2829# on table 't1' and should not produce ER_LOCK_DEADLOCK 2830# immediately as no deadlock is possible at the moment. 2831select * from t1;; 2832# 2833# Switching to connection 'deadlock_con2'. 2834# Wait until the above SELECT * FROM t1 is starts waiting 2835# for an UNRW metadata lock to go away. 2836# Send RENAME TABLE statement that will deadlock with the 2837# SELECT statement and thus should abort the latter. 2838rename table t1 to t0, t2 to t1, t0 to t2;; 2839# 2840# Switching to connection 'default'. 2841# Wait till above RENAME TABLE is blocked while holding 2842# pending X lock on t1. 2843# Allow the above RENAME TABLE to acquire lock on t1 and 2844# create pending lock on t2 thus creating deadlock. 2845unlock tables; 2846# 2847# Switching to connection 'deadlock_con1'. 2848# Since the latest RENAME TABLE entered in deadlock with SELECT 2849# statement the latter should be aborted and emit ER_LOCK_DEADLOCK 2850# error and transaction should be rolled back. 2851# Reap SELECT * FROM t1. 2852ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2853# 2854# Switching to connection 'deadlock_con2'. 2855# Reap RENAME TABLE ... . 2856# 2857# Switching to connection 'default'. 2858drop tables t1, t2, t3, t4; 2859# 2860# Now, test case which shows that deadlock detection empiric 2861# also takes into account requests for metadata lock upgrade. 2862# 2863create table t1 (i int); 2864insert into t1 values (1); 2865# Avoid race which occurs when SELECT in 'deadlock_con1' connection 2866# accesses table before the above INSERT unlocks the table and thus 2867# its result becomes visible to other connections. 2868select * from t1; 2869i 28701 2871# 2872# Switching to connection 'deadlock_con1'. 2873begin; 2874select * from t1; 2875i 28761 2877# 2878# Switching to connection 'default'. 2879# Send: 2880alter table t1 add column j int, rename to t2;; 2881# 2882# Switching to connection 'deadlock_con1'. 2883# Wait until the above ALTER TABLE ... RENAME acquires exclusive 2884# metadata lock on 't2' and starts waiting for connection 2885# 'deadlock_con1' which holds shared lock on 't1'. 2886# The below statement should not wait as it will cause deadlock. 2887# An appropriate error should be reported instead and transaction 2888# should be rolled back. 2889select * from t2; 2890ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2891# 2892# Switching to connection 'default'. 2893# Reap ALTER TABLE ... RENAME. 2894drop table t2; 2895# 2896# Test that in situation when MDL subsystem detects a deadlock 2897# but it turns out that it can be resolved by backing-off locks 2898# acquired by one of participating transactions (which is 2899# possible when one of transactions consists only of currently 2900# executed statement, e.g. in autocommit mode) no error is 2901# reported. 2902# 2903create table t1 (i int); 2904create table t2 (j int); 2905# Ensure that the below SELECT stops once it has acquired metadata 2906# lock on table 't2'. 2907set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2908# Sending: 2909select * from t2, t1; 2910# 2911# Switching to connection 'deadlock_con1'. 2912# Wait till SELECT acquires MDL on 't2' and starts waiting for signal. 2913set debug_sync= 'now WAIT_FOR locked'; 2914# Sending: 2915lock tables t1 write, t2 write; 2916# 2917# Switching to connection 'deadlock_con2'. 2918# Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked 2919# while trying to acquire SNRW lock on 't1'. 2920# Resume SELECT execution, this should eventually unblock LOCK TABLES. 2921set debug_sync= 'now SIGNAL finish'; 2922# 2923# Switching to connection 'deadlock_con1'. 2924# Reaping LOCK TABLES. 2925unlock tables; 2926# 2927# Switching to connection 'default'. 2928# Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error. 2929j i 2930drop tables t1, t2; 2931# 2932# Test coverage for situation in which a race has happened 2933# during deadlock detection process which led to unwarranted 2934# ER_LOCK_DEADLOCK error. 2935# 2936create table t1 (i int); 2937# Ensure that ALTER waits once it has acquired SNW lock. 2938set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; 2939# Sending: 2940alter table t1 add column j int; 2941# 2942# Switching to connection 'deadlock_con1'. 2943# Wait till ALTER acquires SNW lock and stops. 2944set debug_sync='now WAIT_FOR parked1'; 2945# Ensure that INSERT is paused once it detects that there is 2946# a conflicting metadata lock so it has to wait, but before 2947# deadlock detection is run. 2948set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2'; 2949# Sending: 2950insert into t1 values (); 2951# 2952# Switching to connection 'deadlock_con2'. 2953# Wait till INSERT is paused. 2954set debug_sync='now WAIT_FOR parked2'; 2955# Resume ALTER execution. Eventually it will release its 2956# metadata lock and INSERT's request for SW lock will be 2957# satisified. 2958set debug_sync='now SIGNAL go1'; 2959# 2960# Switching to connection 'default'. 2961# Reaping ALTER TABLE. 2962# Add a new request for SNW lock to waiting graph. 2963# Sending: 2964alter table t1 drop column j; 2965# 2966# Switching to connection 'deadlock_con2'. 2967# Wait until ALTER is blocked. 2968# Resume INSERT so it can start deadlock detection. 2969# 2970# At this point there is a discrepancy between the fact that INSERT's 2971# SW lock is already satisfied, but INSERT's connection is still 2972# marked as waiting for it. Looking for a loop in waiters graph 2973# without additional checks has detected a deadlock (INSERT waits 2974# for SW lock; which is not granted because of pending SNW lock from 2975# ALTER; which waits for active SW lock from INSERT). Since requests 2976# for SW and SNW locks have same weight ALTER was selected as a victim 2977# and ended with ER_LOCK_DEADLOCK error. 2978set debug_sync='now SIGNAL go2'; 2979# 2980# Switching to connection 'deadlock_con1'. 2981# Reaping INSERT. 2982# 2983# Switching to connection 'default'. 2984# Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK. 2985drop table t1; 2986# 2987# Now, test for a situation in which deadlock involves waiting not 2988# only in MDL subsystem but also for TDC. Such deadlocks should be 2989# successfully detected. If possible, they should be resolved without 2990# resorting to ER_LOCK_DEADLOCK error. 2991# 2992create table t1(i int); 2993create table t2(j int); 2994# 2995# First, let us check how we handle a simple scenario involving 2996# waits in MDL and TDC. 2997# 2998set debug_sync= 'RESET'; 2999# Switching to connection 'deadlock_con1'. 3000# Start a statement, which will acquire SR metadata lock on t1, open it 3001# and then stop, before trying to acquire SW lock on t2 and opening it. 3002set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 3003# Sending: 3004select * from t1 where i in (select j from t2 for update); 3005# Switching to connection 'deadlock_con2'. 3006# Wait till the above SELECT stops. 3007set debug_sync='now WAIT_FOR parked'; 3008# The below FLUSH TABLES WITH READ LOCK should acquire 3009# SNW locks on t1 and t2 and wait till SELECT closes t1. 3010# Sending: 3011flush tables t1, t2 with read lock; 3012# Switching to connection 'deadlock_con3'. 3013# Wait until FLUSH TABLES WITH t1, t2 READ LOCK starts waiting 3014# for SELECT to close t1. 3015# Resume SELECT, so it tries to acquire SW lock on t1 and blocks, 3016# creating a deadlock. This deadlock should be detected and resolved 3017# by backing-off SELECT. As a result FTWRL should be able to finish. 3018set debug_sync='now SIGNAL go'; 3019# Switching to connection 'deadlock_con2'. 3020# Reap FLUSH TABLES WITH READ LOCK. 3021unlock tables; 3022# Switching to connection 'deadlock_con1'. 3023# Reap SELECT. 3024i 3025# 3026# The same scenario with a slightly different order of events 3027# which emphasizes that setting correct deadlock detector weights 3028# for flush waits is important. 3029# 3030set debug_sync= 'RESET'; 3031# Switching to connection 'deadlock_con2'. 3032set debug_sync='flush_tables_with_read_lock_after_acquire_locks SIGNAL parked WAIT_FOR go'; 3033# The below FLUSH TABLES WITH READ LOCK should acquire 3034# SNW locks on t1 and t2 and wait on debug sync point. 3035# Sending: 3036flush tables t1, t2 with read lock; 3037# Switching to connection 'deadlock_con1'. 3038# Wait till FLUSH TABLE WITH READ LOCK stops. 3039set debug_sync='now WAIT_FOR parked'; 3040# Start statement which will acquire SR metadata lock on t1, open 3041# it and then will block while trying to acquire SW lock on t2. 3042# Sending: 3043select * from t1 where i in (select j from t2 for update); 3044# Switching to connection 'deadlock_con3'. 3045# Wait till the above SELECT blocks. 3046# Resume FLUSH TABLES, so it tries to flush t1, thus creating 3047# a deadlock. This deadlock should be detected and resolved by 3048# backing-off SELECT. As a result FTWRL should be able to finish. 3049set debug_sync='now SIGNAL go'; 3050# Switching to connection 'deadlock_con2'. 3051# Reap FLUSH TABLES WITH READ LOCK. 3052unlock tables; 3053# Switching to connection 'deadlock_con1'. 3054# Reap SELECT. 3055i 3056# 3057# Now a more complex scenario involving two connections 3058# waiting for MDL and one for TDC. 3059# 3060set debug_sync= 'RESET'; 3061# Switching to connection 'deadlock_con1'. 3062# Start a statement which will acquire SR metadata lock on t2, open it 3063# and then stop, before trying to acquire SR on t1 and opening it. 3064set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 3065# Sending: 3066select * from t2, t1; 3067# Switching to connection 'deadlock_con2'. 3068# Wait till the above SELECT stops. 3069set debug_sync='now WAIT_FOR parked'; 3070# The below FLUSH TABLES WITH READ LOCK should acquire 3071# SNW locks on t2 and wait till SELECT closes t2. 3072# Sending: 3073flush tables t2 with read lock; 3074# Switching to connection 'deadlock_con3'. 3075# Wait until FLUSH TABLES WITH READ LOCK starts waiting 3076# for SELECT to close t2. 3077# The below DROP TABLES should acquire X lock on t1 and start 3078# waiting for X lock on t2. 3079# Sending: 3080drop tables t1, t2; 3081# Switching to connection 'default'. 3082# Wait until DROP TABLES starts waiting for X lock on t2. 3083# Resume SELECT, so it tries to acquire SR lock on t1 and blocks, 3084# creating a deadlock. This deadlock should be detected and resolved 3085# by backing-off SELECT. As a result, FTWRL should be able to finish. 3086set debug_sync='now SIGNAL go'; 3087# Switching to connection 'deadlock_con2'. 3088# Reap FLUSH TABLES WITH READ LOCK. 3089# Unblock DROP TABLES. 3090unlock tables; 3091# Switching to connection 'deadlock_con3'. 3092# Reap DROP TABLES. 3093# Switching to connection 'deadlock_con1'. 3094# Reap SELECT. It should emit error about missing table. 3095ERROR 42S02: Table 'test.t2' doesn't exist 3096# Switching to connection 'default'. 3097set debug_sync= 'RESET'; 3098# 3099# Test coverage for scenario when deadlock is caused by LOCK TABLES 3100# implicitly acquiring "strong" metadata lock in order that is 3101# different from one used by other DDL. 3102# 3103# Other DDL should not be chosen as a deadlock victim in this case. 3104# Instead LOCK TABLES should be chosen as a victim and deadlock 3105# should be handled by executing back-off and retry of lock 3106# acquisition, without reporting any error to user. 3107create table t1(i int); 3108create table t2(j int); 3109create table t3(j int); 3110create trigger t3_bi before insert on t3 for each row insert into t1 values (1); 3111# 3112# Switching to connection 'deadlock_con1'. 3113begin; 3114select * from t2; 3115j 3116# 3117# Switching to connection 'deadlock_con2'. 3118# Send: 3119rename table t1 to t0, t2 to t1, t3 to t2, t0 to t3; 3120# 3121# Switching to connection 'deadlock_con3'. 3122# Wait until the above RENAME TABLE is blocked because it has to wait 3123# for 'deadlock_con1' which holds SR lock on 't2'. At this point it 3124# should already acquire X lock on 't0' and 't1'. 3125# The below statement should acquire SNRW lock on 't3' and got 3126# blocked trying to acquire SNRW lock on 't1'. 3127# Sending: 3128lock table t3 write;; 3129# 3130# Switching to connection 'deadlock_con1'. 3131# Wait until the above LOCK TABLE WRITE starts waiting. 3132# 3133# Unblock RENAME TABLE by releasing SR on t2. 3134commit; 3135# 3136# Switching to connection 'deadlock_con2'. 3137# Reap RENAME TABLE. It should succeed. 3138# 3139# Switching to connection 'deadlock_con3'. 3140# Reap LOCK TABLES WRITE. It should succeed too. 3141# New version of 't3' should be visible. 3142select * from t3; 3143i 3144unlock table; 3145# 3146# Switching to connection 'default'. 3147# Clean-up. 3148rename table t3 to t0, t2 to t3, t1 to t2, t0 to t1; 3149drop trigger t3_bi; 3150drop tables t1, t2, t3; 3151# 3152# Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK 3153# used to erroneously release metadata locks. 3154# 3155drop tables if exists t1, t2; 3156set debug_sync= 'RESET'; 3157create table t1(i int); 3158create table t2(j int); 3159# Switching to connection 'con2'. 3160set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 3161# The below FLUSH TABLES <list> WITH READ LOCK should acquire 3162# SNW locks on t1 and t2, open table t1 and block on the debug 3163# sync point. 3164# Sending: 3165flush tables t1, t2 with read lock; 3166# Switching to connection 'con1'. 3167# Wait till FLUSH TABLES <list> WITH READ LOCK stops. 3168set debug_sync='now WAIT_FOR parked'; 3169# Start a statement which will flush all tables and thus 3170# invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK. 3171# Sending: 3172flush tables; 3173# Switching to connection 'default'. 3174# Wait till the above FLUSH TABLES blocks. 3175# Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2 3176# discovers that its t1 is obsolete and tries to reopen all tables. 3177# Such reopen should not cause releasing of SNW metadata locks 3178# which would result in assertion failures. 3179set debug_sync='now SIGNAL go'; 3180# Switching to connection 'con2'. 3181# Reap FLUSH TABLES <list> WITH READ LOCK. 3182unlock tables; 3183# Switching to connection 'con1'. 3184# Reap FLUSH TABLES. 3185# Clean-up. 3186# Switching to connection 'default'. 3187drop tables t1, t2; 3188set debug_sync= 'RESET'; 3189# 3190# Test for bug #46748 "Assertion in MDL_context::wait_for_locks() 3191# on INSERT + CREATE TRIGGER". 3192# 3193drop tables if exists t1, t2, t3, t4, t5; 3194# Let us simulate scenario in which we open some tables from extended 3195# part of prelocking set but then encounter conflicting metadata lock, 3196# so have to back-off and wait for it to go away. 3197create table t1 (i int); 3198create table t2 (j int); 3199create table t3 (k int); 3200create table t4 (l int); 3201create trigger t1_bi before insert on t1 for each row 3202insert into t2 values (new.i); 3203create trigger t2_bi before insert on t2 for each row 3204insert into t3 values (new.j); 3205# 3206# Switching to connection 'con1root'. 3207lock tables t4 read; 3208# 3209# Switching to connection 'con2root'. 3210# Send : 3211rename table t3 to t5, t4 to t3;; 3212# 3213# Switching to connection 'default'. 3214# Wait until the above RENAME TABLE adds pending requests for exclusive 3215# metadata lock on its tables and blocks due to 't4' being used by LOCK 3216# TABLES. 3217# Send : 3218insert into t1 values (1);; 3219# 3220# Switching to connection 'con1root'. 3221# Wait until INSERT statement waits due to encountering pending 3222# exclusive metadata lock on 't3'. 3223unlock tables; 3224# 3225# Switching to connection 'con2root'. 3226# Reap RENAME TABLE. 3227# 3228# Switching to connection 'default'. 3229# Reap INSERT. 3230# Clean-up. 3231drop tables t1, t2, t3, t5; 3232# 3233# Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table 3234# 3235DROP TABLE IF EXISTS t1; 3236set @save_log_output=@@global.log_output; 3237set global log_output=file; 3238# 3239# Test 1: CREATE TABLE 3240# 3241# Connection 2 3242# Start insert on the not-yet existing table 3243# Wait after taking the MDL lock 3244SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 3245INSERT INTO t1 VALUES(1,"def"); 3246# Connection 1 3247SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3248# Now INSERT has a MDL on the non-existent table t1. 3249# 3250# Continue the INSERT once CREATE waits for exclusive lock 3251SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 3252# Try to create that table. 3253CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)); 3254# Connection 2 3255# Insert fails 3256ERROR 42S02: Table 'test.t1' doesn't exist 3257# Connection 1 3258SET DEBUG_SYNC= 'RESET'; 3259SHOW TABLES; 3260Tables_in_test 3261t1 3262DROP TABLE IF EXISTS t1; 3263# 3264# Test 2: CREATE TABLE LIKE 3265# 3266CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1)); 3267# Connection 2 3268# Start insert on the not-yet existing table 3269# Wait after taking the MDL 3270SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 3271INSERT INTO t1 VALUES(1,"def"); 3272# Connection 1 3273SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3274# Now INSERT has a MDL on the non-existent table t1. 3275# 3276# Continue the INSERT once CREATE waits for exclusive lock 3277SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 3278# Try to create that table. 3279CREATE TABLE t1 LIKE t2; 3280# Connection 2 3281# Insert fails 3282ERROR 42S02: Table 'test.t1' doesn't exist 3283# Connection 1 3284SET DEBUG_SYNC= 'RESET'; 3285SHOW TABLES; 3286Tables_in_test 3287t1 3288t2 3289DROP TABLE t2; 3290DROP TABLE IF EXISTS t1; 3291set global log_output=@save_log_output; 3292# 3293# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY 3294# FOR UPDATE" 3295# 3296drop tables if exists t1, t2; 3297create table t1 (i int); 3298insert into t1 values(1); 3299# Let us check that we won't deadlock if during filling 3300# of I_S table we encounter conflicting metadata lock 3301# which owner is in its turn waiting for our connection. 3302lock tables t1 read; 3303# Switching to connection 'con46044_2'. 3304# Sending: 3305lock tables t1 write;; 3306# Switching to connection 'con46044'. 3307# Waiting until LOCK TABLES WRITE is blocked. 3308# Sending: 3309create table t2 select * from t1;; 3310# Switching to connection 'default'. 3311# Waiting until CREATE TABLE ... SELECT ... is blocked. 3312# First let us check that SHOW FIELDS/DESCRIBE doesn't 3313# gets blocked and emits and error. 3314show fields from t2; 3315ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 3316# Now test for I_S query which reads only .FRMs. 3317# 3318# Query below should only emit a warning. 3319select column_name from information_schema.columns 3320where table_schema='test' and table_name='t2'; 3321column_name 3322Warnings: 3323Warning 1684 Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 3324# Finally, test for I_S query which does full-blown table open. 3325# 3326# Query below should not be blocked. Warning message should be 3327# stored in the 'table_comment' column. 3328select table_name, table_type, auto_increment, table_comment 3329from information_schema.tables where table_schema='test' and table_name='t2'; 3330table_name table_type auto_increment table_comment 3331t2 BASE TABLE NULL Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 3332Warnings: 3333Warning 1684 Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 3334# Switching to connection 'default'. 3335unlock tables; 3336# Switching to connection 'con46044_2'. 3337# Reaping LOCK TABLES WRITE 3338unlock tables; 3339# Switching to connection 'con46044'. 3340# Reaping CREATE TABLE ... SELECT ... . 3341drop table t2; 3342# 3343# Let us also check that queries to I_S wait for conflicting metadata 3344# locks to go away instead of skipping table with a warning in cases 3345# when deadlock is not possible. This is a nice thing from compatibility 3346# and ease of use points of view. 3347# 3348# We check same three queries to I_S in this new situation. 3349# Switching to connection 'con46044_2'. 3350lock tables t1 write; 3351# Switching to connection 'con46044'. 3352# Sending: 3353create table t2 select * from t1;; 3354# Switching to connection 'default'. 3355# Waiting until CREATE TABLE ... SELECT ... is blocked. 3356# Let us check that SHOW FIELDS/DESCRIBE gets blocked. 3357# Sending: 3358show fields from t2;; 3359# Switching to connection 'con46044_2'. 3360# Wait until SHOW FIELDS gets blocked. 3361unlock tables; 3362# Switching to connection 'con46044'. 3363# Reaping CREATE TABLE ... SELECT ... . 3364# Switching to connection 'default'. 3365# Reaping SHOW FIELDS ... 3366Field Type Null Key Default Extra 3367i int(11) YES NULL 3368drop table t2; 3369# Switching to connection 'con46044_2'. 3370lock tables t1 write; 3371# Switching to connection 'con46044'. 3372# Sending: 3373create table t2 select * from t1;; 3374# Switching to connection 'default'. 3375# Waiting until CREATE TABLE ... SELECT ... is blocked. 3376# Check that I_S query which reads only .FRMs gets blocked. 3377# Sending: 3378select column_name from information_schema.columns where table_schema='test' and table_name='t2';; 3379# Switching to connection 'con46044_2'. 3380# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked. 3381unlock tables; 3382# Switching to connection 'con46044'. 3383# Reaping CREATE TABLE ... SELECT ... . 3384# Switching to connection 'default'. 3385# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS 3386column_name 3387i 3388drop table t2; 3389# Switching to connection 'con46044_2'. 3390lock tables t1 write; 3391# Switching to connection 'con46044'. 3392# Sending: 3393create table t2 select * from t1;; 3394# Switching to connection 'default'. 3395# Waiting until CREATE TABLE ... SELECT ... is blocked. 3396# Finally, check that I_S query which does full-blown table open 3397# also gets blocked. 3398# Sending: 3399select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';; 3400# Switching to connection 'con46044_2'. 3401# Wait until SELECT ... FROM I_S.TABLES gets blocked. 3402unlock tables; 3403# Switching to connection 'con46044'. 3404# Reaping CREATE TABLE ... SELECT ... . 3405# Switching to connection 'default'. 3406# Reaping SELECT ... FROM I_S.TABLES 3407table_name table_type auto_increment table_comment 3408t2 BASE TABLE NULL 3409drop table t2; 3410# Switching to connection 'default'. 3411# Clean-up. 3412drop table t1; 3413# 3414# Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed 3415# in case of ALTER". 3416# 3417drop table if exists t1; 3418set debug_sync= 'RESET'; 3419create table t1 (c1 int primary key, c2 int, c3 int); 3420insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); 3421begin; 3422select * from t1 where c2 = 3; 3423c1 c2 c3 34243 3 0 3425# 3426# Switching to connection 'con46273'. 3427set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; 3428alter table t1 add column e int, rename to t2;; 3429# 3430# Switching to connection 'default'. 3431set debug_sync='now WAIT_FOR alter_table_locked'; 3432set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go'; 3433# The below statement should get ER_LOCK_DEADLOCK error 3434# (i.e. it should not allow ALTER to proceed, and then 3435# fail due to 't1' changing its name to 't2'). 3436update t1 set c3=c3+1 where c2 = 3; 3437ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 3438# 3439# Switching to connection 'con46273'. 3440# Reap ALTER TABLE. 3441# 3442# Switching to connection 'default'. 3443# Clean-up. 3444set debug_sync= 'RESET'; 3445drop table t2; 3446# 3447# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK 3448# and DML". 3449# 3450drop tables if exists t1; 3451create table t1 (i int); 3452# Switching to connection 'con46673'. 3453begin; 3454insert into t1 values (1); 3455# Switching to connection 'default'. 3456# Statement below should not get blocked. And if after some 3457# changes to code it is there should not be a deadlock between 3458# it and transaction from connection 'con46673'. 3459flush tables with read lock; 3460unlock tables; 3461# Switching to connection 'con46673'. 3462delete from t1 where i = 1; 3463commit; 3464# Switching to connection 'default'. 3465# Clean-up 3466drop table t1; 3467# 3468# Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks 3469# against concurrent CREATE PROCEDURE 3470# 3471# Test 1: CREATE PROCEDURE 3472# Connection 1 3473# Start CREATE PROCEDURE and open mysql.proc 3474SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 3475CREATE PROCEDURE p1() SELECT 1; 3476# Connection 2 3477SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3478# Check that FLUSH must wait to get the GRL 3479# and let CREATE PROCEDURE continue 3480SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3481FLUSH TABLES WITH READ LOCK; 3482# Connection 1 3483# Connection 2 3484UNLOCK TABLES; 3485# Connection 1 3486SET DEBUG_SYNC= 'RESET'; 3487# Test 2: DROP PROCEDURE 3488# Start DROP PROCEDURE and open tables 3489SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 3490DROP PROCEDURE p1; 3491# Connection 2 3492SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3493# Check that FLUSH must wait to get the GRL 3494# and let DROP PROCEDURE continue 3495SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3496FLUSH TABLES WITH READ LOCK; 3497# Connection 1 3498# Once FLUSH TABLES WITH READ LOCK starts waiting 3499# DROP PROCEDURE will be waked up and will drop 3500# procedure. Global read lock will be granted after 3501# this statement ends. 3502# 3503# Reaping DROP PROCEDURE. 3504# Connection 2 3505# Reaping FTWRL. 3506UNLOCK TABLES; 3507# Connection 1 3508SET DEBUG_SYNC= 'RESET'; 3509# 3510# Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null' 3511# failed in open_ltable() 3512# 3513# Supress warnings written to the log file 3514call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock"); 3515DROP TABLE IF EXISTS t1, t2; 3516CREATE TABLE t1 (i INT); 3517CREATE TABLE t2 (i INT); 3518SET @old_general_log= @@global.general_log; 3519SET @@global.general_log= 1; 3520SET @old_log_output= @@global.log_output; 3521SET @@global.log_output= 'TABLE'; 3522SET @old_sql_log_off= @@session.sql_log_off; 3523SET @@session.sql_log_off= 1; 3524# connection: con1 3525HANDLER t1 OPEN; 3526# connection: con3 3527SET @@session.sql_log_off= 1; 3528# connection: con2 3529SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; 3530# Sending: 3531SELECT 1; 3532# connection: con3 3533SET DEBUG_SYNC= 'now WAIT_FOR parked'; 3534# connection: con1 3535# Sending: 3536SELECT 1; 3537# connection: con3 3538ALTER TABLE t1 ADD COLUMN j INT; 3539# connection: default 3540SET DEBUG_SYNC= 'now SIGNAL go'; 3541# connection: con1 3542# Reaping SELECT 1 35431 35441 3545HANDLER t1 CLOSE; 3546# connection: con2 3547# Reaping SELECT 1 35481 35491 3550# connection: default 3551DROP TABLE t1, t2; 3552SET DEBUG_SYNC= 'RESET'; 3553SET @@global.general_log= @old_general_log; 3554SET @@global.log_output= @old_log_output; 3555SET @@session.sql_log_off= @old_sql_log_off; 3556# 3557# Additional coverage for bug #50913 "Deadlock between 3558# open_and_lock_tables_derived and MDL". The main test 3559# case is in lock_multi.test 3560# 3561drop table if exists t1; 3562set debug_sync= 'RESET'; 3563create table t1 (i int) engine=InnoDB; 3564# Switching to connection 'con50913_1'. 3565set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 3566# Sending: 3567alter table t1 add column j int, ALGORITHM=COPY; 3568# Switching to connection 'default'. 3569# Wait until ALTER TABLE gets blocked on a sync point after 3570# acquiring thr_lock.c lock. 3571set debug_sync= 'now WAIT_FOR parked'; 3572# The below statement should wait on MDL lock and not deadlock on 3573# thr_lock.c lock. 3574# Sending: 3575truncate table t1; 3576# Switching to connection 'con50913_2'. 3577# Wait until TRUNCATE TABLE is blocked on MDL lock. 3578# Unblock ALTER TABLE. 3579set debug_sync= 'now SIGNAL go'; 3580# Switching to connection 'con50913_1'. 3581# Reaping ALTER TABLE. 3582# Switching to connection 'default'. 3583# Reaping TRUNCATE TABLE. 3584set debug_sync= 'RESET'; 3585drop table t1; 3586# 3587# Test for bug #50998 "Deadlock in MDL code during test 3588# rqg_mdl_stability". 3589# Also provides coverage for the case when addition of 3590# waiting statement adds several loops in the waiters 3591# graph and therefore several searches for deadlock 3592# should be performed. 3593drop table if exists t1; 3594set debug_sync= 'RESET'; 3595create table t1 (i int); 3596# Switching to connection 'con1'. 3597begin; 3598select * from t1; 3599i 3600# Switching to connection 'con2'. 3601begin; 3602select * from t1; 3603i 3604# Switching to connection 'default'. 3605# Start ALTER TABLE which will acquire SNW lock and 3606# table lock and get blocked on sync point. 3607set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 3608# Sending: 3609alter table t1 add column j int; 3610# Switching to connection 'con1'. 3611# Wait until ALTER TABLE gets blocked on a sync point. 3612set debug_sync= 'now WAIT_FOR parked'; 3613# Sending: 3614insert into t1 values (1); 3615# Switching to connection 'con2'. 3616# Sending: 3617insert into t1 values (1); 3618# Switching to connection 'con3'. 3619# Wait until both 'con1' and 'con2' are blocked trying to acquire 3620# SW lock on the table. 3621# Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock 3622# deadlock with two loops in waiting graph will occur. Both loops 3623# should be found and DML statements in both 'con1' and 'con2' 3624# should be aborted with ER_LOCK_DEADLOCK errors. 3625set debug_sync= 'now SIGNAL go'; 3626# Switching to connection 'con1'. 3627# Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and 3628# not wait indefinitely (as it happened before the bugfix). 3629ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 3630commit; 3631# Switching to connection 'con2'. 3632# Reaping INSERT. 3633ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 3634commit; 3635# Switching to connection 'default'. 3636# Reap ALTER TABLE. 3637set debug_sync= 'RESET'; 3638drop table t1; 3639# 3640# Bug#42643: InnoDB does not support replication of TRUNCATE TABLE 3641# 3642# Ensure that a acquired lock is not given up due to a conflict. 3643# 3644DROP TABLE IF EXISTS t1; 3645CREATE TABLE t1 (a INT) ENGINE=InnoDB; 3646INSERT INTO t1 VALUES (1),(2),(3); 3647# Connection: con1 3648LOCK TABLES t1 WRITE; 3649SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate'; 3650TRUNCATE TABLE t1; 3651# Connection: default 3652SET debug_sync='now WAIT_FOR parked_truncate'; 3653# Connection: con2 3654SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show'; 3655SHOW FIELDS FROM t1; 3656# Connection: default 3657SET debug_sync='now WAIT_FOR parked_show'; 3658# Connection: con3 3659SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush'; 3660FLUSH TABLES t1; 3661# Connection: default 3662SET debug_sync='now WAIT_FOR parked_flush'; 3663SET debug_sync='now SIGNAL go_truncate'; 3664# Ensure that truncate waits for a exclusive lock 3665SET debug_sync= 'now SIGNAL go_show'; 3666# Connection: con1 (TRUNCATE) 3667# Reaping... 3668UNLOCK TABLES; 3669# Connection: con2 (SHOW FIELDS FROM t1) 3670# Reaping... 3671Field Type Null Key Default Extra 3672a int(11) YES NULL 3673# Connection: default 3674SET debug_sync= 'now SIGNAL go_flush'; 3675# Connection: con3 (FLUSH TABLES t1) 3676# Reaping... 3677# Connection: default 3678SET debug_sync= 'RESET'; 3679DROP TABLE t1; 3680# 3681# Bug#52856 concurrent show columns or show full columns causes a crash!!! 3682# 3683CREATE TABLE t1(a CHAR(255)); 3684SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed"; 3685SHOW FULL COLUMNS FROM t1; 3686SET DEBUG_SYNC= "now WAIT_FOR waiting"; 3687SHOW FULL COLUMNS FROM t1; 3688Field Type Collation Null Key Default Extra Privileges Comment 3689a char(255) latin1_swedish_ci YES NULL # 3690SET DEBUG_SYNC= "now SIGNAL completed"; 3691Field Type Collation Null Key Default Extra Privileges Comment 3692a char(255) latin1_swedish_ci YES NULL # 3693DROP TABLE t1; 3694# 3695# Tests for schema-scope locks 3696# 3697DROP DATABASE IF EXISTS db1; 3698DROP DATABASE IF EXISTS db2; 3699# Test 1: 3700# CREATE DATABASE blocks database DDL on the same database, but 3701# not database DDL on different databases. Tests X vs X lock. 3702# 3703# Connection default 3704SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3705# Sending: 3706CREATE DATABASE db1; 3707# Connection con2 3708SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3709# Sending: 3710CREATE DATABASE db1; 3711# Connection con3 3712CREATE DATABASE db2; 3713ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3714DROP DATABASE db2; 3715SET DEBUG_SYNC= 'now SIGNAL blocked'; 3716# Connection default 3717# Reaping: CREATE DATABASE db1 3718# Connection con2 3719# Reaping: CREATE DATABASE db1 3720ERROR HY000: Can't create database 'db1'; database exists 3721# Test 2: 3722# ALTER DATABASE blocks database DDL on the same database, but 3723# not database DDL on different databases. Tests X vs X lock. 3724# 3725# Connection default 3726SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3727# Sending: 3728ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3729# Connection con2 3730SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3731# Sending: 3732ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3733# Connection con3 3734CREATE DATABASE db2; 3735ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3736DROP DATABASE db2; 3737SET DEBUG_SYNC= 'now SIGNAL blocked'; 3738# Connection default 3739# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3740# Connection con2 3741# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3742# Connection default 3743SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3744# Sending: 3745ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3746# Connection con2 3747SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3748# Sending: 3749DROP DATABASE db1; 3750# Connection con3 3751SET DEBUG_SYNC= 'now SIGNAL blocked'; 3752# Connection default 3753# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3754# Connection con2 3755# Reaping: DROP DATABASE db1 3756CREATE DATABASE db1; 3757# Test 3: 3758# Two ALTER..UPGRADE of the same database are mutually exclusive, but 3759# two ALTER..UPGRADE of different databases are not. Tests X vs X lock. 3760# 3761# Connection default 3762SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3763# Sending: 3764ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; 3765# Connection con2 3766SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3767# Sending: 3768ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; 3769# Connection con3 3770ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; 3771Warnings: 3772Warning 1681 'UPGRADE DATA DIRECTORY NAME' is deprecated and will be removed in a future release. 3773SET DEBUG_SYNC= 'now SIGNAL blocked'; 3774# Connection default 3775# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 3776Warnings: 3777Warning 1681 'UPGRADE DATA DIRECTORY NAME' is deprecated and will be removed in a future release. 3778# Connection con2 3779# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 3780ERROR 42000: Unknown database '#mysql50#a-b-c' 3781DROP DATABASE `a-b-c`; 3782DROP DATABASE `a-b-c-d`; 3783# Test 4: 3784# DROP DATABASE blocks database DDL on the same database, but 3785# not database DDL on different databases. Tests X vs X lock. 3786# 3787# Connection default 3788SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3789# Sending: 3790DROP DATABASE db1; 3791# Connection con2 3792SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3793# Sending: 3794DROP DATABASE db1; 3795# Connection con3 3796CREATE DATABASE db2; 3797ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3798DROP DATABASE db2; 3799SET DEBUG_SYNC= 'now SIGNAL blocked'; 3800# Connection default 3801# Reaping: DROP DATABASE db1 3802# Connection con2 3803# Reaping: DROP DATABASE db1 3804ERROR HY000: Can't drop database 'db1'; database doesn't exist 3805# Connection default 3806CREATE DATABASE db1; 3807SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3808# Sending: 3809DROP DATABASE db1; 3810# Connection con2 3811SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3812# Sending: 3813ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3814# Connection con3 3815SET DEBUG_SYNC= 'now SIGNAL blocked'; 3816# Connection default 3817# Reaping: DROP DATABASE db1 3818# Connection con2 3819# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3820Got one of the listed errors 3821# Test 5: 3822# Locked database name prevents CREATE of tables in that database. 3823# Tests X vs IX lock. 3824# 3825# Connection default 3826CREATE DATABASE db1; 3827SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3828# Sending: 3829DROP DATABASE db1; 3830# Connection con2 3831SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3832# Sending: 3833CREATE TABLE db1.t1 (a INT); 3834# Connection con3 3835SET DEBUG_SYNC= 'now SIGNAL blocked'; 3836# Connection default 3837# Reaping: DROP DATABASE db1 3838# Connection con2 3839# Reaping: CREATE TABLE db1.t1 (a INT) 3840ERROR 42000: Unknown database 'db1' 3841# Test 6: 3842# Locked database name prevents RENAME of tables to/from that database. 3843# Tests X vs IX lock. 3844# 3845# Connection default 3846CREATE DATABASE db1; 3847CREATE TABLE db1.t1 (a INT); 3848SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3849# Sending: 3850DROP DATABASE db1; 3851# Connection con2 3852SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3853# Sending: 3854RENAME TABLE db1.t1 TO test.t1; 3855# Connection con3 3856SET DEBUG_SYNC= 'now SIGNAL blocked'; 3857# Connection default 3858# Reaping: DROP DATABASE db1 3859# Connection con2 3860# Reaping: RENAME TABLE db1.t1 TO test.t1 3861Got one of the listed errors 3862# Connection default 3863CREATE DATABASE db1; 3864CREATE TABLE test.t2 (a INT); 3865SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3866# Sending: 3867DROP DATABASE db1; 3868# Connection con2 3869SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3870# Sending: 3871RENAME TABLE test.t2 TO db1.t2; 3872# Connection con3 3873SET DEBUG_SYNC= 'now SIGNAL blocked'; 3874# Connection default 3875# Reaping: DROP DATABASE db1 3876# Connection con2 3877# Reaping: RENAME TABLE test.t2 TO db1.t2 3878Got one of the listed errors 3879DROP TABLE test.t2; 3880# Test 7: 3881# Locked database name prevents DROP of tables in that database. 3882# Tests X vs IX lock. 3883# 3884# Connection default 3885CREATE DATABASE db1; 3886CREATE TABLE db1.t1 (a INT); 3887SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3888# Sending: 3889DROP DATABASE db1; 3890# Connection con2 3891SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3892# Sending: 3893DROP TABLE db1.t1; 3894# Connection con3 3895SET DEBUG_SYNC= 'now SIGNAL blocked'; 3896# Connection default 3897# Reaping: DROP DATABASE db1 3898# Connection con2 3899# Reaping: DROP TABLE db1.t1 3900ERROR 42S02: Unknown table 'db1.t1' 3901# Connection default 3902SET DEBUG_SYNC= 'RESET'; 3903# 3904# End of tests for schema-scope locks 3905# 3906# 3907# Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) 3908# 3909CREATE DATABASE db1; 3910CREATE TABLE db1.t1(a INT); 3911# Connection default 3912FLUSH TABLE WITH READ LOCK; 3913# Connection con2 3914CREATE TABLE db1.t2(a INT); 3915# Connection default 3916UNLOCK TABLES; 3917# Connection con2 3918# Reaping CREATE TABLE db1.t2(a INT) 3919# Connection default 3920FLUSH TABLE WITH READ LOCK; 3921# Connection con2 3922ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3923# Connection default 3924UNLOCK TABLES; 3925# Connection con2 3926# Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3927# Connection default 3928FLUSH TABLE WITH READ LOCK; 3929# Connection con2 3930FLUSH TABLE WITH READ LOCK; 3931UNLOCK TABLES; 3932# Connection default 3933UNLOCK TABLES; 3934DROP DATABASE db1; 3935# 3936# Bug#56292 Deadlock with ALTER TABLE and MERGE tables 3937# 3938DROP TABLE IF EXISTS t1, t2, m1; 3939CREATE TABLE t1(a INT) engine=MyISAM; 3940CREATE TABLE t2(a INT) engine=MyISAM; 3941CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); 3942INSERT INTO t1 VALUES (1), (2); 3943INSERT INTO t2 VALUES (3), (4); 3944# Connection con1 3945# We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want 3946# to stop at the second upgrade. 3947SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; 3948# Sending: 3949ALTER TABLE m1 engine=MERGE UNION=(t2, t1); 3950# Connection con2 3951# Waiting for ALTER TABLE to try lock upgrade 3952SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3953SET DEBUG_SYNC= 'now SIGNAL continue'; 3954SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3955# Sending: 3956DELETE FROM t2 WHERE a = 3; 3957# Connection default 3958# Check that DELETE is waiting on a metadata lock and not a table lock. 3959# Now that DELETE blocks on a metadata lock, we should be able to do 3960# SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table 3961# lock request. 3962SELECT * FROM m1; 3963a 39641 39652 39663 39674 3968# Resuming ALTER TABLE 3969SET DEBUG_SYNC= 'now SIGNAL continue'; 3970# Connection con1 3971# Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) 3972# Connection con2 3973# Reaping: DELETE FROM t2 WHERE a = 3 3974# Connection default 3975DROP TABLE m1, t1, t2; 3976SET DEBUG_SYNC= 'RESET'; 3977# 3978# Bug#21021848 ASSERTION `M_STATUS == DA_ERROR' FAILED. 3979# 3980CREATE TABLE t1(c1 INT NOT NULL) ENGINE = csv; 3981CREATE TABLE t2(c1 INT NOT NULL); 3982# Emulate corruption of t1 3983LOCK TABLES t1 WRITE; 3984INSERT INTO t1 VALUES(0); 3985CHECK TABLE t1; 3986Table Op Msg_type Msg_text 3987test.t1 check error Corrupt 3988UNLOCK TABLES; 3989# Start XA txn on default 3990XA START 'test2'; 3991# Acquire SR on t2 3992SELECT * FROM t2; 3993c1 3994# Block IS query just before calling lock_table_names() (before X on t1) 3995SET DEBUG_SYNC='recover_ot_repair SIGNAL parked WAIT_FOR go'; 3996SELECT COUNT(*) > 1 FROM information_schema.key_column_usage; 3997# Create a new conncection which will compete for MDL lock 3998# Wait until default becomes blocked 3999SET DEBUG_SYNC='now WAIT_FOR parked'; 4000# Try to acquire X on t1 and t2, which will block while default 4001# holds SR on t2 4002DROP TABLES t1, t2; 4003# Create control connection 4004# Wait until con1 is blocked 4005# Wake up XA txn/IS query on default 4006SET DEBUG_SYNC='now SIGNAL go'; 4007# Disconnect control connection 4008# Switch to default connection 4009# Wait for IS query which will try repair t1 which requires X. 4010# Previously that resulted in deadlock with con1 over t1, 4011# but now t1 is skipped and a warning is issued 4012COUNT(*) > 1 40131 4014Warnings: 4015Warning 1194 Table 't1' is marked as crashed and should be repaired 4016# Finish XA txn 'test2' to release SR on t2 so that con1 4017# becomes unblocked 4018XA END 'test2'; 4019XA PREPARE 'test2'; 4020XA COMMIT 'test2'; 4021# Cleanup 4022SET DEBUG_SYNC= 'RESET'; 4023# Clean up con1 now that the XA txn has finished 4024# Wait for the now unblocked DROP t1, t2 to complete 4025# Disconnecting con1 4026# Switching to back to default at end of test case 4027# 4028# Bug#26739438 DEADLOCK ON GET_LOCK(..., 0) 4029# 4030# Run GET_LOCK(...,0) from two connections to which would previously 4031# cause deadlock 4032CREATE TABLE t1(i INT); 4033INSERT INTO t1 VALUES (0), (1); 4034# Starting con0 4035SELECT i FROM t1 WHERE i = 0 AND GET_LOCK(i, 0); 4036i 40370 4038# Starting con1 4039SELECT i FROM t1 WHERE i = 1 AND GET_LOCK(i, 0); 4040i 40411 4042# Send query which will do a 0-wait on i=0 while holding lock on i=1 4043SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL wait0 WAIT_FOR go0'; 4044SELECT i FROM t1 WHERE GET_LOCK(i, 0) AND i = 0; 4045# Switch to con0. Send query which will do a 0-wait on i=1 while 4046# holding lock on i=0 4047SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL wait1 WAIT_FOR go1'; 4048SELECT i FROM t1 WHERE GET_LOCK(i, 0) AND i = 1; 4049# Switch to default connection and wait until both con0 and con1 4050# are ready to check for deadlocks 4051SET DEBUG_SYNC='now WAIT_FOR wait0'; 4052SET DEBUG_SYNC='now WAIT_FOR wait1'; 4053# Tell both connections to proceed 4054SET DEBUG_SYNC='now SIGNAL go0'; 4055SET DEBUG_SYNC='now SIGNAL go1'; 4056# Wait for con0 4057i 4058# Wait for con1 (should not deadlock) 4059i 4060DROP TABLE t1; 4061