1DROP TABLE IF EXISTS t1; 2## Creating new table ## 3CREATE TABLE t1 4( 5name VARCHAR(30) 6); 7'#--------------------FN_DYNVARS_018_01-------------------------#' 8SET @start_value= @@global.concurrent_insert; 9## Setting initial value of variable to 1 ## 10SET @@global.concurrent_insert = 1; 11INSERT INTO t1(name) VALUES('Record_1'); 12INSERT INTO t1(name) VALUES('Record_2'); 13INSERT INTO t1(name) VALUES('Record_3'); 14## locking table ## 15LOCK TABLE t1 READ LOCAL; 16## Creating new connection to insert some rows in table ## 17connect test_con1,localhost,root,,; 18connection test_con1; 19## New records should come at the end of all rows ## 20INSERT INTO t1(name) VALUES('Record_4'); 21SELECT * FROM t1; 22name 23Record_1 24Record_2 25Record_3 26Record_4 27## unlocking tables ## 28connection default; 29UNLOCK TABLES; 30## deleting record to create hole in table ## 31DELETE FROM t1 WHERE name ='Record_2'; 32'#--------------------FN_DYNVARS_018_02-------------------------#' 33LOCK TABLE t1 READ LOCAL; 34connection test_con1; 35SET @@global.concurrent_insert=1; 36## send INSERT which should be blocked until unlock of the table ## 37INSERT INTO t1(name) VALUES('Record_7'); 38connection default; 39## show processlist info and state ## 40SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST 41WHERE state= "Waiting for table level lock" AND info LIKE "INSERT INTO t1%"; 42state info 43Waiting for table level lock INSERT INTO t1(name) VALUES('Record_7') 44## table contents befor UNLOCK ## 45SELECT * FROM t1; 46name 47Record_1 48Record_3 49Record_4 50UNLOCK TABLES; 51## table contens after UNLOCK ## 52SELECT * FROM t1; 53name 54Record_1 55Record_7 56Record_3 57Record_4 58INSERT INTO t1(name) VALUES('Record_6'); 59connection test_con1; 60SELECT * FROM t1; 61name 62Record_1 63Record_7 64Record_3 65Record_4 66Record_6 67connection default; 68'#--------------------FN_DYNVARS_018_03-------------------------#' 69## lock table and connect with connection1 ## 70LOCK TABLE t1 READ LOCAL; 71connection test_con1; 72## setting value of concurrent_insert to 2 ## 73SET @@global.concurrent_insert=2; 74## Inserting record in table, record should go at the end of the table ## 75INSERT INTO t1(name) VALUES('Record_5'); 76SELECT * FROM t1; 77name 78Record_1 79Record_7 80Record_3 81Record_4 82Record_6 83Record_5 84SELECT @@concurrent_insert; 85@@concurrent_insert 86ALWAYS 87connection default; 88## Unlocking table ## 89UNLOCK TABLES; 90SELECT * FROM t1; 91name 92Record_1 93Record_7 94Record_3 95Record_4 96Record_6 97Record_5 98## Inserting new row, this should go in the hole ## 99INSERT INTO t1(name) VALUES('Record_6'); 100SELECT * FROM t1; 101name 102Record_1 103Record_7 104Record_3 105Record_4 106Record_6 107Record_5 108Record_6 109## connection test_con1 ## 110DELETE FROM t1 WHERE name ='Record_3'; 111SELECT * FROM t1; 112name 113Record_1 114Record_7 115Record_4 116Record_6 117Record_5 118Record_6 119## Dropping table ## 120DROP TABLE t1; 121disconnect test_con1; 122SET @@global.concurrent_insert= @start_value; 123