Debian Codegraph @

mysql-test/suite/sys_vars/r/concurrent_insert_func.result

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