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 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;