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