Debian Codegraph @

mysql-test/suite/memcached/r/memc256_lock_table_write.result

raw
1 SET @old_innodb_api_trx_level = @@global.innodb_api_trx_level;
2 INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
3 "innodb_only", "innodb_only", "innodb_only");
4 INSERT INTO config_options VALUES("separator", "|");
5 INSERT INTO containers VALUES ("desc_t1", "test", "t1",
6 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
7 connect c_diag,localhost, root,,;
8 connection default;
9 #
10 # Testing the scenario with innodb_api_trx_level = 0
11 #
12 SET @@global.innodb_api_trx_level = 0;
13 USE test;
14 DROP TABLE IF EXISTS t1;
15 CREATE TABLE t1 (c1 VARCHAR(32),
16 c2 VARCHAR(1024),
17 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
18 ENGINE = INNODB;
19 INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
20 INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
21 INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
22 INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
23 INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
24 Warnings:
25 Warning 1681 'InnoDB Memcached Plugin' is deprecated and will be removed in a future release.
26 SET autocommit=0;
27 LOCK TABLES t1 WRITE;
28 Get D...
29 Get has returned Darmstadt
30 Set D to new value
31 Set returned
32 Get D...
33 Get has returned Darmstadt
34 connection c_diag;
35 SELECT lock_mode,lock_status FROM performance_schema.data_locks
36 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
37 lock_mode lock_status
38 IX WAITING
39 X GRANTED
40 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
41 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
42 lock_type lock_duration lock_status
43 SHARED_NO_READ_WRITE TRANSACTION GRANTED
44 connection default;
45 SELECT c2 FROM t1 WHERE c1='D';
46 c2
47 Darmstadt
48 COMMIT;
49 SET autocommit=1;
50 SELECT c2 FROM t1 WHERE c1='D';
51 c2
52 new value
53 connection c_diag;
54 SELECT lock_mode,lock_status FROM performance_schema.data_locks
55 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
56 lock_mode lock_status
57 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
58 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
59 lock_type lock_duration lock_status
60 SHARED_NO_READ_WRITE TRANSACTION GRANTED
61 connection default;
62 UNLOCK TABLES;
63 connection c_diag;
64 SELECT lock_mode,lock_status FROM performance_schema.data_locks
65 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
66 lock_mode lock_status
67 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
68 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
69 lock_type lock_duration lock_status
70 SELECT c2 FROM t1 WHERE c1='D';
71 c2
72 new value
73 connection default;
74 UNINSTALL PLUGIN daemon_memcached;
75 DROP TABLE t1;
76 #
77 # Testing the scenario with innodb_api_trx_level = 1
78 #
79 SET @@global.innodb_api_trx_level = 1;
80 USE test;
81 DROP TABLE IF EXISTS t1;
82 CREATE TABLE t1 (c1 VARCHAR(32),
83 c2 VARCHAR(1024),
84 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
85 ENGINE = INNODB;
86 INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
87 INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
88 INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
89 INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
90 INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
91 Warnings:
92 Warning 1681 'InnoDB Memcached Plugin' is deprecated and will be removed in a future release.
93 SET autocommit=0;
94 LOCK TABLES t1 WRITE;
95 Get D...
96 Get has returned Darmstadt
97 Set D to new value
98 Set returned
99 Get D...
100 Get has returned Darmstadt
101 connection c_diag;
102 SELECT lock_mode,lock_status FROM performance_schema.data_locks
103 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
104 lock_mode lock_status
105 IX WAITING
106 X GRANTED
107 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
108 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
109 lock_type lock_duration lock_status
110 SHARED_NO_READ_WRITE TRANSACTION GRANTED
111 connection default;
112 SELECT c2 FROM t1 WHERE c1='D';
113 c2
114 Darmstadt
115 COMMIT;
116 SET autocommit=1;
117 SELECT c2 FROM t1 WHERE c1='D';
118 c2
119 new value
120 connection c_diag;
121 SELECT lock_mode,lock_status FROM performance_schema.data_locks
122 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
123 lock_mode lock_status
124 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
125 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
126 lock_type lock_duration lock_status
127 SHARED_NO_READ_WRITE TRANSACTION GRANTED
128 connection default;
129 UNLOCK TABLES;
130 connection c_diag;
131 SELECT lock_mode,lock_status FROM performance_schema.data_locks
132 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
133 lock_mode lock_status
134 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
135 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
136 lock_type lock_duration lock_status
137 SELECT c2 FROM t1 WHERE c1='D';
138 c2
139 new value
140 connection default;
141 UNINSTALL PLUGIN daemon_memcached;
142 DROP TABLE t1;
143 #
144 # Testing the scenario with innodb_api_trx_level = 2
145 #
146 SET @@global.innodb_api_trx_level = 2;
147 USE test;
148 DROP TABLE IF EXISTS t1;
149 CREATE TABLE t1 (c1 VARCHAR(32),
150 c2 VARCHAR(1024),
151 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
152 ENGINE = INNODB;
153 INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
154 INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
155 INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
156 INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
157 INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
158 Warnings:
159 Warning 1681 'InnoDB Memcached Plugin' is deprecated and will be removed in a future release.
160 SET autocommit=0;
161 LOCK TABLES t1 WRITE;
162 Get D...
163 Get has returned Darmstadt
164 Set D to new value
165 Set returned
166 Get D...
167 Get has returned Darmstadt
168 connection c_diag;
169 SELECT lock_mode,lock_status FROM performance_schema.data_locks
170 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
171 lock_mode lock_status
172 IX WAITING
173 X GRANTED
174 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
175 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
176 lock_type lock_duration lock_status
177 SHARED_NO_READ_WRITE TRANSACTION GRANTED
178 connection default;
179 SELECT c2 FROM t1 WHERE c1='D';
180 c2
181 Darmstadt
182 COMMIT;
183 SET autocommit=1;
184 SELECT c2 FROM t1 WHERE c1='D';
185 c2
186 new value
187 connection c_diag;
188 SELECT lock_mode,lock_status FROM performance_schema.data_locks
189 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
190 lock_mode lock_status
191 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
192 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
193 lock_type lock_duration lock_status
194 SHARED_NO_READ_WRITE TRANSACTION GRANTED
195 connection default;
196 UNLOCK TABLES;
197 connection c_diag;
198 SELECT lock_mode,lock_status FROM performance_schema.data_locks
199 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
200 lock_mode lock_status
201 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
202 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
203 lock_type lock_duration lock_status
204 SELECT c2 FROM t1 WHERE c1='D';
205 c2
206 new value
207 connection default;
208 UNINSTALL PLUGIN daemon_memcached;
209 DROP TABLE t1;
210 #
211 # Testing the scenario with innodb_api_trx_level = 3
212 #
213 SET @@global.innodb_api_trx_level = 3;
214 USE test;
215 DROP TABLE IF EXISTS t1;
216 CREATE TABLE t1 (c1 VARCHAR(32),
217 c2 VARCHAR(1024),
218 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
219 ENGINE = INNODB;
220 INSERT INTO t1 VALUES ('D', 'Darmstadt', 0, 0, 0);
221 INSERT INTO t1 VALUES ('B', 'Berlin', 0, 0, 0);
222 INSERT INTO t1 VALUES ('C', 'Cottbus', 0, 0 ,0);
223 INSERT INTO t1 VALUES ('H', 'Hamburg', 0, 0, 0);
224 INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
225 Warnings:
226 Warning 1681 'InnoDB Memcached Plugin' is deprecated and will be removed in a future release.
227 SET autocommit=0;
228 LOCK TABLES t1 WRITE;
229 Get D...
230 Get has returned
231 Set D to new value
232 Set returned
233 Get D...
234 Get has returned
235 connection c_diag;
236 SELECT lock_mode,lock_status FROM performance_schema.data_locks
237 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
238 lock_mode lock_status
239 IS WAITING
240 IS WAITING
241 IX WAITING
242 X GRANTED
243 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
244 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
245 lock_type lock_duration lock_status
246 SHARED_NO_READ_WRITE TRANSACTION GRANTED
247 connection default;
248 SELECT c2 FROM t1 WHERE c1='D';
249 c2
250 Darmstadt
251 COMMIT;
252 SET autocommit=1;
253 SELECT c2 FROM t1 WHERE c1='D';
254 c2
255 new value
256 connection c_diag;
257 SELECT lock_mode,lock_status FROM performance_schema.data_locks
258 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
259 lock_mode lock_status
260 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
261 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
262 lock_type lock_duration lock_status
263 SHARED_NO_READ_WRITE TRANSACTION GRANTED
264 connection default;
265 UNLOCK TABLES;
266 connection c_diag;
267 SELECT lock_mode,lock_status FROM performance_schema.data_locks
268 WHERE object_schema='test' AND object_name='t1' AND lock_type='TABLE';
269 lock_mode lock_status
270 SELECT lock_type,lock_duration,lock_status FROM performance_schema.metadata_locks
271 WHERE object_schema='test' AND object_name='t1' AND object_type='TABLE';
272 lock_type lock_duration lock_status
273 SELECT c2 FROM t1 WHERE c1='D';
274 c2
275 new value
276 connection default;
277 UNINSTALL PLUGIN daemon_memcached;
278 DROP TABLE t1;
279 disconnect c_diag;
280 DROP DATABASE innodb_memcache;
281 SET @@global.innodb_api_trx_level = @old_innodb_api_trx_level;