Debian Codegraph @

mysql-test/suite/test_service_sql_api/r/test_sql_lock.result

raw
1 # Creating table t1
2 CREATE TABLE t1(c1 INT NOT NULL, c2 INT);
3 INSERT INTO t1 VALUES (1,1), (2,2), (3,1), (4,2), (5,1), (6,2), (7,3);
4 # Creating table t2
5 CREATE TABLE t2(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1));
6 INSERT INTO t2 VALUES (1,1), (2,2), (3,1), (4,2), (5,1), (6,2), (7,3);
7 # Count of elements in table t1
8 SELECT COUNT(*) FROM t1;
9 COUNT(*)
10 7
11 ###################################################################
12 # Run plugin
13 ###################################################################
14 INSTALL PLUGIN test_sql_lock SONAME 'TEST_SQL_LOCK';
15 ###################################################################
16 # Stop plugin
17 ###################################################################
18 UNINSTALL PLUGIN test_sql_lock;
19 ###################################################################
20 # Plugin log
21 ###################################################################
22 ========================================================================
23 Test in a server thread
24
25 Opening Session 1
26 Opening Session 2
27
28 ===================================================================
29 Isolation Level : READ COMMITTED
30 ===================================================================
31 -------------------------------------------------------------------
32 Session 1 :
33 -------------------------------------------------------------------
34 SET AUTOCOMMIT = 0
35 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
36 INSERT INTO test.t1 VALUES (8,4)
37 -------------------------------------------------------------------
38 Session 2 :
39 -------------------------------------------------------------------
40 SET AUTOCOMMIT = 0
41 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
42 SELECT COUNT(*) FROM test.t1
43 COUNT(*)
44 7
45 -------------------------------------------------------------------
46 Session 1 :
47 -------------------------------------------------------------------
48 COMMIT
49 SET AUTOCOMMIT = 1
50 -------------------------------------------------------------------
51 Session 2 :
52 -------------------------------------------------------------------
53 SELECT COUNT(*) FROM test.t1
54 COUNT(*)
55 8
56 COMMIT
57 SET AUTOCOMMIT = 1
58 SELECT COUNT(*) FROM test.t1
59 COUNT(*)
60 8
61
62 ===================================================================
63 Isolation Level : READ UNCOMMITTED
64 ===================================================================
65 -------------------------------------------------------------------
66 Session 1 :
67 -------------------------------------------------------------------
68 SET AUTOCOMMIT = 0
69 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
70 INSERT INTO test.t1 VALUES (9,5)
71 -------------------------------------------------------------------
72 Session 2 :
73 -------------------------------------------------------------------
74 SET AUTOCOMMIT = 0
75 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
76 SELECT COUNT(*) FROM test.t1
77 COUNT(*)
78 9
79 -------------------------------------------------------------------
80 Session 1 :
81 -------------------------------------------------------------------
82 COMMIT
83 SET AUTOCOMMIT = 1
84 -------------------------------------------------------------------
85 Session 2 :
86 -------------------------------------------------------------------
87 SELECT COUNT(*) FROM test.t1
88 COUNT(*)
89 9
90 COMMIT
91 SET AUTOCOMMIT = 1
92 SELECT COUNT(*) FROM test.t1
93 COUNT(*)
94 9
95
96 ===================================================================
97 Isolation Level : REPEATABLE READ
98 ===================================================================
99 -------------------------------------------------------------------
100 Session 1 :
101 -------------------------------------------------------------------
102 SET AUTOCOMMIT = 0
103 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
104 SELECT COUNT(*) FROM test.t1
105 COUNT(*)
106 9
107 -------------------------------------------------------------------
108 Session 2 :
109 -------------------------------------------------------------------
110 SET AUTOCOMMIT = 0
111 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
112 INSERT INTO test.t1 VALUES (10,6)
113 COMMIT
114 SET AUTOCOMMIT = 1
115 -------------------------------------------------------------------
116 Session 1 :
117 -------------------------------------------------------------------
118 SELECT COUNT(*) FROM test.t1
119 COUNT(*)
120 9
121 COMMIT
122 SELECT COUNT(*) FROM test.t1
123 COUNT(*)
124 10
125 SET AUTOCOMMIT = 1
126
127 ===================================================================
128 Isolation Level : SERIALIZABLE
129 ===================================================================
130 -------------------------------------------------------------------
131 Session 1 :
132 -------------------------------------------------------------------
133 SET AUTOCOMMIT = 0
134 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
135 INSERT INTO test.t1 VALUES (11,7)
136 -------------------------------------------------------------------
137 Session 2 :
138 -------------------------------------------------------------------
139 SET AUTOCOMMIT = 0
140 SELECT COUNT(*) FROM test.t1
141 COUNT(*)
142 10
143 -------------------------------------------------------------------
144 Session 1 :
145 -------------------------------------------------------------------
146 COMMIT
147 SET AUTOCOMMIT = 1
148 -------------------------------------------------------------------
149 Session 2 :
150 -------------------------------------------------------------------
151 SELECT COUNT(*) FROM test.t1
152 COUNT(*)
153 10
154 COMMIT
155 SELECT COUNT(*) FROM test.t1
156 COUNT(*)
157 11
158 SET AUTOCOMMIT = 1
159
160 ===================================================================
161 Locking done by a SQL transaction
162 ===================================================================
163 -------------------------------------------------------------------
164 Session 1 :
165 -------------------------------------------------------------------
166 BEGIN
167 DELETE FROM test.t1 WHERE c1 = 11
168 SELECT COUNT(*) FROM test.t1
169 COUNT(*)
170 10
171 -------------------------------------------------------------------
172 Session 2 :
173 -------------------------------------------------------------------
174 SELECT COUNT(*) FROM test.t1
175 COUNT(*)
176 11
177 -------------------------------------------------------------------
178 Session 1 :
179 -------------------------------------------------------------------
180 COMMIT
181 -------------------------------------------------------------------
182 Session 2 :
183 -------------------------------------------------------------------
184 SELECT COUNT(*) FROM test.t1
185 COUNT(*)
186 10
187
188 Closing Session 1
189 Closing Session 2
190
191 Opening Session 1
192 Opening Session 2
193 Opening Session 3
194 ===================================================================
195 Locking using LOCK TABLE
196 ===================================================================
197 -------------------------------------------------------------------
198 Session 1 :
199 -------------------------------------------------------------------
200 LOCK TABLE test.t1 READ
201 /*statement should return an error because table t1 is locked with a READ lock*/
202 INSERT INTO test.t1 VALUES (11,7)
203 error: 1099
204 error msg: Table 't1' was locked with a READ lock and can't be updated
205 SHOW OPEN TABLES FROM test LIKE 't1'
206 Database Table In_use Name_locked
207 test t1 1 0
208 -------------------------------------------------------------------
209 Session 2 :
210 -------------------------------------------------------------------
211 LOCK TABLE test.t2 WRITE
212 SHOW OPEN TABLES FROM test LIKE 't1'
213 Database Table In_use Name_locked
214 test t1 1 0
215 SHOW OPEN TABLES FROM test LIKE 't2'
216 Database Table In_use Name_locked
217 test t2 1 0
218 -------------------------------------------------------------------
219 Session 1 :
220 -------------------------------------------------------------------
221 SHOW OPEN TABLES FROM test LIKE 't2'
222 Database Table In_use Name_locked
223 test t2 1 0
224
225 Closing Session 1
226 ===================================================================
227 Check session 1's lock after its close
228 ===================================================================
229 -------------------------------------------------------------------
230 Session 2 :
231 -------------------------------------------------------------------
232 SHOW OPEN TABLES FROM test LIKE 't1'
233 Database Table In_use Name_locked
234 test t1 0 0
235 SHOW OPEN TABLES FROM test LIKE 't2'
236 Database Table In_use Name_locked
237 test t2 1 0
238
239 Closing Session 2
240
241 ===================================================================
242 Check session 2's lock after its close
243 ===================================================================
244 -------------------------------------------------------------------
245 Session 3 :
246 -------------------------------------------------------------------
247 SHOW OPEN TABLES FROM test LIKE 't1'
248 Database Table In_use Name_locked
249 test t1 0 0
250 SHOW OPEN TABLES FROM test LIKE 't2'
251 Database Table In_use Name_locked
252 test t2 0 0
253
254 Closing Session 3
255
256 Follows threaded run
257 ========================================================================
258 init thread
259
260 Opening Session 1
261 Opening Session 2
262
263 ===================================================================
264 Isolation Level : READ COMMITTED
265 ===================================================================
266 -------------------------------------------------------------------
267 Session 1 :
268 -------------------------------------------------------------------
269 SET AUTOCOMMIT = 0
270 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
271 INSERT INTO test.t1 VALUES (8,4)
272 -------------------------------------------------------------------
273 Session 2 :
274 -------------------------------------------------------------------
275 SET AUTOCOMMIT = 0
276 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
277 SELECT COUNT(*) FROM test.t1
278 COUNT(*)
279 10
280 -------------------------------------------------------------------
281 Session 1 :
282 -------------------------------------------------------------------
283 COMMIT
284 SET AUTOCOMMIT = 1
285 -------------------------------------------------------------------
286 Session 2 :
287 -------------------------------------------------------------------
288 SELECT COUNT(*) FROM test.t1
289 COUNT(*)
290 11
291 COMMIT
292 SET AUTOCOMMIT = 1
293 SELECT COUNT(*) FROM test.t1
294 COUNT(*)
295 11
296
297 ===================================================================
298 Isolation Level : READ UNCOMMITTED
299 ===================================================================
300 -------------------------------------------------------------------
301 Session 1 :
302 -------------------------------------------------------------------
303 SET AUTOCOMMIT = 0
304 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
305 INSERT INTO test.t1 VALUES (9,5)
306 -------------------------------------------------------------------
307 Session 2 :
308 -------------------------------------------------------------------
309 SET AUTOCOMMIT = 0
310 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
311 SELECT COUNT(*) FROM test.t1
312 COUNT(*)
313 12
314 -------------------------------------------------------------------
315 Session 1 :
316 -------------------------------------------------------------------
317 COMMIT
318 SET AUTOCOMMIT = 1
319 -------------------------------------------------------------------
320 Session 2 :
321 -------------------------------------------------------------------
322 SELECT COUNT(*) FROM test.t1
323 COUNT(*)
324 12
325 COMMIT
326 SET AUTOCOMMIT = 1
327 SELECT COUNT(*) FROM test.t1
328 COUNT(*)
329 12
330
331 ===================================================================
332 Isolation Level : REPEATABLE READ
333 ===================================================================
334 -------------------------------------------------------------------
335 Session 1 :
336 -------------------------------------------------------------------
337 SET AUTOCOMMIT = 0
338 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
339 SELECT COUNT(*) FROM test.t1
340 COUNT(*)
341 12
342 -------------------------------------------------------------------
343 Session 2 :
344 -------------------------------------------------------------------
345 SET AUTOCOMMIT = 0
346 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
347 INSERT INTO test.t1 VALUES (10,6)
348 COMMIT
349 SET AUTOCOMMIT = 1
350 -------------------------------------------------------------------
351 Session 1 :
352 -------------------------------------------------------------------
353 SELECT COUNT(*) FROM test.t1
354 COUNT(*)
355 12
356 COMMIT
357 SELECT COUNT(*) FROM test.t1
358 COUNT(*)
359 13
360 SET AUTOCOMMIT = 1
361
362 ===================================================================
363 Isolation Level : SERIALIZABLE
364 ===================================================================
365 -------------------------------------------------------------------
366 Session 1 :
367 -------------------------------------------------------------------
368 SET AUTOCOMMIT = 0
369 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
370 INSERT INTO test.t1 VALUES (11,7)
371 -------------------------------------------------------------------
372 Session 2 :
373 -------------------------------------------------------------------
374 SET AUTOCOMMIT = 0
375 SELECT COUNT(*) FROM test.t1
376 COUNT(*)
377 13
378 -------------------------------------------------------------------
379 Session 1 :
380 -------------------------------------------------------------------
381 COMMIT
382 SET AUTOCOMMIT = 1
383 -------------------------------------------------------------------
384 Session 2 :
385 -------------------------------------------------------------------
386 SELECT COUNT(*) FROM test.t1
387 COUNT(*)
388 13
389 COMMIT
390 SELECT COUNT(*) FROM test.t1
391 COUNT(*)
392 14
393 SET AUTOCOMMIT = 1
394
395 ===================================================================
396 Locking done by a SQL transaction
397 ===================================================================
398 -------------------------------------------------------------------
399 Session 1 :
400 -------------------------------------------------------------------
401 BEGIN
402 DELETE FROM test.t1 WHERE c1 = 11
403 SELECT COUNT(*) FROM test.t1
404 COUNT(*)
405 13
406 -------------------------------------------------------------------
407 Session 2 :
408 -------------------------------------------------------------------
409 SELECT COUNT(*) FROM test.t1
410 COUNT(*)
411 14
412 -------------------------------------------------------------------
413 Session 1 :
414 -------------------------------------------------------------------
415 COMMIT
416 -------------------------------------------------------------------
417 Session 2 :
418 -------------------------------------------------------------------
419 SELECT COUNT(*) FROM test.t1
420 COUNT(*)
421 13
422
423 Closing Session 1
424 Closing Session 2
425
426 Opening Session 1
427 Opening Session 2
428 Opening Session 3
429 ===================================================================
430 Locking using LOCK TABLE
431 ===================================================================
432 -------------------------------------------------------------------
433 Session 1 :
434 -------------------------------------------------------------------
435 LOCK TABLE test.t1 READ
436 /*statement should return an error because table t1 is locked with a READ lock*/
437 INSERT INTO test.t1 VALUES (11,7)
438 error: 1099
439 error msg: Table 't1' was locked with a READ lock and can't be updated
440 SHOW OPEN TABLES FROM test LIKE 't1'
441 Database Table In_use Name_locked
442 test t1 1 0
443 -------------------------------------------------------------------
444 Session 2 :
445 -------------------------------------------------------------------
446 LOCK TABLE test.t2 WRITE
447 SHOW OPEN TABLES FROM test LIKE 't1'
448 Database Table In_use Name_locked
449 test t1 1 0
450 SHOW OPEN TABLES FROM test LIKE 't2'
451 Database Table In_use Name_locked
452 test t2 1 0
453 -------------------------------------------------------------------
454 Session 1 :
455 -------------------------------------------------------------------
456 SHOW OPEN TABLES FROM test LIKE 't2'
457 Database Table In_use Name_locked
458 test t2 1 0
459
460 Closing Session 1
461 ===================================================================
462 Check session 1's lock after its close
463 ===================================================================
464 -------------------------------------------------------------------
465 Session 2 :
466 -------------------------------------------------------------------
467 SHOW OPEN TABLES FROM test LIKE 't1'
468 Database Table In_use Name_locked
469 test t1 0 0
470 SHOW OPEN TABLES FROM test LIKE 't2'
471 Database Table In_use Name_locked
472 test t2 1 0
473
474 Closing Session 2
475
476 ===================================================================
477 Check session 2's lock after its close
478 ===================================================================
479 -------------------------------------------------------------------
480 Session 3 :
481 -------------------------------------------------------------------
482 SHOW OPEN TABLES FROM test LIKE 't1'
483 Database Table In_use Name_locked
484 test t1 0 0
485 SHOW OPEN TABLES FROM test LIKE 't2'
486 Database Table In_use Name_locked
487 test t2 0 0
488
489 Closing Session 3
490
491 deinit thread
492 SHOW OPEN TABLES FROM test LIKE 't1';
493 Database Table In_use Name_locked
494 test t1 0 0
495 SHOW OPEN TABLES FROM test LIKE 't2';
496 Database Table In_use Name_locked
497 test t2 0 0
498 # Dropping the created tables
499 DROP TABLE t1;
500 DROP TABLE t2;