1# This tests Bug#16986 Deadlock condition with MyISAM tables 2# All tests are required to run with Myisam. 3# Hence MTR starts mysqld with MyISAM as default 4 5--source include/force_myisam_default.inc 6--source include/have_myisam.inc 7 8# Save the initial number of concurrent sessions 9--source include/count_sessions.inc 10 11--disable_warnings 12drop table if exists t1,t2; 13--enable_warnings 14 15# Test to see if select will get the lock ahead of low priority update 16 17connect (locker,localhost,root,,); 18connect (locker2,localhost,root,,); 19connect (reader,localhost,root,,); 20connect (writer,localhost,root,,); 21 22connection locker; 23create table t1(n int); 24insert into t1 values (1); 25connection locker2; 26select get_lock("mysqltest_lock", 100); 27connection locker; 28send 29update t1 set n = 2 and get_lock('mysqltest_lock', 100); 30connection writer; 31# Wait till above update gets blocked on a user lock. 32let $wait_condition= 33 select count(*) = 1 from information_schema.processlist 34 where state = "User lock" and info = "update t1 set n = 2 and get_lock('mysqltest_lock', 100)"; 35--source include/wait_condition.inc 36send 37update low_priority t1 set n = 4; 38connection reader; 39# Sleep a bit till the update of connection writer is in work and hangs 40let $wait_condition= 41 select count(*) = 1 from information_schema.processlist 42 where state = "Waiting for table level lock" and 43 info = "update low_priority t1 set n = 4"; 44--source include/wait_condition.inc 45send 46select n from t1; 47connection locker2; 48# Sleep a bit till the select of connection reader is in work and hangs 49let $wait_condition= 50 select count(*) = 1 from information_schema.processlist 51 where state = "Waiting for table level lock" and 52 info = "select n from t1"; 53--source include/wait_condition.inc 54select release_lock("mysqltest_lock"); 55connection locker; 56reap; 57select release_lock("mysqltest_lock"); 58connection writer; 59reap; 60connection reader; 61reap; 62drop table t1; 63 64connection locker; 65create table t1(n int); 66insert into t1 values (1); 67connection locker2; 68select get_lock("mysqltest_lock", 100); 69connection locker; 70send 71select n from t1 where get_lock('mysqltest_lock', 100); 72connection writer; 73# Wait till above select gets blocked on a user lock. 74let $wait_condition= 75 select count(*) = 1 from information_schema.processlist 76 where state = "User lock" and info = "select n from t1 where get_lock('mysqltest_lock', 100)"; 77--source include/wait_condition.inc 78send 79update low_priority t1 set n = 4; 80connection reader; 81# Sleep a bit till the update of connection writer is in work and hangs 82let $wait_condition= 83 select count(*) = 1 from information_schema.processlist 84 where state = "Waiting for table level lock" and 85 info = "update low_priority t1 set n = 4"; 86--source include/wait_condition.inc 87select n from t1; 88connection locker2; 89select release_lock("mysqltest_lock"); 90connection locker; 91reap; 92select release_lock("mysqltest_lock"); 93connection writer; 94reap; 95drop table t1; 96 97 98--echo # 99--echo # Test locking in multi-update statements. 100--echo # 101 102--echo # 103--echo # Multi-update should not be blocked by THR_LOCK locks acquired 104--echo # on table which is not updated. 105--echo # 106--enable_connect_log 107connection locker; 108create table t1 (a int, b int); 109create table t2 (c int, d int); 110insert into t1 values(1,1); 111insert into t1 values(2,2); 112insert into t2 values(1,2); 113connection locker2; 114select get_lock("mysqltest_lock", 100); 115connection locker; 116--echo # Sending: 117--send select a from t1 where get_lock('mysqltest_lock', 100) 118connection writer; 119--echo # Wait till above select gets blocked on a user lock. 120let $wait_condition= 121 select count(*) = 1 from information_schema.processlist 122 where state = "User lock" and info = "select a from t1 where get_lock('mysqltest_lock', 100)"; 123--source include/wait_condition.inc 124update t1,t2 set c=a where b=d; 125connection reader; 126select c from t2; 127connection locker2; 128select release_lock("mysqltest_lock"); 129connection locker; 130--echo # Reap select. 131--reap 132select release_lock("mysqltest_lock"); 133 134--echo # 135--echo # Indeed it should be blocked by THR_LOCK locks on table 136--echo # which is updated. 137--echo # 138connection locker2; 139select get_lock("mysqltest_lock", 100); 140connection locker; 141--echo # Sending: 142--send select c from t2 where get_lock('mysqltest_lock', 100) 143connection writer; 144--echo # Wait till above select gets blocked on a user lock. 145let $wait_condition= 146 select count(*) = 1 from information_schema.processlist 147 where state = "User lock" and info = "select c from t2 where get_lock('mysqltest_lock', 100)"; 148--source include/wait_condition.inc 149--echo # Sending: 150--send update t1,t2 set c=a where b=d; 151connection default; 152--echo # Wait till multi-update is blocked on THR_LOCK lock 153let $wait_condition= 154 select count(*) = 1 from information_schema.processlist 155 where state = "Waiting for table level lock" and 156 info = "update t1,t2 set c=a where b=d"; 157connection locker2; 158select release_lock("mysqltest_lock"); 159connection locker; 160--echo # Reap select. 161--reap 162select release_lock("mysqltest_lock"); 163connection writer; 164--echo # Reap multi-update. 165--reap 166 167--echo # 168--echo # OTOH multi-update will be blocked by concurrent LOCK TABLES READ 169--echo # on any table in the join. This is because multi-update acquires 170--echo # SW metadata locks on all tables in the join which conflicts with 171--echo # SRO metadata lock acquired by LOCK TABLES READ. 172--echo # 173 174connection locker; 175lock table t1 read; 176connection writer; 177--echo # Sending: 178--send update t1,t2 set c=a where b=d; 179connection default; 180--echo # Wait till multi-update is blocked on THR_LOCK lock 181let $wait_condition= 182 select count(*) = 1 from information_schema.processlist 183 where state = "Waiting for table metadata lock" and 184 info = "update t1,t2 set c=a where b=d"; 185connection locker; 186unlock tables; 187connection writer; 188--echo # Reap multi-update. 189--reap 190 191connection locker; 192lock table t2 read; 193connection writer; 194--echo # Sending: 195--send update t1,t2 set c=a where b=d; 196connection default; 197--echo # Wait till multi-update is blocked on THR_LOCK lock 198let $wait_condition= 199 select count(*) = 1 from information_schema.processlist 200 where state = "Waiting for table metadata lock" and 201 info = "update t1,t2 set c=a where b=d"; 202connection locker; 203unlock tables; 204connection writer; 205--echo # Reap multi-update. 206--reap 207 208--echo # 209--echo # Still if multi-update is executed under LOCK TABLES 210--echo # it will be compatible with LOCK TABLES READ on the 211--echo # table from its join which is only read. 212--echo # 213--echo # Main difference here is that LOCK TABLES preannounces 214--echo # locks which will be requested by multi-update. 215--echo # 216connection locker; 217lock table t1 read; 218connection writer; 219lock tables t1 read, t2 write; 220update t1,t2 set c=a where b=d; 221unlock tables; 222connection locker; 223unlock tables; 224 225connection default; 226drop table t1; 227drop table t2; 228--disable_connect_log 229 230 231# 232# Test problem when using locks on many tables and dropping a table that 233# is to-be-locked by another thread 234# 235# 236connection locker; 237create table t1 (a int); 238create table t2 (a int); 239lock table t1 write, t2 write; 240connection reader; 241send 242insert t1 select * from t2; 243connection locker; 244let $wait_condition= 245 select count(*) = 1 from information_schema.processlist 246 where state = "Waiting for table metadata lock" and 247 info = "insert t1 select * from t2"; 248--source include/wait_condition.inc 249drop table t2; 250unlock tables; 251connection reader; 252--error ER_NO_SUCH_TABLE 253reap; 254connection locker; 255drop table t1; 256 257# 258# Same test as above, but with the dropped table locked twice 259# 260 261connection locker; 262create table t1 (a int); 263create table t2 (a int); 264lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write; 265connection reader; 266send 267insert t1 select * from t2; 268connection locker; 269# Sleep a bit till the insert of connection reader is in work and hangs 270let $wait_condition= 271 select count(*) = 1 from information_schema.processlist 272 where state = "Waiting for table metadata lock" and 273 info = "insert t1 select * from t2"; 274--source include/wait_condition.inc 275drop table t2; 276unlock tables; 277connection reader; 278--error ER_NO_SUCH_TABLE 279reap; 280connection locker; 281drop table t1; 282 283 284--echo End of 4.1 tests 285 286# 287# Bug#9998 MySQL client hangs on USE "database" 288# 289create table t1(a int); 290lock tables t1 write; 291connection reader; 292show columns from t1; 293connection locker; 294unlock tables; 295drop table t1; 296 297# 298# Bug#16986 Deadlock condition with MyISAM tables 299# 300 301# Need a matching user in mysql.user for multi-table select 302--source include/add_anonymous_users.inc 303 304connection locker; 305USE mysql; 306LOCK TABLES columns_priv WRITE, db WRITE, user WRITE; 307FLUSH TABLES; 308# 309connection reader; 310USE mysql; 311# Note: This must be a multi-table select, otherwise the deadlock will not occur 312send 313SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; 314# 315connection locker; 316# Sleep a bit till the select of connection reader is in work and hangs 317let $wait_condition= 318 SELECT COUNT(*) = 1 FROM information_schema.processlist 319 WHERE state = "Waiting for table metadata lock" AND info = 320 "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1"; 321--source include/wait_condition.inc 322# Make test case independent from earlier grants. 323--replace_result "Table is already up to date" "OK" 324OPTIMIZE TABLES columns_priv, db, user; 325UNLOCK TABLES; 326# 327connection reader; 328reap; 329USE test; 330# 331connection locker; 332use test; 333# 334connection default; 335# 336# Test if CREATE TABLE with LOCK TABLE deadlocks. 337# 338connection writer; 339CREATE TABLE t1 (c1 int); 340LOCK TABLE t1 WRITE; 341# 342# This waits until t1 is unlocked. 343connection locker; 344send 345FLUSH TABLES WITH READ LOCK; 346# 347connection writer; 348# Sleep a bit till the flush of connection locker is in work and hangs 349let $wait_condition= 350 select count(*) = 1 from information_schema.processlist 351 where state = "Waiting for global read lock" and 352 info = "FLUSH TABLES WITH READ LOCK"; 353--source include/wait_condition.inc 354# This must not block. 355--error ER_TABLE_NOT_LOCKED 356CREATE TABLE t2 (c1 int); 357UNLOCK TABLES; 358# 359# This awakes now. 360connection locker; 361reap; 362UNLOCK TABLES; 363# 364connection default; 365DROP TABLE t1; 366# 367# Test if CREATE TABLE SELECT with LOCK TABLE deadlocks. 368# 369connection writer; 370CREATE TABLE t1 (c1 int); 371LOCK TABLE t1 WRITE; 372# 373# This waits until t1 is unlocked. 374connection locker; 375send 376FLUSH TABLES WITH READ LOCK; 377# 378# This must not block. 379connection writer; 380# Sleep a bit till the flush of connection locker is in work and hangs 381let $wait_condition= 382 select count(*) = 1 from information_schema.processlist 383 where state = "Waiting for global read lock" and 384 info = "FLUSH TABLES WITH READ LOCK"; 385--source include/wait_condition.inc 386--error ER_TABLE_NOT_LOCKED 387CREATE TABLE t2 AS SELECT * FROM t1; 388UNLOCK TABLES; 389# 390# This awakes now. 391connection locker; 392reap; 393UNLOCK TABLES; 394# 395connection default; 396DROP TABLE t1; 397 398--source include/delete_anonymous_users.inc 399 400# 401# Bug#19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock 402# 403connect (con1,localhost,root,,); 404connect (con2,localhost,root,,); 405# 406connection con1; 407CREATE DATABASE mysqltest_1; 408FLUSH TABLES WITH READ LOCK; 409# 410# With bug in place: acquire LOCK_mysql_create_table and 411# wait in wait_if_global_read_lock(). 412connection con2; 413send 414DROP DATABASE mysqltest_1; 415# 416# With bug in place: try to acquire LOCK_mysql_create_table... 417# When fixed: Reject dropping db because of the read lock. 418connection con1; 419# Wait a bit so that the session con2 is in state 420# "Waiting for global read lock" 421let $wait_condition= 422 select count(*) = 1 from information_schema.processlist 423 where state = "Waiting for global read lock" 424 and info = "DROP DATABASE mysqltest_1"; 425--source include/wait_condition.inc 426--error ER_CANT_UPDATE_WITH_READLOCK 427DROP DATABASE mysqltest_1; 428UNLOCK TABLES; 429# 430connection con2; 431reap; 432# 433connection default; 434disconnect con1; 435disconnect con2; 436# This must have been dropped by connection 2 already, 437# which waited until the global read lock was released. 438--error ER_DB_DROP_EXISTS 439DROP DATABASE mysqltest_1; 440 441# 442# Bug#17264 MySQL Server freeze 443# 444connection locker; 445# Disable warnings to allow test to run also without InnoDB 446--disable_warnings 447create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb; 448--enable_warnings 449lock tables t1 write; 450connection writer; 451send 452alter table t1 auto_increment=0; 453connection reader; 454# Wait till connection writer is blocked 455let $wait_condition= 456 select count(*) = 1 from information_schema.processlist 457 where state = "Waiting for table metadata lock" and 458 info = "alter table t1 auto_increment=0"; 459--source include/wait_condition.inc 460send 461alter table t1 auto_increment=0; 462connection locker; 463# Wait till connection reader is blocked 464let $wait_condition= 465 select count(*) = 2 from information_schema.processlist 466 where state = "Waiting for table metadata lock" and 467 info = "alter table t1 auto_increment=0"; 468--source include/wait_condition.inc 469unlock tables; 470connection writer; 471reap; 472connection reader; 473reap; 474connection locker; 475drop table t1; 476 477# 478# Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely 479# 480 481connect (con1,localhost,root,,); 482connect (con2,localhost,root,,); 483connect (con3,localhost,root,,); 484connect (con4,localhost,root,,); 485connect (con5,localhost,root,,); 486 487create table t1 (a int); 488create table t2 like t1; 489 490connection con1; 491--echo # con1 492lock tables t1 write; 493connection con2; 494--echo # con2 495send flush tables with read lock; 496connection con5; 497--echo # con5 498let $wait_condition= 499 select count(*) = 1 from information_schema.processlist 500 where state = "Waiting for global read lock" and 501 info = "flush tables with read lock"; 502--source include/wait_condition.inc 503--echo # global read lock is taken 504connection con3; 505--echo # con3 506send select * from t2 for update; 507connection con5; 508let $wait_condition= 509 select count(*) = 1 from information_schema.processlist 510 where state = "Waiting for global read lock" and 511 info = "select * from t2 for update"; 512--source include/wait_condition.inc 513--echo # waiting for release of read lock 514connection con4; 515--echo # con4 516--echo # would hang and later cause a deadlock 517flush tables t2; 518connection con1; 519--echo # clean up 520unlock tables; 521connection con2; 522--reap 523unlock tables; 524connection con3; 525--reap 526connection default; 527disconnect con5; 528disconnect con4; 529disconnect con3; 530disconnect con2; 531disconnect con1; 532 533drop table t1,t2; 534 535--echo # 536--echo # Lightweight version: 537--echo # Ensure that the wait for a GRL is done before opening tables. 538--echo # 539 540connect (con1,localhost,root,,); 541connect (con2,localhost,root,,); 542 543create table t1 (a int); 544create table t2 like t1; 545 546--echo # 547--echo # UPDATE 548--echo # 549 550connection default; 551--echo # default 552flush tables with read lock; 553connection con1; 554--echo # con1 555send update t2 set a = 1; 556connection default; 557--echo # default 558let $wait_condition= 559 select count(*) = 1 from information_schema.processlist 560 where state = "Waiting for global read lock" and 561 info = "update t2 set a = 1"; 562--source include/wait_condition.inc 563--echo # statement is waiting for release of read lock 564connection con2; 565--echo # con2 566flush table t2; 567connection default; 568--echo # default 569unlock tables; 570connection con1; 571--echo # con1 572--reap 573 574--echo # 575--echo # LOCK TABLES .. WRITE 576--echo # 577 578connection default; 579--echo # default 580flush tables with read lock; 581connection con1; 582--echo # con1 583send lock tables t2 write; 584connection default; 585--echo # default 586let $wait_condition= 587 select count(*) = 1 from information_schema.processlist 588 where state = "Waiting for global read lock" and 589 info = "lock tables t2 write"; 590--source include/wait_condition.inc 591--echo # statement is waiting for release of read lock 592connection con2; 593--echo # con2 594flush table t2; 595connection default; 596--echo # default 597unlock tables; 598connection con1; 599--echo # con1 600--reap 601unlock tables; 602 603connection default; 604disconnect con2; 605disconnect con1; 606 607drop table t1,t2; 608 609 610--echo End of 5.0 tests 611 612 613# 614# Bug#21281 Pending write lock is incorrectly removed when its 615# statement being KILLed 616# 617create table t1 (i int); 618insert into t1 values (1); 619select get_lock('mysqltest_lock', 100); 620connection locker; 621send select * from t1 where get_lock('mysqltest_lock', 100); 622connection writer; 623let $wait_condition= 624 select count(*) = 1 from information_schema.processlist 625 where state = "User lock" and 626 info = "select * from t1 where get_lock('mysqltest_lock', 100)"; 627--source include/wait_condition.inc 628send 629update t1 set i= 10; 630connection reader; 631let $wait_condition= 632 select count(*) = 1 from information_schema.processlist 633 where state = "Waiting for table level lock" and 634 info = "update t1 set i= 10"; 635--source include/wait_condition.inc 636send 637select * from t1; 638connection default; 639let $wait_condition= 640 select count(*) = 1 from information_schema.processlist 641 where state = "Waiting for table level lock" and 642 info = "select * from t1"; 643--source include/wait_condition.inc 644let $ID= `select id from information_schema.processlist 645 where state = "Waiting for table level lock" and 646 info = "update t1 set i= 10"`; 647--replace_result $ID ID 648eval kill query $ID; 649connection reader; 650--reap 651connection writer; 652--error ER_QUERY_INTERRUPTED 653--reap 654connection default; 655select release_lock('mysqltest_lock'); 656connection locker; 657--reap 658select release_lock('mysqltest_lock'); 659connection default; 660drop table t1; 661 662# 663# Bug#25856 HANDLER table OPEN in one connection lock DROP TABLE in another one 664# 665--disable_warnings 666drop table if exists t1; 667--enable_warnings 668create table t1 (a int) ENGINE=MEMORY; 669--echo --> client 2 670connection locker; 671--error ER_ILLEGAL_HA 672handler t1 open; 673--echo --> client 1 674connection default; 675drop table t1; 676 677 678# Disconnect sessions used in many subtests above 679disconnect locker; 680disconnect locker2; 681disconnect reader; 682disconnect writer; 683 684 685# 686# Bug#32395 Alter table under a impending global read lock causes a server crash 687# 688 689# 690# Test ALTER TABLE under LOCK TABLES and FLUSH TABLES WITH READ LOCK 691# 692 693--disable_warnings 694drop table if exists t1; 695--enable_warnings 696create table t1 (i int); 697connect (flush,localhost,root,,test,,); 698connection default; 699--echo connection: default 700lock tables t1 write; 701connection flush; 702--echo connection: flush 703--send flush tables with read lock; 704connection default; 705--echo connection: default 706let $wait_condition= 707 select count(*) = 1 from information_schema.processlist 708 where state = "Waiting for global read lock" and 709 info = "flush tables with read lock"; 710--source include/wait_condition.inc 711alter table t1 add column j int; 712connect (insert,localhost,root,,test,,); 713connection insert; 714--echo connection: insert 715let $wait_condition= 716 select count(*) = 1 from information_schema.processlist 717 where state = "Waiting for global read lock" and 718 info = "flush tables with read lock"; 719--source include/wait_condition.inc 720--send insert into t1 values (1,2); 721--echo connection: default 722connection default; 723let $wait_condition= 724 select count(*) = 1 from information_schema.processlist 725 where state = "Waiting for global read lock" and 726 info = "insert into t1 values (1,2)"; 727--source include/wait_condition.inc 728unlock tables; 729connection flush; 730--echo connection: flush 731--reap 732let $wait_condition= 733 select count(*) = 1 from information_schema.processlist 734 where state = "Waiting for global read lock" and 735 info = "insert into t1 values (1,2)"; 736--source include/wait_condition.inc 737select * from t1; 738unlock tables; 739connection insert; 740--reap 741connection default; 742let $wait_condition= 743 select count(*) = 1 from t1; 744--source include/wait_condition.inc 745select * from t1; 746drop table t1; 747disconnect flush; 748disconnect insert; 749 750# 751# Test that FLUSH TABLES under LOCK TABLES protects write locked tables 752# from a impending FLUSH TABLES WITH READ LOCK 753# 754 755--disable_warnings 756drop table if exists t1; 757--enable_warnings 758create table t1 (i int); 759connect (flush,localhost,root,,test,,); 760connection default; 761--echo connection: default 762lock tables t1 write; 763connection flush; 764--echo connection: flush 765--send flush tables with read lock; 766connection default; 767--echo connection: default 768let $wait_condition= 769 select count(*) = 1 from information_schema.processlist 770 where state = "Waiting for global read lock"; 771--source include/wait_condition.inc 772flush tables; 773let $wait_condition= 774 select count(*) = 1 from information_schema.processlist 775 where state = "Waiting for global read lock"; 776--source include/wait_condition.inc 777unlock tables; 778connection flush; 779--reap 780connection default; 781disconnect flush; 782drop table t1; 783 784# 785# Bug#30331 Table_locks_waited shows inaccurate values 786# 787 788--disable_warnings 789drop table if exists t1,t2; 790--enable_warnings 791create table t1 (a int); 792insert into t1 values (1); 793connect (blocker,localhost,root,,); 794connection blocker; 795select get_lock('mysqltest_lock', 100); 796connection default; 797flush status; 798send select * from t1 where get_lock('mysqltest_lock', 100); 799connect (waiter,localhost,root,,); 800connection waiter; 801let $wait_condition= 802 select count(*) = 1 from information_schema.processlist 803 where state = "User lock" and 804 info = "select * from t1 where get_lock('mysqltest_lock', 100)"; 805--source include/wait_condition.inc 806 807let $tlwa= `show status like 'Table_locks_waited'`; 808send update t1 set a= 2; 809connection blocker; 810let $wait_condition= 811 select count(*) = 1 from information_schema.processlist 812 where state = "Waiting for table level lock" and 813 info = "update t1 set a= 2"; 814--source include/wait_condition.inc 815let $tlwb= `show status like 'Table_locks_waited'`; 816select release_lock('mysqltest_lock'); 817connection waiter; 818--reap 819connection default; 820--reap 821select release_lock('mysqltest_lock'); 822drop table t1; 823disconnect blocker; 824disconnect waiter; 825--disable_query_log 826eval SET @tlwa= SUBSTRING_INDEX('$tlwa', ' ', -1); 827eval SET @tlwb= SUBSTRING_INDEX('$tlwb', ' ', -1); 828--enable_query_log 829select @tlwa < @tlwb; 830 831--echo End of 5.1 tests 832 833# 834# Test that DROP TABLES does not wait for a impending FLUSH TABLES 835# WITH READ LOCK 836# 837 838--disable_warnings 839drop table if exists t1; 840--enable_warnings 841create table t1 (i int); 842connect (flush,localhost,root,,test,,); 843connection default; 844--echo connection: default 845lock tables t1 write; 846connection flush; 847--echo connection: flush 848--send flush tables with read lock; 849connection default; 850--echo connection: default 851let $wait_condition= 852 select count(*) = 1 from information_schema.processlist 853 where state = "Waiting for global read lock"; 854--source include/wait_condition.inc 855flush tables; 856let $wait_condition= 857 select count(*) = 1 from information_schema.processlist 858 where state = "Waiting for global read lock"; 859--source include/wait_condition.inc 860drop table t1; 861connection flush; 862--reap 863connection default; 864disconnect flush; 865 866 867--echo # 868--echo # Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock". 869--echo # 870--disable_warnings 871drop table if exists t1; 872--enable_warnings 873create table t1 (c1 int primary key, c2 int, c3 int); 874insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); 875begin; 876update t1 set c3=c3+1 where c2=3; 877 878--echo # 879--echo # Switching to connection 'con46272'. 880connect (con46272,localhost,root,,test,,); 881connection con46272; 882--echo # The below ALTER TABLE statement should wait till transaction 883--echo # in connection 'default' is complete and then succeed. 884--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error. 885--echo # Sending: 886--send alter table t1 add column c4 int; 887 888--echo # 889--echo # Switching to connection 'default'. 890connection default; 891--echo # Wait until the above ALTER TABLE gets blocked because this 892--echo # connection holds SW metadata lock on table to be altered. 893let $wait_condition= 894 select count(*) = 1 from information_schema.processlist 895 where state = "Waiting for table metadata lock" and 896 info = "alter table t1 add column c4 int"; 897--source include/wait_condition.inc 898 899--echo # The below statement should succeed. It should not 900--echo # deadlock or end with ER_LOCK_DEADLOCK error. 901update t1 set c3=c3+1 where c2=4; 902 903--echo # Unblock ALTER TABLE by committing transaction. 904commit; 905 906--echo # 907--echo # Switching to connection 'con46272'. 908connection con46272; 909--echo # Reaping ALTER TABLE. 910--reap 911 912--echo # 913--echo # Switching to connection 'default'. 914connection default; 915disconnect con46272; 916drop table t1; 917 918 919--echo # 920--echo # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible 921--echo # 922 923--disable_warnings 924DROP TABLE IF EXISTS t1; 925DROP VIEW IF EXISTS v1; 926--enable_warnings 927 928--echo # 929--echo # Test 1: LOCK TABLES v1 WRITE, t1 READ; 930--echo # 931--echo # Thanks to the fact that we no longer allow DDL on tables 932--echo # which are locked for write implicitly, the exact scenario 933--echo # in which assert was failing is no longer repeatable. 934 935CREATE TABLE t1 ( f1 integer ); 936CREATE VIEW v1 AS SELECT f1 FROM t1 ; 937 938--echo # Connection 2 939connect (con2,localhost,root); 940LOCK TABLES v1 WRITE, t1 READ; 941FLUSH TABLE t1; 942disconnect con2; 943--source include/wait_until_disconnected.inc 944 945--echo # Connection 1 946connection default; 947LOCK TABLES t1 WRITE; 948FLUSH TABLE t1; # Assertion happened here 949 950UNLOCK TABLES; 951 952# Cleanup 953DROP TABLE t1; 954DROP VIEW v1; 955 956--echo # 957--echo # Test 2: LOCK TABLES t1 WRITE, v1 READ; 958--echo # 959 960CREATE TABLE t1 ( f1 integer ); 961CREATE VIEW v1 AS SELECT f1 FROM t1 ; 962 963--echo # Connection 2 964connect (con2,localhost,root); 965LOCK TABLES t1 WRITE, v1 READ; 966FLUSH TABLE t1; 967disconnect con2; 968--source include/wait_until_disconnected.inc 969 970--echo # Connection 1 971connection default; 972LOCK TABLES t1 WRITE; 973FLUSH TABLE t1; # Assertion happened here 974 975# Cleanup 976DROP TABLE t1; 977DROP VIEW v1; 978 979 980--echo # 981--echo # Test for bug #50913 "Deadlock between open_and_lock_tables_derived 982--echo # and MDL". Also see additional coverage in mdl_sync.test. 983--echo # 984--disable_warnings 985drop table if exists t1; 986drop view if exists v1; 987--enable_warnings 988connect (con50913,localhost,root); 989connection default; 990create table t1 (i int); 991create view v1 as select i from t1; 992begin; 993select * from t1; 994 995--echo # Switching to connection 'con50913'. 996connection con50913; 997--echo # Sending: 998--send alter table t1 add column j int 999 1000--echo # Switching to connection 'default'. 1001connection default; 1002--echo # Wait until ALTER TABLE gets blocked. 1003let $wait_condition= 1004 select count(*) = 1 from information_schema.processlist 1005 where state = "Waiting for table metadata lock" and 1006 info = "alter table t1 add column j int"; 1007--source include/wait_condition.inc 1008--echo # The below statement should try to acquire SW lock on 't1' 1009--echo # and therefore should get ER_LOCK_DEADLOCK error. Before 1010--echo # bug fix it acquired SR lock and hung on thr_lock.c lock. 1011--error ER_LOCK_DEADLOCK 1012delete a from t1 as a where i = 1; 1013--echo # Unblock ALTER TABLE. 1014commit; 1015 1016--echo # Switching to connection 'con50913'. 1017connection con50913; 1018--echo # Reaping ALTER TABLE; 1019--reap 1020 1021--echo # Switching to connection 'default'. 1022connection default; 1023begin; 1024select * from v1; 1025 1026--echo # Switching to connection 'con50913'. 1027connection con50913; 1028--echo # Sending: 1029--send alter table t1 drop column j 1030 1031--echo # Switching to connection 'default'. 1032connection default; 1033--echo # Wait until ALTER TABLE gets blocked. 1034let $wait_condition= 1035 select count(*) = 1 from information_schema.processlist 1036 where state = "Waiting for table metadata lock" and 1037 info = "alter table t1 drop column j"; 1038--source include/wait_condition.inc 1039--echo # The below statement should try to acquire SW lock on 't1' 1040--echo # and therefore should get ER_LOCK_DEADLOCK error. Before 1041--echo # bug fix it acquired SR lock and hung on thr_lock.c lock. 1042--error ER_LOCK_DEADLOCK 1043insert into v1 values (1); 1044--echo # Unblock ALTER TABLE. 1045commit; 1046 1047--echo # Switching to connection 'con50913'. 1048connection con50913; 1049--echo # Reaping ALTER TABLE; 1050--reap 1051 1052--echo # Switching to connection 'default'. 1053connection default; 1054disconnect con50913; 1055drop view v1; 1056drop table t1; 1057 1058 1059--echo # 1060--echo # Bug#45225 Locking: hang if drop table with no timeout 1061--echo # 1062--echo # These tests also provide function coverage for the 1063--echo # lock_wait_timeout server variable. 1064--echo # 1065 1066--disable_warnings 1067DROP TABLE IF EXISTS t1; 1068--enable_warnings 1069 1070CREATE TABLE t1 (id int); 1071 1072connect(con2, localhost, root,,); 1073SET SESSION lock_wait_timeout= 1; 1074 1075--echo # 1076--echo # Test 1: acquire exclusive lock 1077--echo # 1078 1079--echo # Connection default 1080connection default; 1081START TRANSACTION; 1082INSERT INTO t1 VALUES (1); 1083 1084--echo # Connection 2 1085connection con2; 1086--error ER_LOCK_WAIT_TIMEOUT 1087DROP TABLE t1; 1088 1089--echo # Connection default 1090connection default; 1091COMMIT; 1092 1093--echo # 1094--echo # Test 2: upgrade shared lock 1095--echo # 1096 1097--echo # Connection default 1098connection default; 1099START TRANSACTION; 1100SELECT * FROM t1; 1101 1102--echo # Connection 2 1103connection con2; 1104--error ER_LOCK_WAIT_TIMEOUT 1105ALTER TABLE t1 RENAME TO t2; 1106 1107--echo # Connection default 1108connection default; 1109COMMIT; 1110 1111--echo # 1112--echo # Test 3: acquire shared lock 1113--echo # 1114 1115--echo # Connection default 1116connection default; 1117LOCK TABLE t1 WRITE; 1118 1119--echo # Connection 2 1120connection con2; 1121--error ER_LOCK_WAIT_TIMEOUT 1122INSERT INTO t1(id) VALUES (2); 1123 1124--echo # Connection default 1125connection default; 1126UNLOCK TABLES; 1127 1128--echo # 1129--echo # Test 4: table level locks 1130--echo # 1131 1132--echo # Connection default 1133connection default; 1134LOCK TABLE t1 READ; 1135 1136--echo # Connection 2 1137connection con2; 1138--error ER_LOCK_WAIT_TIMEOUT 1139INSERT INTO t1(id) VALUES(4); 1140 1141--echo # Connection default 1142connection default; 1143UNLOCK TABLES; 1144 1145--echo # 1146--echo # Test 5: Waiting on Table Definition Cache (TDC) 1147--echo # 1148 1149connect(con3, localhost, root); 1150 1151--echo # Connection default 1152connection default; 1153LOCK TABLE t1 READ; 1154 1155--echo # Connection con3 1156connection con3; 1157--echo # Sending: 1158--send FLUSH TABLES 1159 1160--echo # Connection con2 1161connection con2; 1162let $wait_condition= 1163 SELECT COUNT(*) = 1 FROM information_schema.processlist 1164 WHERE state = "Waiting for table flush" AND info = "FLUSH TABLES"; 1165--source include/wait_condition.inc 1166--error ER_LOCK_WAIT_TIMEOUT 1167SELECT * FROM t1; 1168 1169--echo # Connection default 1170connection default; 1171UNLOCK TABLES; 1172 1173--echo # Connection con3 1174connection con3; 1175--echo # Reaping: FLUSH TABLES 1176--reap 1177 1178--echo # 1179--echo # Test 6: Timeouts in I_S queries 1180--echo # 1181 1182--echo # Connection default 1183connection default; 1184CREATE TABLE t2 (id INT); 1185LOCK TABLE t2 WRITE; 1186 1187--echo # Connection con3 1188connection con3; 1189--echo # Sending: 1190--send DROP TABLE t1, t2 1191 1192--echo # Connection con2 1193connection con2; 1194let $wait_condition= 1195 SELECT COUNT(*) = 1 FROM information_schema.processlist 1196 WHERE state = "Waiting for table metadata lock" AND 1197 info = "DROP TABLE t1, t2"; 1198--source include/wait_condition.inc 1199# Note: This query causes two timeouts. 1200# 1: try_acquire_high_prio_shared_mdl_lock on t1 1201# 2: recover_from_failed_open on t1 1202SELECT table_name, table_comment FROM information_schema.tables 1203 WHERE table_schema= 'test' AND table_name= 't1'; 1204 1205--echo # Connection default 1206connection default; 1207UNLOCK TABLES; 1208 1209--echo # Connection con3 1210connection con3; 1211--echo # Reaping: DROP TABLE t1, t2 1212--reap 1213 1214--echo # Connection default 1215connection default; 1216--echo # Cleanup 1217disconnect con2; 1218disconnect con3; 1219 1220 1221--echo # 1222--echo # Test for bug #51134 "Crash in MDL_lock::destroy on a concurrent 1223--echo # DDL workload". 1224--echo # 1225--disable_warnings 1226drop tables if exists t1, t2, t3; 1227--enable_warnings 1228connect (con1, localhost, root, , ); 1229connect (con2, localhost, root, , ); 1230connection default; 1231create table t3 (i int); 1232 1233--echo # Switching to connection 'con1' 1234connection con1; 1235--echo # Lock 't3' so upcoming RENAME is blocked. 1236lock table t3 read; 1237 1238--echo # Switching to connection 'con2' 1239connection con2; 1240--echo # Remember ID for this connection. 1241let $ID= `select connection_id()`; 1242--echo # Start statement which will try to acquire two instances 1243--echo # of X metadata lock on the same object. 1244--echo # Sending: 1245--send rename tables t1 to t2, t2 to t3; 1246 1247--echo # Switching to connection 'default' 1248connection default; 1249--echo # Wait until RENAME TABLE is blocked on table 't3'. 1250let $wait_condition= 1251 select count(*) = 1 from information_schema.processlist 1252 where state = "Waiting for table metadata lock" and 1253 info = "rename tables t1 to t2, t2 to t3"; 1254--source include/wait_condition.inc 1255--echo # Kill RENAME TABLE. 1256--replace_result $ID ID 1257eval kill query $ID; 1258 1259--echo # Switching to connection 'con2' 1260connection con2; 1261--echo # RENAME TABLE should be aborted but should not crash. 1262--error ER_QUERY_INTERRUPTED 1263--reap 1264 1265--echo # Switching to connection 'con1' 1266connection con1; 1267unlock tables; 1268 1269--echo # Switching to connection 'default' 1270connection default; 1271disconnect con1; 1272disconnect con2; 1273drop table t3; 1274 1275 1276--echo # 1277--echo # Test for the bug where upgradable metadata locks was acquired 1278--echo # even if the table to altered was temporary. 1279--echo # Bug found while working on the related bug #51240. 1280--echo # 1281 1282--disable_warnings 1283DROP TABLE IF EXISTS t1; 1284--enable_warnings 1285 1286CREATE TABLE t1 (id INT); 1287LOCK TABLE t1 WRITE; 1288 1289--echo # Connection con1 1290connect (con1, localhost, root); 1291CREATE TEMPORARY TABLE t1 (id INT); 1292# This alter should not block and timeout. 1293ALTER TABLE t1 ADD COLUMN j INT; 1294 1295--echo # Connection default 1296connection default; 1297disconnect con1; 1298UNLOCK TABLES; 1299DROP TABLE t1; 1300 1301 1302--echo # 1303--echo # Test coverage for LOCK TABLES ... READ/WRITE 1304--echo # 1305--echo # Of course this functionality is well-covered by tests all 1306--echo # around the test suite. Still it is nice to have formal 1307--echo # coverage for LOCK TABLES in one place. 1308--echo # 1309 1310--enable_connect_log 1311--echo # We are going to check behavior for both InnoDB and MyISAM 1312--echo # tables. 1313CREATE TABLE t1 (i INT) ENGINE=MyISAM; 1314CREATE TABLE t2 (i INT) ENGINE=InnoDB; 1315INSERT INTO t1 VALUES (1); 1316INSERT INTO t2 VALUES (1); 1317CREATE VIEW v1 AS SELECT * FROM t1; 1318CREATE VIEW v2 AS SELECT * FROM t2; 1319CREATE TABLE t3 (j INT); 1320CREATE TABLE t4 (j INT); 1321CREATE VIEW v3 AS SELECT * FROM t3 WHERE (SELECT COUNT(*) FROM t1); 1322CREATE VIEW v4 AS SELECT * FROM t4 WHERE (SELECT COUNT(*) FROM t2); 1323CREATE TABLE t5 (k INT); 1324CREATE TABLE t6 (k INT); 1325CREATE TRIGGER bi_t5 BEFORE INSERT ON t5 FOR EACH ROW SET @a:= (SELECT COUNT(*) FROM t1); 1326CREATE TRIGGER bi_t6 BEFORE INSERT ON t6 FOR EACH ROW SET @a:= (SELECT COUNT(*) FROM t2); 1327CREATE TABLE t7 (z INT); 1328CREATE TABLE t8 (z INT); 1329CREATE TRIGGER bi_t7 BEFORE INSERT ON t7 FOR EACH ROW INSERT INTO t1 VALUES (1); 1330CREATE TRIGGER bi_t8 BEFORE INSERT ON t8 FOR EACH ROW INSERT INTO t2 VALUES (1); 1331 1332--echo # 1333--echo # 1) LOCK TABLES READ explicitly locking table 1334--echo # 1335--echo # 1.a) Allows concurrent reads 1336LOCK TABLE t1 READ, t2 READ; 1337connect (con1, localhost, root); 1338SELECT * FROM t1; 1339SELECT * FROM t2; 1340connection default; 1341UNLOCK TABLES; 1342 1343--echo # 1344--echo # 1.b) Is allowed concurrently to reads 1345SELECT GET_LOCK('mysqltest_lock', 100); 1346 1347connection con1; 1348--echo # Start read by sending SELECT: 1349--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2; 1350 1351connect (con2, localhost, root); 1352--echo # Wait until SELECT gets read lock and starts waiting for user lock 1353let $wait_condition= 1354 select count(*) = 1 from information_schema.processlist 1355 where state = "User lock" and 1356 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2"; 1357--source include/wait_condition.inc 1358--echo # LOCK TABLES should not be blocked. 1359LOCK TABLES t1 READ, t2 READ; 1360UNLOCK TABLES; 1361 1362connection default; 1363--echo # Unblock SELECT. 1364SELECT RELEASE_LOCK('mysqltest_lock'); 1365 1366connection con1; 1367--echo # Reaping SELECT 1368--reap 1369SELECT RELEASE_LOCK('mysqltest_lock'); 1370 1371--echo # 1372--echo # 1.c) Blocks concurrent modifications to table 1373connection default; 1374 1375LOCK TABLE t1 READ, t2 READ; 1376 1377connection con1; 1378--echo # Sending: 1379--send INSERT INTO t1 VALUES (1) 1380 1381connection default; 1382--echo # Wait until INSERT gets blocked 1383let $wait_condition= 1384 select count(*) = 1 from information_schema.processlist 1385 where state = "Waiting for table metadata lock" and 1386 info = "INSERT INTO t1 VALUES (1)"; 1387--source include/wait_condition.inc 1388 1389connection con2; 1390--echo # Sending: 1391--send INSERT INTO t2 VALUES (1) 1392 1393connection default; 1394--echo # Wait until INSERT gets blocked 1395let $wait_condition= 1396 select count(*) = 1 from information_schema.processlist 1397 where state = "Waiting for table metadata lock" and 1398 info = "INSERT INTO t2 VALUES (1)"; 1399--source include/wait_condition.inc 1400 1401--echo # Unblock INSERTs 1402UNLOCK TABLES; 1403 1404connection con1; 1405--echo # Reap INSERT 1406--reap 1407 1408connection con2; 1409--echo # Reap INSERT 1410--reap 1411 1412connection default; 1413--echo # Revert effects from INSERTs 1414DELETE FROM t1 LIMIT 1; 1415DELETE FROM t2 LIMIT 1; 1416 1417--echo # 1418--echo # 1.d) Is blocked by concurrent table modifications 1419SELECT GET_LOCK('mysqltest_lock', 100); 1420 1421connection con1; 1422--echo # Sending: 1423--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 1424 1425connection con2; 1426--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 1427let $wait_condition= 1428 select count(*) = 1 from information_schema.processlist 1429 where state = "User lock" and 1430 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 1431--source include/wait_condition.inc 1432 1433--echo # Sending: 1434--send LOCK TABLES t1 READ; 1435 1436connection default; 1437--echo # Wait until LOCK TABLES READ is blocked due to INSERT 1438let $wait_condition= 1439 select count(*) = 1 from information_schema.processlist 1440 where state = "Waiting for table metadata lock" and 1441 info = "LOCK TABLES t1 READ"; 1442--source include/wait_condition.inc 1443 1444--echo # Unblock INSERT. 1445SELECT RELEASE_LOCK('mysqltest_lock'); 1446 1447connection con1; 1448--echo # Reaping INSERT 1449--reap 1450SELECT RELEASE_LOCK('mysqltest_lock'); 1451 1452connection con2; 1453--echo # Reaping LOCK TABLES READ 1454--reap 1455UNLOCK TABLES; 1456 1457connection default; 1458 1459SELECT GET_LOCK('mysqltest_lock', 100); 1460 1461--echo # 1462--echo # The below part of test also covers scenario in which bug #42147 1463--echo # "Concurrent DML and LOCK TABLE ... READ for InnoDB table cause 1464--echo # warnings in errlog" occurred. 1465--echo # 1466connection con1; 1467--echo # Sending: 1468--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 1469 1470connection con2; 1471--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 1472let $wait_condition= 1473 select count(*) = 1 from information_schema.processlist 1474 where state = "User lock" and 1475 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 1476--source include/wait_condition.inc 1477 1478--echo # Sending: 1479--send LOCK TABLES t2 READ; 1480 1481connection default; 1482--echo # Wait until LOCK TABLES READ is blocked due to INSERT 1483let $wait_condition= 1484 select count(*) = 1 from information_schema.processlist 1485 where state = "Waiting for table metadata lock" and 1486 info = "LOCK TABLES t2 READ"; 1487--source include/wait_condition.inc 1488 1489--echo # Unblock INSERT. 1490SELECT RELEASE_LOCK('mysqltest_lock'); 1491 1492connection con1; 1493--echo # Reaping INSERT 1494--reap 1495SELECT RELEASE_LOCK('mysqltest_lock'); 1496 1497connection con2; 1498--echo # Reaping LOCK TABLES READ 1499--reap 1500UNLOCK TABLES; 1501 1502connection default; 1503--echo # Revert effects from INSERTs 1504DELETE FROM t1 LIMIT 1; 1505DELETE FROM t2 LIMIT 1; 1506 1507--echo # 1508--echo # 1.e) LOCK TABLES READ which explicitly locks table is not blocked 1509--echo # by concurrent transactions which read table. 1510BEGIN; 1511SELECT * FROM t1; 1512 1513connection con1; 1514LOCK TABLES t1 READ; 1515UNLOCK TABLES; 1516 1517connection default; 1518COMMIT; 1519 1520BEGIN; 1521SELECT * FROM t2; 1522 1523connection con1; 1524LOCK TABLES t2 READ; 1525UNLOCK TABLES; 1526 1527connection default; 1528COMMIT; 1529 1530--echo # 1531--echo # 1.f) LOCK TABLES READ which explicitly locks table is blocked 1532--echo # by concurrent transactions which modify table. 1533BEGIN; 1534INSERT INTO t1 VALUES (1); 1535 1536connection con1; 1537--echo # Sending: 1538--send LOCK TABLES t1 READ; 1539 1540connection default; 1541--echo # Wait until LOCK TABLES READ is blocked due concurrent transaction 1542let $wait_condition= 1543 select count(*) = 1 from information_schema.processlist 1544 where state = "Waiting for table metadata lock" and 1545 info = "LOCK TABLES t1 READ"; 1546--source include/wait_condition.inc 1547--echo # Unblock LOCK TABLES READ 1548COMMIT; 1549 1550connection con1; 1551--echo # Reap LOCK TABLES READ 1552--reap 1553UNLOCK TABLES; 1554 1555connection default; 1556BEGIN; 1557INSERT INTO t2 VALUES (1); 1558 1559connection con1; 1560--echo # Sending: 1561--send LOCK TABLES t2 READ; 1562 1563connection default; 1564--echo # Wait until LOCK TABLES READ is blocked due concurrent transaction 1565let $wait_condition= 1566 select count(*) = 1 from information_schema.processlist 1567 where state = "Waiting for table metadata lock" and 1568 info = "LOCK TABLES t2 READ"; 1569--source include/wait_condition.inc 1570--echo # Unblock LOCK TABLES READ 1571COMMIT; 1572 1573connection con1; 1574--echo # Reap LOCK TABLES READ 1575--reap 1576UNLOCK TABLES; 1577 1578connection default; 1579--echo # Revert effects from INSERTs 1580DELETE FROM t1 LIMIT 1; 1581DELETE FROM t2 LIMIT 1; 1582 1583--echo # 1584--echo # 1.g) LOCK TABLES READ which explicitly locks table is compatible 1585--echo # with itself. 1586LOCK TABLES t1 READ, t2 READ; 1587 1588connection con1; 1589LOCK TABLES t1 READ, t2 READ; 1590UNLOCK TABLES; 1591 1592connection default; 1593UNLOCK TABLES; 1594 1595--echo # 1596--echo # 1.h) LOCK TABLES READ which explicitly locks table is not compatible 1597--echo # with LOCK TABLE WRITE. 1598LOCK TABLES t1 READ; 1599 1600connection con1; 1601--echo # Sending: 1602--send LOCK TABLES t1 WRITE; 1603 1604connection default; 1605--echo # Wait until LOCK TABLES WRITE is blocked 1606let $wait_condition= 1607 select count(*) = 1 from information_schema.processlist 1608 where state = "Waiting for table metadata lock" and 1609 info = "LOCK TABLES t1 WRITE"; 1610--source include/wait_condition.inc 1611--echo # Unblock LOCK TABLES WRITE 1612UNLOCK TABLES; 1613 1614connection con1; 1615--echo # Reap LOCK TABLES WRITE 1616--reap 1617UNLOCK TABLES; 1618 1619connection default; 1620LOCK TABLES t2 READ; 1621 1622connection con1; 1623--echo # Sending: 1624--send LOCK TABLES t2 WRITE; 1625 1626connection default; 1627--echo # Wait until LOCK TABLES WRITE is blocked 1628let $wait_condition= 1629 select count(*) = 1 from information_schema.processlist 1630 where state = "Waiting for table metadata lock" and 1631 info = "LOCK TABLES t2 WRITE"; 1632--source include/wait_condition.inc 1633--echo # Unblock LOCK TABLES WRITE 1634UNLOCK TABLES; 1635 1636connection con1; 1637--echo # Reap LOCK TABLES WRITE 1638--reap 1639UNLOCK TABLES; 1640 1641connection default; 1642LOCK TABLES t1 WRITE; 1643 1644connection con1; 1645--echo # Sending: 1646--send LOCK TABLES t1 READ; 1647 1648connection default; 1649--echo # Wait until LOCK TABLES READ is blocked 1650let $wait_condition= 1651 select count(*) = 1 from information_schema.processlist 1652 where state = "Waiting for table metadata lock" and 1653 info = "LOCK TABLES t1 READ"; 1654--source include/wait_condition.inc 1655--echo # Unblock LOCK TABLES READ 1656UNLOCK TABLES; 1657 1658connection con1; 1659--echo # Reap LOCK TABLES READ 1660--reap 1661UNLOCK TABLES; 1662 1663connection default; 1664LOCK TABLES t2 WRITE; 1665 1666connection con1; 1667--echo # Sending: 1668--send LOCK TABLES t2 READ; 1669 1670connection default; 1671--echo # Wait until LOCK TABLES READ is blocked 1672let $wait_condition= 1673 select count(*) = 1 from information_schema.processlist 1674 where state = "Waiting for table metadata lock" and 1675 info = "LOCK TABLES t2 READ"; 1676--source include/wait_condition.inc 1677--echo # Unblock LOCK TABLES READ 1678UNLOCK TABLES; 1679 1680connection con1; 1681--echo # Reap LOCK TABLES READ 1682--reap 1683UNLOCK TABLES; 1684 1685 1686--echo # 1687--echo # 2) LOCK TABLES WRITE explicitly locking table 1688--echo # 1689--echo # 2.a) Doesn't allow concurrent reads 1690connection default; 1691LOCK TABLE t1 WRITE; 1692 1693connection con1; 1694--echo # Sending: 1695--send SELECT * FROM t1; 1696 1697connection default; 1698--echo # Wait until SELECT gets blocked 1699let $wait_condition= 1700 select count(*) = 1 from information_schema.processlist 1701 where state = "Waiting for table metadata lock" and 1702 info = "SELECT * FROM t1"; 1703--source include/wait_condition.inc 1704--echo # Unblock SELECT 1705UNLOCK TABLES; 1706 1707connection con1; 1708--echo # Reaping SELECT 1709--reap 1710 1711connection default; 1712LOCK TABLE t2 WRITE; 1713 1714connection con1; 1715--echo # Sending: 1716--send SELECT * FROM t2; 1717 1718connection default; 1719--echo # Wait until SELECT gets blocked 1720let $wait_condition= 1721 select count(*) = 1 from information_schema.processlist 1722 where state = "Waiting for table metadata lock" and 1723 info = "SELECT * FROM t2"; 1724--source include/wait_condition.inc 1725--echo # Unblock SELECT 1726UNLOCK TABLES; 1727 1728connection con1; 1729--echo # Reaping SELECT 1730--reap 1731 1732connection default; 1733--echo # 1734--echo # 2.b) Is not allowed concurrently to reads 1735SELECT GET_LOCK('mysqltest_lock', 100); 1736 1737connection con1; 1738--echo # Start read by sending SELECT: 1739--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1; 1740 1741connection con2; 1742--echo # Wait until SELECT gets read lock and starts waiting for user lock 1743let $wait_condition= 1744 select count(*) = 1 from information_schema.processlist 1745 where state = "User lock" and 1746 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1"; 1747--source include/wait_condition.inc 1748--echo # Sending: 1749--send LOCK TABLES t1 WRITE; 1750 1751connection default; 1752let $wait_condition= 1753 select count(*) = 1 from information_schema.processlist 1754 where state = "Waiting for table metadata lock" and 1755 info = "LOCK TABLES t1 WRITE"; 1756--source include/wait_condition.inc 1757UNLOCK TABLES; 1758 1759--echo # Unblock SELECT. 1760SELECT RELEASE_LOCK('mysqltest_lock'); 1761 1762connection con1; 1763--echo # Reaping SELECT 1764--reap 1765SELECT RELEASE_LOCK('mysqltest_lock'); 1766 1767connection con2; 1768--echo # Reaping LOCK TABLE WRITE 1769--reap 1770UNLOCK TABLES; 1771 1772connection default; 1773SELECT GET_LOCK('mysqltest_lock', 100); 1774 1775connection con1; 1776--echo # Start read by sending SELECT: 1777--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t2; 1778 1779connection con2; 1780--echo # Wait until SELECT gets read lock and starts waiting for user lock 1781let $wait_condition= 1782 select count(*) = 1 from information_schema.processlist 1783 where state = "User lock" and 1784 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t2"; 1785--source include/wait_condition.inc 1786--echo # Sending: 1787--send LOCK TABLES t2 WRITE; 1788 1789connection default; 1790let $wait_condition= 1791 select count(*) = 1 from information_schema.processlist 1792 where state = "Waiting for table metadata lock" and 1793 info = "LOCK TABLES t2 WRITE"; 1794--source include/wait_condition.inc 1795UNLOCK TABLES; 1796 1797--echo # Unblock SELECT. 1798SELECT RELEASE_LOCK('mysqltest_lock'); 1799 1800connection con1; 1801--echo # Reaping SELECT 1802--reap 1803SELECT RELEASE_LOCK('mysqltest_lock'); 1804 1805connection con2; 1806--echo # Reaping LOCK TABLE WRITE 1807--reap 1808UNLOCK TABLES; 1809 1810 1811--echo # 1812--echo # 2.c) Blocks concurrent modifications to table 1813connection default; 1814LOCK TABLE t1 WRITE, t2 WRITE; 1815 1816connection con1; 1817--echo # Sending: 1818--send INSERT INTO t1 VALUES (1) 1819 1820connection default; 1821--echo # Wait until INSERT gets blocked 1822let $wait_condition= 1823 select count(*) = 1 from information_schema.processlist 1824 where state = "Waiting for table metadata lock" and 1825 info = "INSERT INTO t1 VALUES (1)"; 1826--source include/wait_condition.inc 1827 1828connection con2; 1829--echo # Sending: 1830--send INSERT INTO t2 VALUES (1) 1831 1832connection default; 1833--echo # Wait until INSERT gets blocked 1834let $wait_condition= 1835 select count(*) = 1 from information_schema.processlist 1836 where state = "Waiting for table metadata lock" and 1837 info = "INSERT INTO t2 VALUES (1)"; 1838--source include/wait_condition.inc 1839 1840--echo # Unblock INSERTs 1841UNLOCK TABLES; 1842 1843connection con1; 1844--echo # Reap INSERT 1845--reap 1846 1847connection con2; 1848--echo # Reap INSERT 1849--reap 1850 1851connection default; 1852--echo # Revert effects from INSERTs 1853DELETE FROM t1 LIMIT 1; 1854DELETE FROM t2 LIMIT 1; 1855 1856--echo # 1857--echo # 2.d) Is blocked by concurrent table modifications 1858SELECT GET_LOCK('mysqltest_lock', 100); 1859 1860connection con1; 1861--echo # Sending: 1862--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 1863 1864connection con2; 1865--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 1866let $wait_condition= 1867 select count(*) = 1 from information_schema.processlist 1868 where state = "User lock" and 1869 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 1870--source include/wait_condition.inc 1871 1872--echo # Sending: 1873--send LOCK TABLES t1 WRITE; 1874 1875connection default; 1876--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 1877let $wait_condition= 1878 select count(*) = 1 from information_schema.processlist 1879 where state = "Waiting for table metadata lock" and 1880 info = "LOCK TABLES t1 WRITE"; 1881--source include/wait_condition.inc 1882 1883--echo # Unblock INSERT. 1884SELECT RELEASE_LOCK('mysqltest_lock'); 1885 1886connection con1; 1887--echo # Reaping INSERT 1888--reap 1889SELECT RELEASE_LOCK('mysqltest_lock'); 1890 1891connection con2; 1892--echo # Reaping LOCK TABLES WRITE 1893--reap 1894UNLOCK TABLES; 1895 1896connection default; 1897 1898SELECT GET_LOCK('mysqltest_lock', 100); 1899 1900connection con1; 1901--echo # Sending: 1902--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 1903 1904connection con2; 1905--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 1906let $wait_condition= 1907 select count(*) = 1 from information_schema.processlist 1908 where state = "User lock" and 1909 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 1910--source include/wait_condition.inc 1911 1912--echo # Sending: 1913--send LOCK TABLES t2 WRITE; 1914 1915connection default; 1916--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 1917let $wait_condition= 1918 select count(*) = 1 from information_schema.processlist 1919 where state = "Waiting for table metadata lock" and 1920 info = "LOCK TABLES t2 WRITE"; 1921--source include/wait_condition.inc 1922 1923--echo # Unblock INSERT. 1924SELECT RELEASE_LOCK('mysqltest_lock'); 1925 1926connection con1; 1927--echo # Reaping INSERT 1928--reap 1929SELECT RELEASE_LOCK('mysqltest_lock'); 1930 1931connection con2; 1932--echo # Reaping LOCK TABLES WRITE 1933--reap 1934UNLOCK TABLES; 1935 1936connection default; 1937--echo # Revert effects from INSERTs 1938DELETE FROM t1 LIMIT 1; 1939DELETE FROM t2 LIMIT 1; 1940 1941--echo # 1942--echo # 2.e) LOCK TABLES WRITE which explicitly locks table is blocked 1943--echo # by concurrent transactions which read table. 1944BEGIN; 1945SELECT * FROM t1; 1946 1947connection con1; 1948--echo # Sending: 1949--send LOCK TABLES t1 WRITE; 1950 1951connection default; 1952--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 1953let $wait_condition= 1954 select count(*) = 1 from information_schema.processlist 1955 where state = "Waiting for table metadata lock" and 1956 info = "LOCK TABLES t1 WRITE"; 1957--source include/wait_condition.inc 1958--echo # Unblock LOCK TABLES WRITE 1959COMMIT; 1960 1961connection con1; 1962--echo # Reap LOCK TABLES WRITE 1963--reap 1964UNLOCK TABLES; 1965 1966connection default; 1967BEGIN; 1968SELECT * FROM t2; 1969 1970connection con1; 1971--echo # Sending: 1972--send LOCK TABLES t2 WRITE; 1973 1974connection default; 1975--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 1976let $wait_condition= 1977 select count(*) = 1 from information_schema.processlist 1978 where state = "Waiting for table metadata lock" and 1979 info = "LOCK TABLES t2 WRITE"; 1980--source include/wait_condition.inc 1981--echo # Unblock LOCK TABLES WRITE 1982COMMIT; 1983 1984connection con1; 1985--echo # Reap LOCK TABLES WRITE 1986--reap 1987UNLOCK TABLES; 1988 1989connection default; 1990--echo # 1991--echo # 2.f) LOCK TABLES WRITE which explicitly locks table is blocked 1992--echo # by concurrent transactions which modify table. 1993BEGIN; 1994INSERT INTO t1 VALUES (1); 1995 1996connection con1; 1997--echo # Sending: 1998--send LOCK TABLES t1 WRITE; 1999 2000connection default; 2001--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 2002let $wait_condition= 2003 select count(*) = 1 from information_schema.processlist 2004 where state = "Waiting for table metadata lock" and 2005 info = "LOCK TABLES t1 WRITE"; 2006--source include/wait_condition.inc 2007--echo # Unblock LOCK TABLES WRITE 2008COMMIT; 2009 2010connection con1; 2011--echo # Reap LOCK TABLES WRITE 2012--reap 2013UNLOCK TABLES; 2014 2015connection default; 2016BEGIN; 2017INSERT INTO t2 VALUES (1); 2018 2019connection con1; 2020--echo # Sending: 2021--send LOCK TABLES t2 WRITE; 2022 2023connection default; 2024--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 2025let $wait_condition= 2026 select count(*) = 1 from information_schema.processlist 2027 where state = "Waiting for table metadata lock" and 2028 info = "LOCK TABLES t2 WRITE"; 2029--source include/wait_condition.inc 2030--echo # Unblock LOCK TABLES WRITE 2031COMMIT; 2032 2033connection con1; 2034--echo # Reap LOCK TABLES WRITE 2035--reap 2036UNLOCK TABLES; 2037 2038connection default; 2039--echo # Revert effects from INSERTs 2040DELETE FROM t1 LIMIT 1; 2041DELETE FROM t2 LIMIT 1; 2042 2043--echo # 2044--echo # 2.g) LOCK TABLES WRITE which explicitly locks table is not compatible 2045--echo # with itself. 2046LOCK TABLES t1 WRITE; 2047 2048connection con1; 2049--echo # Sending: 2050--send LOCK TABLES t1 WRITE; 2051 2052connection default; 2053--echo # Wait until LOCK TABLES WRITE is blocked 2054let $wait_condition= 2055 select count(*) = 1 from information_schema.processlist 2056 where state = "Waiting for table metadata lock" and 2057 info = "LOCK TABLES t1 WRITE"; 2058--source include/wait_condition.inc 2059--echo # Unblock LOCK TABLES WRITE 2060UNLOCK TABLES; 2061 2062connection con1; 2063--echo # Reap LOCK TABLES WRITE 2064--reap 2065UNLOCK TABLES; 2066 2067connection default; 2068LOCK TABLES t2 WRITE; 2069 2070connection con1; 2071--echo # Sending: 2072--send LOCK TABLES t2 WRITE; 2073 2074connection default; 2075--echo # Wait until LOCK TABLES WRITE is blocked 2076let $wait_condition= 2077 select count(*) = 1 from information_schema.processlist 2078 where state = "Waiting for table metadata lock" and 2079 info = "LOCK TABLES t2 WRITE"; 2080--source include/wait_condition.inc 2081--echo # Unblock LOCK TABLES WRITE 2082UNLOCK TABLES; 2083 2084connection con1; 2085--echo # Reap LOCK TABLES WRITE 2086--reap 2087UNLOCK TABLES; 2088 2089 2090--echo # 2091--echo # 3) LOCK TABLES which locks table for read through view. 2092--echo # Case of main table in mergeable view. 2093--echo # 2094--echo # 3.a) Allows concurrent reads 2095connection default; 2096LOCK TABLE v1 READ, v2 READ; 2097connection con1; 2098SELECT * FROM t1; 2099SELECT * FROM t2; 2100connection default; 2101UNLOCK TABLES; 2102 2103--echo # 2104--echo # 3.b) Is allowed concurrently to reads 2105SELECT GET_LOCK('mysqltest_lock', 100); 2106 2107connection con1; 2108--echo # Start read by sending SELECT: 2109--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2; 2110 2111connection con2; 2112--echo # Wait until SELECT gets read lock and starts waiting for user lock 2113let $wait_condition= 2114 select count(*) = 1 from information_schema.processlist 2115 where state = "User lock" and 2116 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2"; 2117--source include/wait_condition.inc 2118--echo # LOCK TABLES should not be blocked. 2119LOCK TABLES v1 READ, v2 READ; 2120UNLOCK TABLES; 2121 2122connection default; 2123--echo # Unblock SELECT. 2124SELECT RELEASE_LOCK('mysqltest_lock'); 2125 2126connection con1; 2127--echo # Reaping SELECT 2128--reap 2129SELECT RELEASE_LOCK('mysqltest_lock'); 2130 2131--echo # 2132--echo # 3.c) Blocks concurrent modifications to table 2133connection default; 2134 2135LOCK TABLE v1 READ, v2 READ; 2136 2137connection con1; 2138--echo # Sending: 2139--send INSERT INTO t1 VALUES (1) 2140 2141connection default; 2142--echo # Wait until INSERT gets blocked 2143let $wait_condition= 2144 select count(*) = 1 from information_schema.processlist 2145 where state = "Waiting for table metadata lock" and 2146 info = "INSERT INTO t1 VALUES (1)"; 2147--source include/wait_condition.inc 2148 2149connection con2; 2150--echo # Sending: 2151--send INSERT INTO t2 VALUES (1) 2152 2153connection default; 2154--echo # Wait until INSERT gets blocked 2155let $wait_condition= 2156 select count(*) = 1 from information_schema.processlist 2157 where state = "Waiting for table metadata lock" and 2158 info = "INSERT INTO t2 VALUES (1)"; 2159--source include/wait_condition.inc 2160 2161--echo # Unblock INSERTs 2162UNLOCK TABLES; 2163 2164connection con1; 2165--echo # Reap INSERT 2166--reap 2167 2168connection con2; 2169--echo # Reap INSERT 2170--reap 2171 2172connection default; 2173--echo # Revert effects from INSERTs 2174DELETE FROM t1 LIMIT 1; 2175DELETE FROM t2 LIMIT 1; 2176 2177--echo # 2178--echo # 3.d) Is blocked by concurrent table modifications 2179SELECT GET_LOCK('mysqltest_lock', 100); 2180 2181connection con1; 2182--echo # Sending: 2183--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 2184 2185connection con2; 2186--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 2187let $wait_condition= 2188 select count(*) = 1 from information_schema.processlist 2189 where state = "User lock" and 2190 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 2191--source include/wait_condition.inc 2192 2193--echo # Sending: 2194--send LOCK TABLES v1 READ; 2195 2196connection default; 2197--echo # Wait until LOCK TABLES READ is blocked due to INSERT 2198let $wait_condition= 2199 select count(*) = 1 from information_schema.processlist 2200 where state = "Waiting for table metadata lock" and 2201 info = "LOCK TABLES v1 READ"; 2202--source include/wait_condition.inc 2203 2204--echo # Unblock INSERT. 2205SELECT RELEASE_LOCK('mysqltest_lock'); 2206 2207connection con1; 2208--echo # Reaping INSERT 2209--reap 2210SELECT RELEASE_LOCK('mysqltest_lock'); 2211 2212connection con2; 2213--echo # Reaping LOCK TABLES READ 2214--reap 2215UNLOCK TABLES; 2216 2217connection default; 2218 2219SELECT GET_LOCK('mysqltest_lock', 100); 2220 2221connection con1; 2222--echo # Sending: 2223--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 2224 2225connection con2; 2226--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 2227let $wait_condition= 2228 select count(*) = 1 from information_schema.processlist 2229 where state = "User lock" and 2230 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 2231--source include/wait_condition.inc 2232 2233--echo # Sending: 2234--send LOCK TABLES v2 READ; 2235 2236connection default; 2237--echo # Wait until LOCK TABLES READ is blocked due to INSERT 2238let $wait_condition= 2239 select count(*) = 1 from information_schema.processlist 2240 where state = "Waiting for table metadata lock" and 2241 info = "LOCK TABLES v2 READ"; 2242--source include/wait_condition.inc 2243 2244--echo # Unblock INSERT. 2245SELECT RELEASE_LOCK('mysqltest_lock'); 2246 2247connection con1; 2248--echo # Reaping INSERT 2249--reap 2250SELECT RELEASE_LOCK('mysqltest_lock'); 2251 2252connection con2; 2253--echo # Reaping LOCK TABLES READ 2254--reap 2255UNLOCK TABLES; 2256 2257connection default; 2258--echo # Revert effects from INSERTs 2259DELETE FROM t1 LIMIT 1; 2260DELETE FROM t2 LIMIT 1; 2261 2262--echo # 2263--echo # 3.e) LOCK TABLES which locks table for read through view is not blocked 2264--echo # by concurrent transactions which read table. 2265BEGIN; 2266SELECT * FROM t1; 2267 2268connection con1; 2269LOCK TABLES v1 READ; 2270UNLOCK TABLES; 2271 2272connection default; 2273COMMIT; 2274 2275BEGIN; 2276SELECT * FROM t2; 2277 2278connection con1; 2279LOCK TABLES v2 READ; 2280UNLOCK TABLES; 2281 2282connection default; 2283COMMIT; 2284 2285--echo # 2286--echo # 3.f) LOCK TABLES which locks table for read through view is blocked 2287--echo # by concurrent transactions which modify table. 2288BEGIN; 2289INSERT INTO t1 VALUES (1); 2290 2291connection con1; 2292--echo # Sending: 2293--send LOCK TABLES v1 READ; 2294 2295connection default; 2296--echo # Wait until LOCK TABLES READ is blocked due concurrent transaction 2297let $wait_condition= 2298 select count(*) = 1 from information_schema.processlist 2299 where state = "Waiting for table metadata lock" and 2300 info = "LOCK TABLES v1 READ"; 2301--source include/wait_condition.inc 2302--echo # Unblock LOCK TABLES READ 2303COMMIT; 2304 2305connection con1; 2306--echo # Reap LOCK TABLES READ 2307--reap 2308UNLOCK TABLES; 2309 2310connection default; 2311BEGIN; 2312INSERT INTO t2 VALUES (1); 2313 2314connection con1; 2315--echo # Sending: 2316--send LOCK TABLES v2 READ; 2317 2318connection default; 2319--echo # Wait until LOCK TABLES READ is blocked due concurrent transaction 2320let $wait_condition= 2321 select count(*) = 1 from information_schema.processlist 2322 where state = "Waiting for table metadata lock" and 2323 info = "LOCK TABLES v2 READ"; 2324--source include/wait_condition.inc 2325--echo # Unblock LOCK TABLES READ 2326COMMIT; 2327 2328connection con1; 2329--echo # Reap LOCK TABLES READ 2330--reap 2331UNLOCK TABLES; 2332 2333connection default; 2334--echo # Revert effects from INSERTs 2335DELETE FROM t1 LIMIT 1; 2336DELETE FROM t2 LIMIT 1; 2337 2338--echo # 2339--echo # 3.g) LOCK TABLES READ which locks table for read through view is 2340--echo # compatible with explicit LOCK TABLES READ on the same table. 2341LOCK TABLES t1 READ, t2 READ; 2342 2343connection con1; 2344LOCK TABLES v1 READ, v2 READ; 2345UNLOCK TABLES; 2346 2347connection default; 2348UNLOCK TABLES; 2349 2350--echo # 2351--echo # 3.h) LOCK TABLES READ which locks table for read through view is 2352--echo # not compatible with explicit LOCK TABLES WRITE on the same table. 2353LOCK TABLES v1 READ; 2354 2355connection con1; 2356--echo # Sending: 2357--send LOCK TABLES t1 WRITE; 2358 2359connection default; 2360--echo # Wait until LOCK TABLES WRITE is blocked 2361let $wait_condition= 2362 select count(*) = 1 from information_schema.processlist 2363 where state = "Waiting for table metadata lock" and 2364 info = "LOCK TABLES t1 WRITE"; 2365--source include/wait_condition.inc 2366--echo # Unblock LOCK TABLES WRITE 2367UNLOCK TABLES; 2368 2369connection con1; 2370--echo # Reap LOCK TABLES WRITE 2371--reap 2372UNLOCK TABLES; 2373 2374connection default; 2375LOCK TABLES v2 READ; 2376 2377connection con1; 2378--echo # Sending: 2379--send LOCK TABLES t2 WRITE; 2380 2381connection default; 2382--echo # Wait until LOCK TABLES WRITE is blocked 2383let $wait_condition= 2384 select count(*) = 1 from information_schema.processlist 2385 where state = "Waiting for table metadata lock" and 2386 info = "LOCK TABLES t2 WRITE"; 2387--source include/wait_condition.inc 2388--echo # Unblock LOCK TABLES WRITE 2389UNLOCK TABLES; 2390 2391connection con1; 2392--echo # Reap LOCK TABLES WRITE 2393--reap 2394UNLOCK TABLES; 2395 2396connection default; 2397LOCK TABLES t1 WRITE; 2398 2399connection con1; 2400--echo # Sending: 2401--send LOCK TABLES v1 READ; 2402 2403connection default; 2404--echo # Wait until LOCK TABLES READ is blocked 2405let $wait_condition= 2406 select count(*) = 1 from information_schema.processlist 2407 where state = "Waiting for table metadata lock" and 2408 info = "LOCK TABLES v1 READ"; 2409--source include/wait_condition.inc 2410--echo # Unblock LOCK TABLES READ 2411UNLOCK TABLES; 2412 2413connection con1; 2414--echo # Reap LOCK TABLES READ 2415--reap 2416UNLOCK TABLES; 2417 2418connection default; 2419LOCK TABLES t2 WRITE; 2420 2421connection con1; 2422--echo # Sending: 2423--send LOCK TABLES v2 READ; 2424 2425connection default; 2426--echo # Wait until LOCK TABLES READ is blocked 2427let $wait_condition= 2428 select count(*) = 1 from information_schema.processlist 2429 where state = "Waiting for table metadata lock" and 2430 info = "LOCK TABLES v2 READ"; 2431--source include/wait_condition.inc 2432--echo # Unblock LOCK TABLES READ 2433UNLOCK TABLES; 2434 2435connection con1; 2436--echo # Reap LOCK TABLES READ 2437--reap 2438UNLOCK TABLES; 2439 2440 2441--echo # 2442--echo # 4) LOCK TABLES which locks table for read through mergeable view. 2443--echo # Case of table not from the main join. Such table will be locked 2444--echo # for read even though view is locked for write. 2445--echo # 4.a) Allows concurrent reads 2446connection default; 2447LOCK TABLES v3 WRITE, v4 WRITE; 2448connection con1; 2449SELECT * FROM t1; 2450SELECT * FROM t2; 2451connection default; 2452UNLOCK TABLES; 2453 2454--echo # 2455--echo # 4.b) Is allowed concurrently to reads 2456SELECT GET_LOCK('mysqltest_lock', 100); 2457 2458connection con1; 2459--echo # Start read by sending SELECT: 2460--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2; 2461 2462connection con2; 2463--echo # Wait until SELECT gets read lock and starts waiting for user lock 2464let $wait_condition= 2465 select count(*) = 1 from information_schema.processlist 2466 where state = "User lock" and 2467 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2"; 2468--source include/wait_condition.inc 2469--echo # LOCK TABLES should not be blocked. 2470LOCK TABLES v3 WRITE, v4 WRITE; 2471UNLOCK TABLES; 2472 2473connection default; 2474--echo # Unblock SELECT. 2475SELECT RELEASE_LOCK('mysqltest_lock'); 2476 2477connection con1; 2478--echo # Reaping SELECT 2479--reap 2480SELECT RELEASE_LOCK('mysqltest_lock'); 2481 2482--echo # 2483--echo # 4.c) Blocks concurrent modifications to table 2484connection default; 2485 2486LOCK TABLES v3 WRITE, v4 WRITE; 2487 2488connection con1; 2489--echo # Sending: 2490--send INSERT INTO t1 VALUES (1) 2491 2492connection default; 2493--echo # Wait until INSERT gets blocked 2494let $wait_condition= 2495 select count(*) = 1 from information_schema.processlist 2496 where state = "Waiting for table metadata lock" and 2497 info = "INSERT INTO t1 VALUES (1)"; 2498--source include/wait_condition.inc 2499 2500connection con2; 2501--echo # Sending: 2502--send INSERT INTO t2 VALUES (1) 2503 2504connection default; 2505--echo # Wait until INSERT gets blocked 2506let $wait_condition= 2507 select count(*) = 1 from information_schema.processlist 2508 where state = "Waiting for table metadata lock" and 2509 info = "INSERT INTO t2 VALUES (1)"; 2510--source include/wait_condition.inc 2511 2512--echo # Unblock INSERTs 2513UNLOCK TABLES; 2514 2515connection con1; 2516--echo # Reap INSERT 2517--reap 2518 2519connection con2; 2520--echo # Reap INSERT 2521--reap 2522 2523connection default; 2524--echo # Revert effects from INSERTs 2525DELETE FROM t1 LIMIT 1; 2526DELETE FROM t2 LIMIT 1; 2527 2528--echo # 2529--echo # 4.d) Is blocked by concurrent table modifications 2530SELECT GET_LOCK('mysqltest_lock', 100); 2531 2532connection con1; 2533--echo # Sending: 2534--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 2535 2536connection con2; 2537--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 2538let $wait_condition= 2539 select count(*) = 1 from information_schema.processlist 2540 where state = "User lock" and 2541 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 2542--source include/wait_condition.inc 2543 2544--echo # Sending: 2545--send LOCK TABLES v3 WRITE; 2546 2547connection default; 2548--echo # Wait until LOCK TABLES is blocked due to INSERT 2549let $wait_condition= 2550 select count(*) = 1 from information_schema.processlist 2551 where state = "Waiting for table metadata lock" and 2552 info = "LOCK TABLES v3 WRITE"; 2553--source include/wait_condition.inc 2554 2555--echo # Unblock INSERT. 2556SELECT RELEASE_LOCK('mysqltest_lock'); 2557 2558connection con1; 2559--echo # Reaping INSERT 2560--reap 2561SELECT RELEASE_LOCK('mysqltest_lock'); 2562 2563connection con2; 2564--echo # Reaping LOCK TABLES 2565--reap 2566UNLOCK TABLES; 2567 2568connection default; 2569 2570SELECT GET_LOCK('mysqltest_lock', 100); 2571 2572connection con1; 2573--echo # Sending: 2574--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 2575 2576connection con2; 2577--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 2578let $wait_condition= 2579 select count(*) = 1 from information_schema.processlist 2580 where state = "User lock" and 2581 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 2582--source include/wait_condition.inc 2583 2584--echo # Sending: 2585--send LOCK TABLES v4 WRITE; 2586 2587connection default; 2588--echo # Wait until LOCK TABLES is blocked due to INSERT 2589let $wait_condition= 2590 select count(*) = 1 from information_schema.processlist 2591 where state = "Waiting for table metadata lock" and 2592 info = "LOCK TABLES v4 WRITE"; 2593--source include/wait_condition.inc 2594 2595--echo # Unblock INSERT. 2596SELECT RELEASE_LOCK('mysqltest_lock'); 2597 2598connection con1; 2599--echo # Reaping INSERT 2600--reap 2601SELECT RELEASE_LOCK('mysqltest_lock'); 2602 2603connection con2; 2604--echo # Reaping LOCK TABLES 2605--reap 2606UNLOCK TABLES; 2607 2608connection default; 2609--echo # Revert effects from INSERTs 2610DELETE FROM t1 LIMIT 1; 2611DELETE FROM t2 LIMIT 1; 2612 2613--echo # 2614--echo # 4.e) LOCK TABLES which locks table for read through view is not blocked 2615--echo # by concurrent transactions which read table. 2616BEGIN; 2617SELECT * FROM t1; 2618 2619connection con1; 2620LOCK TABLES v3 WRITE; 2621UNLOCK TABLES; 2622 2623connection default; 2624COMMIT; 2625 2626BEGIN; 2627SELECT * FROM t2; 2628 2629connection con1; 2630LOCK TABLES v4 WRITE; 2631UNLOCK TABLES; 2632 2633connection default; 2634COMMIT; 2635 2636--echo # 2637--echo # 4.f) LOCK TABLES which locks table for read through view is blocked 2638--echo # by concurrent transactions which modify table. 2639BEGIN; 2640INSERT INTO t1 VALUES (1); 2641 2642connection con1; 2643--echo # Sending: 2644--send LOCK TABLES v3 WRITE; 2645 2646connection default; 2647--echo # Wait until LOCK TABLES is blocked due concurrent transaction 2648let $wait_condition= 2649 select count(*) = 1 from information_schema.processlist 2650 where state = "Waiting for table metadata lock" and 2651 info = "LOCK TABLES v3 WRITE"; 2652--source include/wait_condition.inc 2653--echo # Unblock LOCK TABLES 2654COMMIT; 2655 2656connection con1; 2657--echo # Reap LOCK TABLES 2658--reap 2659UNLOCK TABLES; 2660 2661connection default; 2662BEGIN; 2663INSERT INTO t2 VALUES (1); 2664 2665connection con1; 2666--echo # Sending: 2667--send LOCK TABLES v4 WRITE; 2668 2669connection default; 2670--echo # Wait until LOCK TABLES is blocked due concurrent transaction 2671let $wait_condition= 2672 select count(*) = 1 from information_schema.processlist 2673 where state = "Waiting for table metadata lock" and 2674 info = "LOCK TABLES v4 WRITE"; 2675--source include/wait_condition.inc 2676--echo # Unblock LOCK TABLES 2677COMMIT; 2678 2679connection con1; 2680--echo # Reap LOCK TABLES 2681--reap 2682UNLOCK TABLES; 2683 2684connection default; 2685--echo # Revert effects from INSERTs 2686DELETE FROM t1 LIMIT 1; 2687DELETE FROM t2 LIMIT 1; 2688 2689--echo # 2690--echo # 4.g) LOCK TABLES which locks table for read through view is 2691--echo # compatible with explicit LOCK TABLES READ on the same table. 2692LOCK TABLES t1 READ, t2 READ; 2693 2694connection con1; 2695LOCK TABLES v3 WRITE, v4 WRITE; 2696UNLOCK TABLES; 2697 2698connection default; 2699UNLOCK TABLES; 2700 2701--echo # 2702--echo # 4.h) LOCK TABLES which locks table for read through view is 2703--echo # not compatible with explicit LOCK TABLES WRITE on the same table. 2704LOCK TABLES v3 WRITE; 2705 2706connection con1; 2707--echo # Sending: 2708--send LOCK TABLES t1 WRITE; 2709 2710connection default; 2711--echo # Wait until LOCK TABLES WRITE is blocked 2712let $wait_condition= 2713 select count(*) = 1 from information_schema.processlist 2714 where state = "Waiting for table metadata lock" and 2715 info = "LOCK TABLES t1 WRITE"; 2716--source include/wait_condition.inc 2717--echo # Unblock LOCK TABLES WRITE 2718UNLOCK TABLES; 2719 2720connection con1; 2721--echo # Reap LOCK TABLES WRITE 2722--reap 2723UNLOCK TABLES; 2724 2725connection default; 2726LOCK TABLES v4 WRITE; 2727 2728connection con1; 2729--echo # Sending: 2730--send LOCK TABLES t2 WRITE; 2731 2732connection default; 2733--echo # Wait until LOCK TABLES WRITE is blocked 2734let $wait_condition= 2735 select count(*) = 1 from information_schema.processlist 2736 where state = "Waiting for table metadata lock" and 2737 info = "LOCK TABLES t2 WRITE"; 2738--source include/wait_condition.inc 2739--echo # Unblock LOCK TABLES WRITE 2740UNLOCK TABLES; 2741 2742connection con1; 2743--echo # Reap LOCK TABLES WRITE 2744--reap 2745UNLOCK TABLES; 2746 2747connection default; 2748LOCK TABLES t1 WRITE; 2749 2750connection con1; 2751--echo # Sending: 2752--send LOCK TABLES v3 WRITE; 2753 2754connection default; 2755--echo # Wait until LOCK TABLES is blocked 2756let $wait_condition= 2757 select count(*) = 1 from information_schema.processlist 2758 where state = "Waiting for table metadata lock" and 2759 info = "LOCK TABLES v3 WRITE"; 2760--source include/wait_condition.inc 2761--echo # Unblock LOCK TABLES 2762UNLOCK TABLES; 2763 2764connection con1; 2765--echo # Reap LOCK TABLES 2766--reap 2767UNLOCK TABLES; 2768 2769connection default; 2770LOCK TABLES t2 WRITE; 2771 2772connection con1; 2773--echo # Sending: 2774--send LOCK TABLES v4 WRITE; 2775 2776connection default; 2777--echo # Wait until LOCK TABLES WRITE is blocked 2778let $wait_condition= 2779 select count(*) = 1 from information_schema.processlist 2780 where state = "Waiting for table metadata lock" and 2781 info = "LOCK TABLES v4 WRITE"; 2782--source include/wait_condition.inc 2783--echo # Unblock LOCK TABLES 2784UNLOCK TABLES; 2785 2786connection con1; 2787--echo # Reap LOCK TABLES 2788--reap 2789UNLOCK TABLES; 2790 2791 2792--echo # 2793--echo # 5) LOCK TABLES which locks tables for write through view 2794--echo # 2795--echo # 5.a) Doesn't allow concurrent reads 2796connection default; 2797LOCK TABLE v1 WRITE; 2798 2799connection con1; 2800--echo # Sending: 2801--send SELECT * FROM t1; 2802 2803connection default; 2804--echo # Wait until SELECT gets blocked 2805let $wait_condition= 2806 select count(*) = 1 from information_schema.processlist 2807 where state = "Waiting for table metadata lock" and 2808 info = "SELECT * FROM t1"; 2809--source include/wait_condition.inc 2810--echo # Unblock SELECT 2811UNLOCK TABLES; 2812 2813connection con1; 2814--echo # Reaping SELECT 2815--reap 2816 2817connection default; 2818LOCK TABLE v2 WRITE; 2819 2820connection con1; 2821--echo # Sending: 2822--send SELECT * FROM t2; 2823 2824connection default; 2825--echo # Wait until SELECT gets blocked 2826let $wait_condition= 2827 select count(*) = 1 from information_schema.processlist 2828 where state = "Waiting for table metadata lock" and 2829 info = "SELECT * FROM t2"; 2830--source include/wait_condition.inc 2831--echo # Unblock SELECT 2832UNLOCK TABLES; 2833 2834connection con1; 2835--echo # Reaping SELECT 2836--reap 2837 2838connection default; 2839--echo # 2840--echo # 5.b) Is not allowed concurrently to reads 2841SELECT GET_LOCK('mysqltest_lock', 100); 2842 2843connection con1; 2844--echo # Start read by sending SELECT: 2845--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1; 2846 2847connection con2; 2848--echo # Wait until SELECT gets read lock and starts waiting for user lock 2849let $wait_condition= 2850 select count(*) = 1 from information_schema.processlist 2851 where state = "User lock" and 2852 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1"; 2853--source include/wait_condition.inc 2854--echo # Sending: 2855--send LOCK TABLES v1 WRITE; 2856 2857connection default; 2858let $wait_condition= 2859 select count(*) = 1 from information_schema.processlist 2860 where state = "Waiting for table metadata lock" and 2861 info = "LOCK TABLES v1 WRITE"; 2862--source include/wait_condition.inc 2863UNLOCK TABLES; 2864 2865--echo # Unblock SELECT. 2866SELECT RELEASE_LOCK('mysqltest_lock'); 2867 2868connection con1; 2869--echo # Reaping SELECT 2870--reap 2871SELECT RELEASE_LOCK('mysqltest_lock'); 2872 2873connection con2; 2874--echo # Reaping LOCK TABLE WRITE 2875--reap 2876UNLOCK TABLES; 2877 2878connection default; 2879SELECT GET_LOCK('mysqltest_lock', 100); 2880 2881connection con1; 2882--echo # Start read by sending SELECT: 2883--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t2; 2884 2885connection con2; 2886--echo # Wait until SELECT gets read lock and starts waiting for user lock 2887let $wait_condition= 2888 select count(*) = 1 from information_schema.processlist 2889 where state = "User lock" and 2890 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t2"; 2891--source include/wait_condition.inc 2892--echo # Sending: 2893--send LOCK TABLES v2 WRITE; 2894 2895connection default; 2896let $wait_condition= 2897 select count(*) = 1 from information_schema.processlist 2898 where state = "Waiting for table metadata lock" and 2899 info = "LOCK TABLES v2 WRITE"; 2900--source include/wait_condition.inc 2901UNLOCK TABLES; 2902 2903--echo # Unblock SELECT. 2904SELECT RELEASE_LOCK('mysqltest_lock'); 2905 2906connection con1; 2907--echo # Reaping SELECT 2908--reap 2909SELECT RELEASE_LOCK('mysqltest_lock'); 2910 2911connection con2; 2912--echo # Reaping LOCK TABLE WRITE 2913--reap 2914UNLOCK TABLES; 2915 2916 2917--echo # 2918--echo # 5.c) Blocks concurrent modifications to table 2919connection default; 2920LOCK TABLE v1 WRITE, v2 WRITE; 2921 2922connection con1; 2923--echo # Sending: 2924--send INSERT INTO t1 VALUES (1) 2925 2926connection default; 2927--echo # Wait until INSERT gets blocked 2928let $wait_condition= 2929 select count(*) = 1 from information_schema.processlist 2930 where state = "Waiting for table metadata lock" and 2931 info = "INSERT INTO t1 VALUES (1)"; 2932--source include/wait_condition.inc 2933 2934connection con2; 2935--echo # Sending: 2936--send INSERT INTO t2 VALUES (1) 2937 2938connection default; 2939--echo # Wait until INSERT gets blocked 2940let $wait_condition= 2941 select count(*) = 1 from information_schema.processlist 2942 where state = "Waiting for table metadata lock" and 2943 info = "INSERT INTO t2 VALUES (1)"; 2944--source include/wait_condition.inc 2945 2946--echo # Unblock INSERTs 2947UNLOCK TABLES; 2948 2949connection con1; 2950--echo # Reap INSERT 2951--reap 2952 2953connection con2; 2954--echo # Reap INSERT 2955--reap 2956 2957connection default; 2958--echo # Revert effects from INSERTs 2959DELETE FROM t1 LIMIT 1; 2960DELETE FROM t2 LIMIT 1; 2961 2962--echo # 2963--echo # 5.d) Is blocked by concurrent table modifications 2964SELECT GET_LOCK('mysqltest_lock', 100); 2965 2966connection con1; 2967--echo # Sending: 2968--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 2969 2970connection con2; 2971--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 2972let $wait_condition= 2973 select count(*) = 1 from information_schema.processlist 2974 where state = "User lock" and 2975 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 2976--source include/wait_condition.inc 2977 2978--echo # Sending: 2979--send LOCK TABLES v1 WRITE; 2980 2981connection default; 2982--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 2983let $wait_condition= 2984 select count(*) = 1 from information_schema.processlist 2985 where state = "Waiting for table metadata lock" and 2986 info = "LOCK TABLES v1 WRITE"; 2987--source include/wait_condition.inc 2988 2989--echo # Unblock INSERT. 2990SELECT RELEASE_LOCK('mysqltest_lock'); 2991 2992connection con1; 2993--echo # Reaping INSERT 2994--reap 2995SELECT RELEASE_LOCK('mysqltest_lock'); 2996 2997connection con2; 2998--echo # Reaping LOCK TABLES WRITE 2999--reap 3000UNLOCK TABLES; 3001 3002connection default; 3003 3004SELECT GET_LOCK('mysqltest_lock', 100); 3005 3006connection con1; 3007--echo # Sending: 3008--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 3009 3010connection con2; 3011--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 3012let $wait_condition= 3013 select count(*) = 1 from information_schema.processlist 3014 where state = "User lock" and 3015 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 3016--source include/wait_condition.inc 3017 3018--echo # Sending: 3019--send LOCK TABLES v2 WRITE; 3020 3021connection default; 3022--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 3023let $wait_condition= 3024 select count(*) = 1 from information_schema.processlist 3025 where state = "Waiting for table metadata lock" and 3026 info = "LOCK TABLES v2 WRITE"; 3027--source include/wait_condition.inc 3028 3029--echo # Unblock INSERT. 3030SELECT RELEASE_LOCK('mysqltest_lock'); 3031 3032connection con1; 3033--echo # Reaping INSERT 3034--reap 3035SELECT RELEASE_LOCK('mysqltest_lock'); 3036 3037connection con2; 3038--echo # Reaping LOCK TABLES WRITE 3039--reap 3040UNLOCK TABLES; 3041 3042connection default; 3043--echo # Revert effects from INSERTs 3044DELETE FROM t1 LIMIT 1; 3045DELETE FROM t2 LIMIT 1; 3046 3047--echo # 3048--echo # 5.e) LOCK TABLES which locks table for write through view is blocked 3049--echo # by concurrent transactions which read table. 3050BEGIN; 3051SELECT * FROM t1; 3052 3053connection con1; 3054--echo # Sending: 3055--send LOCK TABLES v1 WRITE; 3056 3057connection default; 3058--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3059let $wait_condition= 3060 select count(*) = 1 from information_schema.processlist 3061 where state = "Waiting for table metadata lock" and 3062 info = "LOCK TABLES v1 WRITE"; 3063--source include/wait_condition.inc 3064--echo # Unblock LOCK TABLES WRITE 3065COMMIT; 3066 3067connection con1; 3068--echo # Reap LOCK TABLES WRITE 3069--reap 3070UNLOCK TABLES; 3071 3072connection default; 3073BEGIN; 3074SELECT * FROM t2; 3075 3076connection con1; 3077--echo # Sending: 3078--send LOCK TABLES v2 WRITE; 3079 3080connection default; 3081--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3082let $wait_condition= 3083 select count(*) = 1 from information_schema.processlist 3084 where state = "Waiting for table metadata lock" and 3085 info = "LOCK TABLES v2 WRITE"; 3086--source include/wait_condition.inc 3087--echo # Unblock LOCK TABLES WRITE 3088COMMIT; 3089 3090connection con1; 3091--echo # Reap LOCK TABLES WRITE 3092--reap 3093UNLOCK TABLES; 3094 3095connection default; 3096--echo # 3097--echo # 5.f) LOCK TABLES which locks table for write through view is blocked 3098--echo # by concurrent transactions which modify table. 3099BEGIN; 3100INSERT INTO t1 VALUES (1); 3101 3102connection con1; 3103--echo # Sending: 3104--send LOCK TABLES v1 WRITE; 3105 3106connection default; 3107--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3108let $wait_condition= 3109 select count(*) = 1 from information_schema.processlist 3110 where state = "Waiting for table metadata lock" and 3111 info = "LOCK TABLES v1 WRITE"; 3112--source include/wait_condition.inc 3113--echo # Unblock LOCK TABLES WRITE 3114COMMIT; 3115 3116connection con1; 3117--echo # Reap LOCK TABLES WRITE 3118--reap 3119UNLOCK TABLES; 3120 3121connection default; 3122BEGIN; 3123INSERT INTO t2 VALUES (1); 3124 3125connection con1; 3126--echo # Sending: 3127--send LOCK TABLES v2 WRITE; 3128 3129connection default; 3130--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3131let $wait_condition= 3132 select count(*) = 1 from information_schema.processlist 3133 where state = "Waiting for table metadata lock" and 3134 info = "LOCK TABLES v2 WRITE"; 3135--source include/wait_condition.inc 3136--echo # Unblock LOCK TABLES WRITE 3137COMMIT; 3138 3139connection con1; 3140--echo # Reap LOCK TABLES WRITE 3141--reap 3142UNLOCK TABLES; 3143 3144connection default; 3145--echo # Revert effects from INSERTs 3146DELETE FROM t1 LIMIT 1; 3147DELETE FROM t2 LIMIT 1; 3148 3149--echo # 3150--echo # 5.g) LOCK TABLES which locks table for write through view is not 3151--echo # compatible with LOCK TABLE WRITE. 3152LOCK TABLES v1 WRITE; 3153 3154connection con1; 3155--echo # Sending: 3156--send LOCK TABLES t1 WRITE; 3157 3158connection default; 3159--echo # Wait until LOCK TABLES WRITE is blocked 3160let $wait_condition= 3161 select count(*) = 1 from information_schema.processlist 3162 where state = "Waiting for table metadata lock" and 3163 info = "LOCK TABLES t1 WRITE"; 3164--source include/wait_condition.inc 3165--echo # Unblock LOCK TABLES WRITE 3166UNLOCK TABLES; 3167 3168connection con1; 3169--echo # Reap LOCK TABLES WRITE 3170--reap 3171UNLOCK TABLES; 3172 3173connection default; 3174LOCK TABLES v2 WRITE; 3175 3176connection con1; 3177--echo # Sending: 3178--send LOCK TABLES t2 WRITE; 3179 3180connection default; 3181--echo # Wait until LOCK TABLES WRITE is blocked 3182let $wait_condition= 3183 select count(*) = 1 from information_schema.processlist 3184 where state = "Waiting for table metadata lock" and 3185 info = "LOCK TABLES t2 WRITE"; 3186--source include/wait_condition.inc 3187--echo # Unblock LOCK TABLES WRITE 3188UNLOCK TABLES; 3189 3190connection con1; 3191--echo # Reap LOCK TABLES WRITE 3192--reap 3193UNLOCK TABLES; 3194 3195 3196--echo # 3197--echo # 6) LOCK TABLES which locks table for read through trigger. 3198--echo # 3199--echo # 6.a) Allows concurrent reads 3200connection default; 3201LOCK TABLES t5 WRITE, t6 WRITE; 3202connection con1; 3203SELECT * FROM t1; 3204SELECT * FROM t2; 3205connection default; 3206UNLOCK TABLES; 3207 3208--echo # 3209--echo # 6.b) Is allowed concurrently to reads 3210SELECT GET_LOCK('mysqltest_lock', 100); 3211 3212connection con1; 3213--echo # Start read by sending SELECT: 3214--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2; 3215 3216connection con2; 3217--echo # Wait until SELECT gets read lock and starts waiting for user lock 3218let $wait_condition= 3219 select count(*) = 1 from information_schema.processlist 3220 where state = "User lock" and 3221 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1, t2"; 3222--source include/wait_condition.inc 3223--echo # LOCK TABLES should not be blocked. 3224LOCK TABLES v3 WRITE, t6 WRITE; 3225UNLOCK TABLES; 3226 3227connection default; 3228--echo # Unblock SELECT. 3229SELECT RELEASE_LOCK('mysqltest_lock'); 3230 3231connection con1; 3232--echo # Reaping SELECT 3233--reap 3234SELECT RELEASE_LOCK('mysqltest_lock'); 3235 3236--echo # 3237--echo # 6.c) Blocks concurrent modifications to table 3238connection default; 3239 3240LOCK TABLES t5 WRITE, t6 WRITE; 3241 3242connection con1; 3243--echo # Sending: 3244--send INSERT INTO t1 VALUES (1) 3245 3246connection default; 3247--echo # Wait until INSERT gets blocked 3248let $wait_condition= 3249 select count(*) = 1 from information_schema.processlist 3250 where state = "Waiting for table metadata lock" and 3251 info = "INSERT INTO t1 VALUES (1)"; 3252--source include/wait_condition.inc 3253 3254connection con2; 3255--echo # Sending: 3256--send INSERT INTO t2 VALUES (1) 3257 3258connection default; 3259--echo # Wait until INSERT gets blocked 3260let $wait_condition= 3261 select count(*) = 1 from information_schema.processlist 3262 where state = "Waiting for table metadata lock" and 3263 info = "INSERT INTO t2 VALUES (1)"; 3264--source include/wait_condition.inc 3265 3266--echo # Unblock INSERTs 3267UNLOCK TABLES; 3268 3269connection con1; 3270--echo # Reap INSERT 3271--reap 3272 3273connection con2; 3274--echo # Reap INSERT 3275--reap 3276 3277connection default; 3278--echo # Revert effects from INSERTs 3279DELETE FROM t1 LIMIT 1; 3280DELETE FROM t2 LIMIT 1; 3281 3282--echo # 3283--echo # 6.d) Is blocked by concurrent table modifications 3284SELECT GET_LOCK('mysqltest_lock', 100); 3285 3286connection con1; 3287--echo # Sending: 3288--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 3289 3290connection con2; 3291--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 3292let $wait_condition= 3293 select count(*) = 1 from information_schema.processlist 3294 where state = "User lock" and 3295 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 3296--source include/wait_condition.inc 3297 3298--echo # Sending: 3299--send LOCK TABLES t5 WRITE; 3300 3301connection default; 3302--echo # Wait until LOCK TABLES is blocked due to INSERT 3303let $wait_condition= 3304 select count(*) = 1 from information_schema.processlist 3305 where state = "Waiting for table metadata lock" and 3306 info = "LOCK TABLES t5 WRITE"; 3307--source include/wait_condition.inc 3308 3309--echo # Unblock INSERT. 3310SELECT RELEASE_LOCK('mysqltest_lock'); 3311 3312connection con1; 3313--echo # Reaping INSERT 3314--reap 3315SELECT RELEASE_LOCK('mysqltest_lock'); 3316 3317connection con2; 3318--echo # Reaping LOCK TABLES 3319--reap 3320UNLOCK TABLES; 3321 3322connection default; 3323 3324SELECT GET_LOCK('mysqltest_lock', 100); 3325 3326connection con1; 3327--echo # Sending: 3328--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 3329 3330connection con2; 3331--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 3332let $wait_condition= 3333 select count(*) = 1 from information_schema.processlist 3334 where state = "User lock" and 3335 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 3336--source include/wait_condition.inc 3337 3338--echo # Sending: 3339--send LOCK TABLES t6 WRITE; 3340 3341connection default; 3342--echo # Wait until LOCK TABLES is blocked due to INSERT 3343let $wait_condition= 3344 select count(*) = 1 from information_schema.processlist 3345 where state = "Waiting for table metadata lock" and 3346 info = "LOCK TABLES t6 WRITE"; 3347--source include/wait_condition.inc 3348 3349--echo # Unblock INSERT. 3350SELECT RELEASE_LOCK('mysqltest_lock'); 3351 3352connection con1; 3353--echo # Reaping INSERT 3354--reap 3355SELECT RELEASE_LOCK('mysqltest_lock'); 3356 3357connection con2; 3358--echo # Reaping LOCK TABLES 3359--reap 3360UNLOCK TABLES; 3361 3362connection default; 3363--echo # Revert effects from INSERTs 3364DELETE FROM t1 LIMIT 1; 3365DELETE FROM t2 LIMIT 1; 3366 3367--echo # 3368--echo # 6.e) LOCK TABLES which locks table for read through trigger is not 3369--echo # blocked by concurrent transactions which read table. 3370BEGIN; 3371SELECT * FROM t1; 3372 3373connection con1; 3374LOCK TABLES t5 WRITE; 3375UNLOCK TABLES; 3376 3377connection default; 3378COMMIT; 3379 3380BEGIN; 3381SELECT * FROM t2; 3382 3383connection con1; 3384LOCK TABLES t6 WRITE; 3385UNLOCK TABLES; 3386 3387connection default; 3388COMMIT; 3389 3390--echo # 3391--echo # 6.f) LOCK TABLES which locks table for read through trigger is 3392--echo # blocked by concurrent transactions which modify table. 3393BEGIN; 3394INSERT INTO t1 VALUES (1); 3395 3396connection con1; 3397--echo # Sending: 3398--send LOCK TABLES t5 WRITE; 3399 3400connection default; 3401--echo # Wait until LOCK TABLES is blocked due concurrent transaction 3402let $wait_condition= 3403 select count(*) = 1 from information_schema.processlist 3404 where state = "Waiting for table metadata lock" and 3405 info = "LOCK TABLES t5 WRITE"; 3406--source include/wait_condition.inc 3407--echo # Unblock LOCK TABLES 3408COMMIT; 3409 3410connection con1; 3411--echo # Reap LOCK TABLES 3412--reap 3413UNLOCK TABLES; 3414 3415connection default; 3416BEGIN; 3417INSERT INTO t2 VALUES (1); 3418 3419connection con1; 3420--echo # Sending: 3421--send LOCK TABLES t6 WRITE; 3422 3423connection default; 3424--echo # Wait until LOCK TABLES is blocked due concurrent transaction 3425let $wait_condition= 3426 select count(*) = 1 from information_schema.processlist 3427 where state = "Waiting for table metadata lock" and 3428 info = "LOCK TABLES t6 WRITE"; 3429--source include/wait_condition.inc 3430--echo # Unblock LOCK TABLES 3431COMMIT; 3432 3433connection con1; 3434--echo # Reap LOCK TABLES 3435--reap 3436UNLOCK TABLES; 3437 3438connection default; 3439--echo # Revert effects from INSERTs 3440DELETE FROM t1 LIMIT 1; 3441DELETE FROM t2 LIMIT 1; 3442 3443--echo # 3444--echo # 6.g) LOCK TABLES which locks table for read through trigger is 3445--echo # compatible with explicit LOCK TABLES READ on the same table. 3446LOCK TABLES t1 READ, t2 READ; 3447 3448connection con1; 3449LOCK TABLES t5 WRITE, t6 WRITE; 3450UNLOCK TABLES; 3451 3452connection default; 3453UNLOCK TABLES; 3454 3455--echo # 3456--echo # 6.h) LOCK TABLES which locks table for read through trigger is 3457--echo # not compatible with explicit LOCK TABLES WRITE on the same table. 3458LOCK TABLES t5 WRITE; 3459 3460connection con1; 3461--echo # Sending: 3462--send LOCK TABLES t1 WRITE; 3463 3464connection default; 3465--echo # Wait until LOCK TABLES WRITE is blocked 3466let $wait_condition= 3467 select count(*) = 1 from information_schema.processlist 3468 where state = "Waiting for table metadata lock" and 3469 info = "LOCK TABLES t1 WRITE"; 3470--source include/wait_condition.inc 3471--echo # Unblock LOCK TABLES WRITE 3472UNLOCK TABLES; 3473 3474connection con1; 3475--echo # Reap LOCK TABLES WRITE 3476--reap 3477UNLOCK TABLES; 3478 3479connection default; 3480LOCK TABLES t6 WRITE; 3481 3482connection con1; 3483--echo # Sending: 3484--send LOCK TABLES t2 WRITE; 3485 3486connection default; 3487--echo # Wait until LOCK TABLES WRITE is blocked 3488let $wait_condition= 3489 select count(*) = 1 from information_schema.processlist 3490 where state = "Waiting for table metadata lock" and 3491 info = "LOCK TABLES t2 WRITE"; 3492--source include/wait_condition.inc 3493--echo # Unblock LOCK TABLES WRITE 3494UNLOCK TABLES; 3495 3496connection con1; 3497--echo # Reap LOCK TABLES WRITE 3498--reap 3499UNLOCK TABLES; 3500 3501connection default; 3502LOCK TABLES t1 WRITE; 3503 3504connection con1; 3505--echo # Sending: 3506--send LOCK TABLES t5 WRITE; 3507 3508connection default; 3509--echo # Wait until LOCK TABLES is blocked 3510let $wait_condition= 3511 select count(*) = 1 from information_schema.processlist 3512 where state = "Waiting for table metadata lock" and 3513 info = "LOCK TABLES t5 WRITE"; 3514--source include/wait_condition.inc 3515--echo # Unblock LOCK TABLES 3516UNLOCK TABLES; 3517 3518connection con1; 3519--echo # Reap LOCK TABLES 3520--reap 3521UNLOCK TABLES; 3522 3523connection default; 3524LOCK TABLES t2 WRITE; 3525 3526connection con1; 3527--echo # Sending: 3528--send LOCK TABLES t6 WRITE; 3529 3530connection default; 3531--echo # Wait until LOCK TABLES WRITE is blocked 3532let $wait_condition= 3533 select count(*) = 1 from information_schema.processlist 3534 where state = "Waiting for table metadata lock" and 3535 info = "LOCK TABLES t6 WRITE"; 3536--source include/wait_condition.inc 3537--echo # Unblock LOCK TABLES 3538UNLOCK TABLES; 3539 3540connection con1; 3541--echo # Reap LOCK TABLES 3542--reap 3543UNLOCK TABLES; 3544 3545 3546--echo # 3547--echo # 7) LOCK TABLES which locks tables for write through trigger 3548--echo # 3549--echo # 7.a) Doesn't allow concurrent reads 3550connection default; 3551LOCK TABLE t7 WRITE; 3552 3553connection con1; 3554--echo # Sending: 3555--send SELECT * FROM t1; 3556 3557connection default; 3558--echo # Wait until SELECT gets blocked 3559let $wait_condition= 3560 select count(*) = 1 from information_schema.processlist 3561 where state = "Waiting for table metadata lock" and 3562 info = "SELECT * FROM t1"; 3563--source include/wait_condition.inc 3564--echo # Unblock SELECT 3565UNLOCK TABLES; 3566 3567connection con1; 3568--echo # Reaping SELECT 3569--reap 3570 3571connection default; 3572LOCK TABLE t8 WRITE; 3573 3574connection con1; 3575--echo # Sending: 3576--send SELECT * FROM t2; 3577 3578connection default; 3579--echo # Wait until SELECT gets blocked 3580let $wait_condition= 3581 select count(*) = 1 from information_schema.processlist 3582 where state = "Waiting for table metadata lock" and 3583 info = "SELECT * FROM t2"; 3584--source include/wait_condition.inc 3585--echo # Unblock SELECT 3586UNLOCK TABLES; 3587 3588connection con1; 3589--echo # Reaping SELECT 3590--reap 3591 3592connection default; 3593--echo # 3594--echo # 7.b) Is not allowed concurrently to reads 3595SELECT GET_LOCK('mysqltest_lock', 100); 3596 3597connection con1; 3598--echo # Start read by sending SELECT: 3599--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t1; 3600 3601connection con2; 3602--echo # Wait until SELECT gets read lock and starts waiting for user lock 3603let $wait_condition= 3604 select count(*) = 1 from information_schema.processlist 3605 where state = "User lock" and 3606 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t1"; 3607--source include/wait_condition.inc 3608--echo # Sending: 3609--send LOCK TABLES t7 WRITE; 3610 3611connection default; 3612let $wait_condition= 3613 select count(*) = 1 from information_schema.processlist 3614 where state = "Waiting for table metadata lock" and 3615 info = "LOCK TABLES t7 WRITE"; 3616--source include/wait_condition.inc 3617UNLOCK TABLES; 3618 3619--echo # Unblock SELECT. 3620SELECT RELEASE_LOCK('mysqltest_lock'); 3621 3622connection con1; 3623--echo # Reaping SELECT 3624--reap 3625SELECT RELEASE_LOCK('mysqltest_lock'); 3626 3627connection con2; 3628--echo # Reaping LOCK TABLE WRITE 3629--reap 3630UNLOCK TABLES; 3631 3632connection default; 3633SELECT GET_LOCK('mysqltest_lock', 100); 3634 3635connection con1; 3636--echo # Start read by sending SELECT: 3637--send SELECT GET_LOCK('mysqltest_lock', 100) FROM t2; 3638 3639connection con2; 3640--echo # Wait until SELECT gets read lock and starts waiting for user lock 3641let $wait_condition= 3642 select count(*) = 1 from information_schema.processlist 3643 where state = "User lock" and 3644 info = "SELECT GET_LOCK('mysqltest_lock', 100) FROM t2"; 3645--source include/wait_condition.inc 3646--echo # Sending: 3647--send LOCK TABLES t8 WRITE; 3648 3649connection default; 3650let $wait_condition= 3651 select count(*) = 1 from information_schema.processlist 3652 where state = "Waiting for table metadata lock" and 3653 info = "LOCK TABLES t8 WRITE"; 3654--source include/wait_condition.inc 3655UNLOCK TABLES; 3656 3657--echo # Unblock SELECT. 3658SELECT RELEASE_LOCK('mysqltest_lock'); 3659 3660connection con1; 3661--echo # Reaping SELECT 3662--reap 3663SELECT RELEASE_LOCK('mysqltest_lock'); 3664 3665connection con2; 3666--echo # Reaping LOCK TABLE WRITE 3667--reap 3668UNLOCK TABLES; 3669 3670 3671--echo # 3672--echo # 7.c) Blocks concurrent modifications to table 3673connection default; 3674LOCK TABLE t7 WRITE, t8 WRITE; 3675 3676connection con1; 3677--echo # Sending: 3678--send INSERT INTO t1 VALUES (1) 3679 3680connection default; 3681--echo # Wait until INSERT gets blocked 3682let $wait_condition= 3683 select count(*) = 1 from information_schema.processlist 3684 where state = "Waiting for table metadata lock" and 3685 info = "INSERT INTO t1 VALUES (1)"; 3686--source include/wait_condition.inc 3687 3688connection con2; 3689--echo # Sending: 3690--send INSERT INTO t2 VALUES (1) 3691 3692connection default; 3693--echo # Wait until INSERT gets blocked 3694let $wait_condition= 3695 select count(*) = 1 from information_schema.processlist 3696 where state = "Waiting for table metadata lock" and 3697 info = "INSERT INTO t2 VALUES (1)"; 3698--source include/wait_condition.inc 3699 3700--echo # Unblock INSERTs 3701UNLOCK TABLES; 3702 3703connection con1; 3704--echo # Reap INSERT 3705--reap 3706 3707connection con2; 3708--echo # Reap INSERT 3709--reap 3710 3711connection default; 3712--echo # Revert effects from INSERTs 3713DELETE FROM t1 LIMIT 1; 3714DELETE FROM t2 LIMIT 1; 3715 3716--echo # 3717--echo # 7.d) Is blocked by concurrent table modifications 3718SELECT GET_LOCK('mysqltest_lock', 100); 3719 3720connection con1; 3721--echo # Sending: 3722--send INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100)); 3723 3724connection con2; 3725--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 3726let $wait_condition= 3727 select count(*) = 1 from information_schema.processlist 3728 where state = "User lock" and 3729 info = "INSERT INTO t1 VALUES (GET_LOCK('mysqltest_lock', 100))"; 3730--source include/wait_condition.inc 3731 3732--echo # Sending: 3733--send LOCK TABLES t7 WRITE; 3734 3735connection default; 3736--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 3737let $wait_condition= 3738 select count(*) = 1 from information_schema.processlist 3739 where state = "Waiting for table metadata lock" and 3740 info = "LOCK TABLES t7 WRITE"; 3741--source include/wait_condition.inc 3742 3743--echo # Unblock INSERT. 3744SELECT RELEASE_LOCK('mysqltest_lock'); 3745 3746connection con1; 3747--echo # Reaping INSERT 3748--reap 3749SELECT RELEASE_LOCK('mysqltest_lock'); 3750 3751connection con2; 3752--echo # Reaping LOCK TABLES WRITE 3753--reap 3754UNLOCK TABLES; 3755 3756connection default; 3757 3758SELECT GET_LOCK('mysqltest_lock', 100); 3759 3760connection con1; 3761--echo # Sending: 3762--send INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100)); 3763 3764connection con2; 3765--echo # Wait until INSERT gets SW MDL lock and starts waiting for user lock 3766let $wait_condition= 3767 select count(*) = 1 from information_schema.processlist 3768 where state = "User lock" and 3769 info = "INSERT INTO t2 VALUES (GET_LOCK('mysqltest_lock', 100))"; 3770--source include/wait_condition.inc 3771 3772--echo # Sending: 3773--send LOCK TABLES t8 WRITE; 3774 3775connection default; 3776--echo # Wait until LOCK TABLES WRITE is blocked due to INSERT 3777let $wait_condition= 3778 select count(*) = 1 from information_schema.processlist 3779 where state = "Waiting for table metadata lock" and 3780 info = "LOCK TABLES t8 WRITE"; 3781--source include/wait_condition.inc 3782 3783--echo # Unblock INSERT. 3784SELECT RELEASE_LOCK('mysqltest_lock'); 3785 3786connection con1; 3787--echo # Reaping INSERT 3788--reap 3789SELECT RELEASE_LOCK('mysqltest_lock'); 3790 3791connection con2; 3792--echo # Reaping LOCK TABLES WRITE 3793--reap 3794UNLOCK TABLES; 3795 3796connection default; 3797--echo # Revert effects from INSERTs 3798DELETE FROM t1 LIMIT 1; 3799DELETE FROM t2 LIMIT 1; 3800 3801--echo # 3802--echo # 7.e) LOCK TABLES which locks table for write through trigger is blocked 3803--echo # by concurrent transactions which read table. 3804BEGIN; 3805SELECT * FROM t1; 3806 3807connection con1; 3808--echo # Sending: 3809--send LOCK TABLES t7 WRITE; 3810 3811connection default; 3812--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3813let $wait_condition= 3814 select count(*) = 1 from information_schema.processlist 3815 where state = "Waiting for table metadata lock" and 3816 info = "LOCK TABLES t7 WRITE"; 3817--source include/wait_condition.inc 3818--echo # Unblock LOCK TABLES WRITE 3819COMMIT; 3820 3821connection con1; 3822--echo # Reap LOCK TABLES WRITE 3823--reap 3824UNLOCK TABLES; 3825 3826connection default; 3827BEGIN; 3828SELECT * FROM t2; 3829 3830connection con1; 3831--echo # Sending: 3832--send LOCK TABLES t8 WRITE; 3833 3834connection default; 3835--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3836let $wait_condition= 3837 select count(*) = 1 from information_schema.processlist 3838 where state = "Waiting for table metadata lock" and 3839 info = "LOCK TABLES t8 WRITE"; 3840--source include/wait_condition.inc 3841--echo # Unblock LOCK TABLES WRITE 3842COMMIT; 3843 3844connection con1; 3845--echo # Reap LOCK TABLES WRITE 3846--reap 3847UNLOCK TABLES; 3848 3849connection default; 3850--echo # 3851--echo # 7.f) LOCK TABLES which locks table for write through trigger is blocked 3852--echo # by concurrent transactions which modify table. 3853BEGIN; 3854INSERT INTO t1 VALUES (1); 3855 3856connection con1; 3857--echo # Sending: 3858--send LOCK TABLES t7 WRITE; 3859 3860connection default; 3861--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3862let $wait_condition= 3863 select count(*) = 1 from information_schema.processlist 3864 where state = "Waiting for table metadata lock" and 3865 info = "LOCK TABLES t7 WRITE"; 3866--source include/wait_condition.inc 3867--echo # Unblock LOCK TABLES WRITE 3868COMMIT; 3869 3870connection con1; 3871--echo # Reap LOCK TABLES WRITE 3872--reap 3873UNLOCK TABLES; 3874 3875connection default; 3876BEGIN; 3877INSERT INTO t2 VALUES (1); 3878 3879connection con1; 3880--echo # Sending: 3881--send LOCK TABLES t8 WRITE; 3882 3883connection default; 3884--echo # Wait until LOCK TABLES WRITE is blocked due concurrent transaction 3885let $wait_condition= 3886 select count(*) = 1 from information_schema.processlist 3887 where state = "Waiting for table metadata lock" and 3888 info = "LOCK TABLES t8 WRITE"; 3889--source include/wait_condition.inc 3890--echo # Unblock LOCK TABLES WRITE 3891COMMIT; 3892 3893connection con1; 3894--echo # Reap LOCK TABLES WRITE 3895--reap 3896UNLOCK TABLES; 3897 3898connection default; 3899--echo # Revert effects from INSERTs 3900DELETE FROM t1 LIMIT 1; 3901DELETE FROM t2 LIMIT 1; 3902 3903--echo # 3904--echo # 7.g) LOCK TABLES which locks table for write through trigger is not 3905--echo # compatible with LOCK TABLE WRITE. 3906LOCK TABLES t7 WRITE; 3907 3908connection con1; 3909--echo # Sending: 3910--send LOCK TABLES t1 WRITE; 3911 3912connection default; 3913--echo # Wait until LOCK TABLES WRITE is blocked 3914let $wait_condition= 3915 select count(*) = 1 from information_schema.processlist 3916 where state = "Waiting for table metadata lock" and 3917 info = "LOCK TABLES t1 WRITE"; 3918--source include/wait_condition.inc 3919--echo # Unblock LOCK TABLES WRITE 3920UNLOCK TABLES; 3921 3922connection con1; 3923--echo # Reap LOCK TABLES WRITE 3924--reap 3925UNLOCK TABLES; 3926 3927connection default; 3928LOCK TABLES t8 WRITE; 3929 3930connection con1; 3931--echo # Sending: 3932--send LOCK TABLES t2 WRITE; 3933 3934connection default; 3935--echo # Wait until LOCK TABLES WRITE is blocked 3936let $wait_condition= 3937 select count(*) = 1 from information_schema.processlist 3938 where state = "Waiting for table metadata lock" and 3939 info = "LOCK TABLES t2 WRITE"; 3940--source include/wait_condition.inc 3941--echo # Unblock LOCK TABLES WRITE 3942UNLOCK TABLES; 3943 3944connection con1; 3945--echo # Reap LOCK TABLES WRITE 3946--reap 3947UNLOCK TABLES; 3948disconnect con1; 3949disconnect con2; 3950connection default; 3951 3952DROP VIEW v1, v2, v3, v4; 3953DROP TABLES t1, t2, t3, t4, t5, t6, t7, t8; 3954--disable_connect_log 3955 3956 3957--echo # 3958--echo # Test coverage for LOCK TABLES ... READ LOCAL 3959--echo # 3960--enable_connect_log 3961SET @old_concurrent_insert= @@global.concurrent_insert; 3962SET @@global.concurrent_insert= 1; 3963CREATE TABLE t1 (i INT) ENGINE=MyISAM; 3964CREATE TABLE t2 (i INT) ENGINE=InnoDB; 3965CREATE VIEW v1 AS SELECT * FROM t1; 3966CREATE VIEW v2 AS (SELECT * FROM t1) UNION (SELECT * FROM t1); 3967CREATE VIEW v3 AS SELECT * FROM t2; 3968 3969--echo # 3970--echo # 1) READ LOCAL is fully supported for MyISAM. 3971--echo # Concurrent inserts are allowed. 3972--echo # 3973LOCK TABLE t1 READ LOCAL; 3974 3975connect (con1, localhost, root); 3976INSERT INTO t1 VALUES (1); 3977 3978--echo # 3979--echo # Concurrent updates are blocked. 3980--echo # 3981--echo # Sending: 3982--send UPDATE t1 SET i= 2 3983 3984connection default; 3985--echo # Wait until UPDATE is blocked. 3986let $wait_condition= 3987 select count(*) = 1 from information_schema.processlist 3988 where state = "Waiting for table level lock" and 3989 info = "UPDATE t1 SET i= 2"; 3990--source include/wait_condition.inc 3991UNLOCK TABLES; 3992 3993connection con1; 3994--echo # Reaping UPDATE. 3995--reap 3996 3997--echo # 3998--echo # 2) READ LOCAL works even for mergeable views on 3999--echo # top of MyISAM tables. 4000--echo # 4001connection default; 4002LOCK TABLE v1 READ LOCAL; 4003 4004connection con1; 4005INSERT INTO v1 VALUES (1); 4006INSERT INTO t1 VALUES (3); 4007 4008--echo # Concurrent updates are blocked. 4009--echo # Sending: 4010--send UPDATE t1 SET i= 2 4011 4012connection default; 4013--echo # Wait until UPDATE is blocked. 4014let $wait_condition= 4015 select count(*) = 1 from information_schema.processlist 4016 where state = "Waiting for table level lock" and 4017 info = "UPDATE t1 SET i= 2"; 4018--source include/wait_condition.inc 4019UNLOCK TABLES; 4020connection con1; 4021--echo # Reaping UPDATE. 4022--reap 4023 4024--echo # 4025--echo # 3) READ LOCAL doesn't work for non-mergeable views on 4026--echo # top of MyISAM tables. 4027--echo # 4028connection default; 4029LOCK TABLE v2 READ LOCAL; 4030 4031connection con1; 4032--echo # Sending: 4033--send INSERT INTO t1 VALUES (1) 4034 4035connection default; 4036--echo # Wait until INSERT is blocked. 4037let $wait_condition= 4038 select count(*) = 1 from information_schema.processlist 4039 where state = "Waiting for table metadata lock" and 4040 info = "INSERT INTO t1 VALUES (1)"; 4041--source include/wait_condition.inc 4042UNLOCK TABLES; 4043connection con1; 4044--echo # Reaping INSERT. 4045--reap 4046 4047connection default; 4048LOCK TABLE v2 READ LOCAL; 4049 4050connection con1; 4051--echo # Sending: 4052--send UPDATE t1 SET i= 2; 4053 4054connection default; 4055--echo # Wait until UPDATE is blocked. 4056let $wait_condition= 4057 select count(*) = 1 from information_schema.processlist 4058 where state = "Waiting for table metadata lock" and 4059 info = "UPDATE t1 SET i= 2"; 4060--source include/wait_condition.inc 4061UNLOCK TABLES; 4062connection con1; 4063--echo # Reaping UPDATE. 4064--reap 4065 4066--echo # 4067--echo # 4) READ LOCAL locks are upgraded to simple READ locks 4068--echo # for InnoDB tables. So they block both concurrent inserts 4069--echo # and updates. 4070--echo # 4071connection default; 4072LOCK TABLE t2 READ LOCAL; 4073 4074connection con1; 4075--echo # Sending: 4076--send INSERT INTO t2 VALUES (1) 4077 4078connection default; 4079--echo # Wait until INSERT is blocked. 4080let $wait_condition= 4081 select count(*) = 1 from information_schema.processlist 4082 where state = "Waiting for table metadata lock" and 4083 info = "INSERT INTO t2 VALUES (1)"; 4084--source include/wait_condition.inc 4085UNLOCK TABLES; 4086connection con1; 4087--echo # Reaping INSERT. 4088--reap 4089 4090connection default; 4091LOCK TABLE t2 READ LOCAL; 4092 4093connection con1; 4094--echo # Sending: 4095--send UPDATE t2 SET i= 2; 4096 4097connection default; 4098--echo # Wait until UPDATE is blocked. 4099let $wait_condition= 4100 select count(*) = 1 from information_schema.processlist 4101 where state = "Waiting for table metadata lock" and 4102 info = "UPDATE t2 SET i= 2"; 4103--source include/wait_condition.inc 4104UNLOCK TABLES; 4105connection con1; 4106--echo # Reaping UPDATE. 4107--reap 4108 4109--echo # 4110--echo # 5) For mergeable views on top of InnoDB tables READ LOCAL locks are 4111--echo # upgraded to simple READ locks as well. 4112--echo # 4113connection default; 4114LOCK TABLE v3 READ LOCAL; 4115 4116connection con1; 4117--echo # Sending: 4118--send INSERT INTO t2 VALUES (1) 4119 4120connection default; 4121--echo # Wait until INSERT is blocked. 4122let $wait_condition= 4123 select count(*) = 1 from information_schema.processlist 4124 where state = "Waiting for table metadata lock" and 4125 info = "INSERT INTO t2 VALUES (1)"; 4126--source include/wait_condition.inc 4127UNLOCK TABLES; 4128connection con1; 4129--echo # Reaping INSERT. 4130--reap 4131 4132connection default; 4133LOCK TABLE v3 READ LOCAL; 4134 4135connection con1; 4136--echo # Sending: 4137--send UPDATE t2 SET i= 2; 4138 4139connection default; 4140--echo # Wait until UPDATE is blocked. 4141let $wait_condition= 4142 select count(*) = 1 from information_schema.processlist 4143 where state = "Waiting for table metadata lock" and 4144 info = "UPDATE t2 SET i= 2"; 4145--source include/wait_condition.inc 4146UNLOCK TABLES; 4147connection con1; 4148--echo # Reaping UPDATE. 4149--reap 4150 4151connection default; 4152disconnect con1; 4153DROP VIEW v1, v2, v3; 4154DROP TABLES t1, t2; 4155SET @@global.concurrent_insert= @old_concurrent_insert; 4156--disable_connect_log 4157 4158 4159--echo # 4160--echo # Test coverage for interaction between LOCK TABLE ... READ and 4161--echo # concurrent DML which uses LOW_PRIORITY and HIGH_PRIORITY clauses/ 4162--echo # concurrent DML which is executed in @@low_priority_updates=1 mode. 4163--echo # 4164--enable_connect_log 4165--echo # We will use MyISAM to avoid row-locks. 4166CREATE TABLE t1 (i INT) ENGINE=MyISAM; 4167CREATE VIEW v1 AS SELECT * FROM t1; 4168CREATE TABLE t2 (j INT); 4169CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW 4170 INSERT LOW_PRIORITY INTO t1 VALUES (2); 4171CREATE TABLE t3 (k INT); 4172CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW 4173 INSERT INTO t1 VALUES (2); 4174CREATE TABLE tm (i INT) ENGINE=MERGE UNION=(t1); 4175 4176--echo # 4177--echo # 1) Let us check that DML operations with LOW_PRIORITY clauses have 4178--echo # lower priority than pending LOCK TABLE ... READ, thus can't starve 4179--echo # it out. 4180--echo # 4181--echo # Acquire SW lock on the table, to create pending LOCK TABLE ... READ. 4182BEGIN; 4183INSERT INTO t1 VALUES (1); 4184 4185connect (blocker, localhost, root); 4186--echo # Sending: 4187--send LOCK TABLE t1 READ; 4188 4189connection default; 4190--echo # Wait until LOCK TABLE READ gets blocked 4191let $wait_condition= 4192 select count(*) = 1 from information_schema.processlist 4193 where state = "Waiting for table metadata lock" and 4194 info = "LOCK TABLE t1 READ"; 4195--source include/wait_condition.inc 4196 4197connect (con_insert, localhost, root); 4198--echo # INSERT HIGH_PRIORITY should not get blocked 4199INSERT HIGH_PRIORITY INTO t1 VALUES (1); 4200 4201--echo # Sending: 4202--send INSERT LOW_PRIORITY INTO t1 VALUES (1); 4203 4204connection default; 4205--echo # Check that INSERT is blocked 4206let $wait_condition= 4207 select count(*) = 1 from information_schema.processlist 4208 where state = "Waiting for table metadata lock" and 4209 info = "INSERT LOW_PRIORITY INTO t1 VALUES (1)"; 4210--source include/wait_condition.inc 4211 4212connect (con_replace, localhost, root); 4213--echo # Sending: 4214--send REPLACE LOW_PRIORITY INTO t1 VALUES (1); 4215 4216connection default; 4217--echo # Check that REPLACE is blocked 4218let $wait_condition= 4219 select count(*) = 1 from information_schema.processlist 4220 where state = "Waiting for table metadata lock" and 4221 info = "REPLACE LOW_PRIORITY INTO t1 VALUES (1)"; 4222--source include/wait_condition.inc 4223 4224connect (con_update, localhost, root); 4225--echo # Sending: 4226--send UPDATE LOW_PRIORITY t1 SET i= 1; 4227 4228connection default; 4229--echo # Check that UPDATE is blocked 4230let $wait_condition= 4231 select count(*) = 1 from information_schema.processlist 4232 where state = "Waiting for table metadata lock" and 4233 info = "UPDATE LOW_PRIORITY t1 SET i= 1"; 4234--source include/wait_condition.inc 4235 4236connect (con_update_multi, localhost, root); 4237--echo # Sending: 4238--send UPDATE LOW_PRIORITY t1 AS a, t1 AS b SET a.i= 1; 4239 4240connection default; 4241--echo # Check that multi-UPDATE is blocked 4242let $wait_condition= 4243 select count(*) = 1 from information_schema.processlist 4244 where state = "Waiting for table metadata lock" and 4245 info = "UPDATE LOW_PRIORITY t1 AS a, t1 AS b SET a.i= 1"; 4246--source include/wait_condition.inc 4247 4248connect (con_delete, localhost, root); 4249--echo # Sending: 4250--send DELETE LOW_PRIORITY FROM t1 LIMIT 1; 4251 4252connection default; 4253--echo # Check that DELETE is blocked 4254let $wait_condition= 4255 select count(*) = 1 from information_schema.processlist 4256 where state = "Waiting for table metadata lock" and 4257 info = "DELETE LOW_PRIORITY FROM t1 LIMIT 1"; 4258--source include/wait_condition.inc 4259 4260connect (con_delete_multi, localhost, root); 4261--echo # Sending: 4262--send DELETE LOW_PRIORITY FROM a USING t1 AS a, t1 AS b; 4263 4264connection default; 4265--echo # Check that multi-DELETE is blocked 4266let $wait_condition= 4267 select count(*) = 1 from information_schema.processlist 4268 where state = "Waiting for table metadata lock" and 4269 info = "DELETE LOW_PRIORITY FROM a USING t1 AS a, t1 AS b"; 4270--source include/wait_condition.inc 4271 4272connect (con_load, localhost, root); 4273--echo # Sending: 4274--send LOAD DATA LOW_PRIORITY INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE t1 (@dummy,i); 4275 4276connection default; 4277--echo # Check that LOAD DATA is blocked 4278let $wait_condition= 4279 select count(*) = 1 from information_schema.processlist 4280 where state = "Waiting for table metadata lock" and 4281 info = "LOAD DATA LOW_PRIORITY INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE t1 (@dummy,i)"; 4282--source include/wait_condition.inc 4283 4284--echo # 4285--echo # This should work even for views. 4286--echo # 4287connect (con_view, localhost, root); 4288--echo # Sending: 4289--send DELETE LOW_PRIORITY FROM v1; 4290 4291connection default; 4292--echo # Check that DELETE is blocked 4293let $wait_condition= 4294 select count(*) = 1 from information_schema.processlist 4295 where state = "Waiting for table metadata lock" and 4296 info = "DELETE LOW_PRIORITY FROM v1"; 4297--source include/wait_condition.inc 4298 4299--echo # 4300--echo # And when LOW_PRIORITY clause is used in trigger. 4301--echo # 4302connect (con_trigger, localhost, root); 4303--echo # Sending: 4304--send INSERT INTO t2 VALUES (1); 4305 4306connection default; 4307--echo # Check that INSERT in trigger is blocked 4308let $wait_condition= 4309 select count(*) = 1 from information_schema.processlist 4310 where state = "Waiting for table metadata lock" and 4311 info = "INSERT INTO t2 VALUES (1)"; 4312--source include/wait_condition.inc 4313 4314--echo # 4315--echo # And for MERGE tables 4316--echo # 4317connect (con_merge, localhost, root); 4318--echo # Sending: 4319--send DELETE LOW_PRIORITY FROM tm LIMIT 1; 4320 4321connection default; 4322--echo # Check that DELETE from MERGE table is blocked 4323let $wait_condition= 4324 select count(*) = 1 from information_schema.processlist 4325 where state = "Waiting for table metadata lock" and 4326 info = "DELETE LOW_PRIORITY FROM tm LIMIT 1"; 4327--source include/wait_condition.inc 4328 4329--echo # Unblock LOCK TABLE .. READ 4330COMMIT; 4331 4332connection blocker; 4333--echo # Reaping LOCK TABLE .. READ 4334--reap 4335UNLOCK TABLES; 4336 4337--echo # Reap all DML statements. 4338connection con_insert; 4339--reap 4340connection con_replace; 4341--reap 4342connection con_update; 4343--reap 4344connection con_update_multi; 4345--reap 4346connection con_delete; 4347--reap 4348connection con_delete_multi; 4349--reap 4350connection con_load; 4351--reap 4352connection con_view; 4353--reap 4354connection con_trigger; 4355--reap 4356connection con_merge; 4357--reap 4358connection default; 4359 4360--echo # 4361--echo # 2) Let us check that DML operations have lower priority than pending 4362--echo # LOCK TABLE ... READ when @@low_priority_updates mode is on. 4363--echo # 4364--echo # Acquire SW lock on the table, to create pending LOCK TABLE ... READ. 4365BEGIN; 4366INSERT INTO t1 VALUES (0); 4367 4368connection blocker; 4369--echo # Sending: 4370--send LOCK TABLE t1 READ; 4371 4372connection default; 4373--echo # Wait until LOCK TABLE READ gets blocked 4374let $wait_condition= 4375 select count(*) = 1 from information_schema.processlist 4376 where state = "Waiting for table metadata lock" and 4377 info = "LOCK TABLE t1 READ"; 4378--source include/wait_condition.inc 4379 4380connection con_insert; 4381SET @@session.low_priority_updates= 1; 4382--echo # INSERT HIGH_PRIORITY still should not get blocked 4383INSERT HIGH_PRIORITY INTO t1 VALUES (1); 4384 4385--echo # Sending: 4386--send INSERT INTO t1 VALUES (1); 4387 4388connection default; 4389--echo # Check that INSERT is blocked 4390let $wait_condition= 4391 select count(*) = 1 from information_schema.processlist 4392 where state = "Waiting for table metadata lock" and 4393 info = "INSERT INTO t1 VALUES (1)"; 4394--source include/wait_condition.inc 4395 4396connection con_replace; 4397SET @@session.low_priority_updates= 1; 4398--echo # Sending: 4399--send REPLACE INTO t1 VALUES (1); 4400 4401connection default; 4402--echo # Check that REPLACE is blocked 4403let $wait_condition= 4404 select count(*) = 1 from information_schema.processlist 4405 where state = "Waiting for table metadata lock" and 4406 info = "REPLACE INTO t1 VALUES (1)"; 4407--source include/wait_condition.inc 4408 4409connection con_update; 4410SET @@session.low_priority_updates= 1; 4411--echo # Sending: 4412--send UPDATE t1 SET i= 1; 4413 4414connection default; 4415--echo # Check that UPDATE is blocked 4416let $wait_condition= 4417 select count(*) = 1 from information_schema.processlist 4418 where state = "Waiting for table metadata lock" and 4419 info = "UPDATE t1 SET i= 1"; 4420--source include/wait_condition.inc 4421 4422connection con_update_multi; 4423SET @@session.low_priority_updates= 1; 4424--echo # Sending: 4425--send UPDATE t1 AS a, t1 AS b SET a.i= 1; 4426 4427connection default; 4428--echo # Check that multi-UPDATE is blocked 4429let $wait_condition= 4430 select count(*) = 1 from information_schema.processlist 4431 where state = "Waiting for table metadata lock" and 4432 info = "UPDATE t1 AS a, t1 AS b SET a.i= 1"; 4433--source include/wait_condition.inc 4434 4435connection con_delete; 4436SET @@session.low_priority_updates= 1; 4437--echo # Sending: 4438--send DELETE FROM t1 LIMIT 1; 4439 4440connection default; 4441--echo # Check that DELETE is blocked 4442let $wait_condition= 4443 select count(*) = 1 from information_schema.processlist 4444 where state = "Waiting for table metadata lock" and 4445 info = "DELETE FROM t1 LIMIT 1"; 4446--source include/wait_condition.inc 4447 4448connection con_delete_multi; 4449SET @@session.low_priority_updates= 1; 4450--echo # Sending: 4451--send DELETE FROM a USING t1 AS a, t1 AS b; 4452 4453connection default; 4454--echo # Check that multi-DELETE is blocked 4455let $wait_condition= 4456 select count(*) = 1 from information_schema.processlist 4457 where state = "Waiting for table metadata lock" and 4458 info = "DELETE FROM a USING t1 AS a, t1 AS b"; 4459--source include/wait_condition.inc 4460 4461connection con_load; 4462SET @@session.low_priority_updates= 1; 4463--echo # Sending: 4464--send LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE t1 (@dummy,i); 4465 4466connection default; 4467--echo # Check that LOAD DATA is blocked 4468let $wait_condition= 4469 select count(*) = 1 from information_schema.processlist 4470 where state = "Waiting for table metadata lock" and 4471 info = "LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE t1 (@dummy,i)"; 4472--source include/wait_condition.inc 4473 4474connection con_view; 4475SET @@session.low_priority_updates= 1; 4476--echo # Sending: 4477--send DELETE FROM v1; 4478 4479connection default; 4480--echo # Check that DELETE is blocked 4481let $wait_condition= 4482 select count(*) = 1 from information_schema.processlist 4483 where state = "Waiting for table metadata lock" and 4484 info = "DELETE FROM v1"; 4485--source include/wait_condition.inc 4486 4487--echo # 4488--echo # And when table used in trigger. 4489--echo # 4490connection con_trigger; 4491SET @@session.low_priority_updates= 1; 4492--echo # Sending: 4493--send INSERT INTO t3 VALUES (1); 4494 4495connection default; 4496--echo # Check that INSERT in trigger is blocked 4497let $wait_condition= 4498 select count(*) = 1 from information_schema.processlist 4499 where state = "Waiting for table metadata lock" and 4500 info = "INSERT INTO t3 VALUES (1)"; 4501--source include/wait_condition.inc 4502 4503--echo # 4504--echo # And for MERGE tables 4505--echo # 4506connection con_merge; 4507SET @@session.low_priority_updates= 1; 4508--echo # Sending: 4509--send DELETE FROM tm LIMIT 1; 4510 4511connection default; 4512--echo # Check that DELETE from MERGE table is blocked 4513let $wait_condition= 4514 select count(*) = 1 from information_schema.processlist 4515 where state = "Waiting for table metadata lock" and 4516 info = "DELETE FROM tm LIMIT 1"; 4517--source include/wait_condition.inc 4518 4519--echo # Unblock LOCK TABLE .. READ 4520COMMIT; 4521 4522connection blocker; 4523--echo # Reaping LOCK TABLE .. READ 4524--reap 4525UNLOCK TABLES; 4526 4527--echo # Reap all DML statements. 4528connection con_insert; 4529--reap 4530connection con_replace; 4531--reap 4532connection con_update; 4533--reap 4534connection con_update_multi; 4535--reap 4536connection con_delete; 4537--reap 4538connection con_delete_multi; 4539--reap 4540connection con_load; 4541--reap 4542connection con_view; 4543--reap 4544connection con_trigger; 4545--reap 4546connection con_merge; 4547--reap 4548 4549connection default; 4550disconnect blocker; 4551disconnect con_insert; 4552disconnect con_replace; 4553disconnect con_update; 4554disconnect con_update_multi; 4555disconnect con_delete; 4556disconnect con_delete_multi; 4557disconnect con_load; 4558disconnect con_view; 4559disconnect con_trigger; 4560disconnect con_merge; 4561 4562DROP VIEW v1; 4563DROP TABLES tm, t2, t3, t1; 4564--disable_connect_log 4565 4566 4567--echo # 4568--echo # Test for bug #11764618 "DEADLOCK WHEN DDL UNDER LOCK TABLES 4569--echo # WRITE, READ + PREPARE". 4570--echo # 4571--enable_connect_log 4572connect (con1,localhost,root,,test,,); 4573connect (con2,localhost,root,,test,,); 4574connect (con3,localhost,root,,test,,); 4575connection default; 4576 4577create table t1(i int); 4578create table t2(i int); 4579create table t3(i int); 4580create table t4(i int); 4581 4582lock tables t1 write, t3 read; 4583 4584connection con1; 4585begin; 4586select count(*) from t4; 4587--echo # Sending: 4588--send insert into t3 values (1); 4589 4590connection con2; 4591--echo # Wait until 'con1' acquires SR metadata lock on 't4' 4592--echo # and blocks on 't3'. Before WL#6671 waiting has happened 4593--echo # on THR_LOCK lock which led to deadlock. 4594let $wait_condition= 4595 select count(*) = 1 from information_schema.processlist 4596 where state = "Waiting for table metadata lock" and 4597 info = "insert into t3 values (1)"; 4598--source include/wait_condition.inc 4599 4600--echo # Sending: 4601--send rename table t2 to t0, t4 to t2, t0 to t4; 4602 4603connection con3; 4604--echo # Wait until RENAME acquires X metadata lock on 't2' 4605--echo # and blocks on 't4'. 4606let $wait_condition= 4607 select count(*) = 1 from information_schema.processlist 4608 where state = "Waiting for table metadata lock" and 4609 info = "rename table t2 to t0, t4 to t2, t0 to t4"; 4610--source include/wait_condition.inc 4611 4612--echo # Sending: 4613--send prepare stmt1 from 'select * from t1, t2'; 4614 4615connection default; 4616--echo # Wait until PREPARE acquires S metadata lock on 't1' 4617--echo # and blocks on 't2'. 4618let $wait_condition= 4619 select count(*) = 1 from information_schema.processlist 4620 where state = "Waiting for table metadata lock" and 4621 info = "PREPARE stmt1 FROM ..."; 4622--source include/wait_condition.inc 4623 4624--echo # This ALTER TABLE upgrades SNRW lock on t1 to X lock. 4625--echo # In the past this caused deadlock. 4626alter table t1 add column j int; 4627 4628unlock tables; 4629 4630connection con1; 4631--echo # Reap INSERT 4632--reap 4633commit; 4634 4635connection con2; 4636--echo # Reap RENAME 4637--reap 4638 4639connection con3; 4640--echo # Reap PREPARE 4641--reap 4642 4643connection default; 4644disconnect con1; 4645disconnect con2; 4646disconnect con3; 4647drop tables t1, t2, t3, t4; 4648 4649--disable_connect_log 4650 4651 4652# Wait till all disconnects are completed 4653--source include/wait_until_count_sessions.inc 4654