Debian Codegraph @

mysql-test/suite/auth_sec/r/acl_tables_row_locking.result

raw
1 #
2 # WL#14087 Avoid locking reads from ACL tables
3 #
4 # Test scenarios that involve reading ACL tables and updating
5 # ACL tables concurrently. This tests are related to WL#14087.
6 #
7 # Following ACL tables are considered for testing:
8 # mysql.user
9 # mysql.db
10 # mysql.tables_priv
11 # mysql.columns_priv
12 # mysql.procs_priv
13 # mysql.proxies_priv
14 # mysql.role_edges
15 # mysql.default_roles
16 # mysql.global_grants
17 # mysql.password_history
18 #
19 # Setup schema for the test
20 # We create new users so that we can populate rows in all acl tables.
21 # We avoid using 'root' user here, because that might affect execution
22 # of other tests. Also, we would need more than one users for below
23 # test cases.
24 #
25 CREATE TABLE t1 (f1 INT);
26 CREATE PROCEDURE proc1() BEGIN SELECT * FROM t1; END //
27 # User accounts used for testing.
28 CREATE USER u1;
29 GRANT SELECT ON mysql.* TO u1;
30 GRANT SELECT ON performance_schema.* TO u1;
31 GRANT LOCK TABLES ON *.* TO u1;
32 GRANT RELOAD ON *.* TO u1;
33 CREATE USER writer1;
34 GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO writer1;
35 GRANT SELECT ON performance_schema.* TO writer1;
36 GRANT CREATE, DROP ON *.* TO writer1;
37 GRANT CREATE USER ON *.* TO writer1;
38 CREATE USER writer2;
39 GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO writer2;
40 GRANT SELECT ON performance_schema.* TO writer2;
41 GRANT CREATE, DROP ON *.* TO writer2;
42 GRANT CREATE USER ON *.* TO writer2;
43 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO writer2;
44 GRANT REFERENCES ON mysql.user TO writer2;
45 # Populate all ACL tables with some rows for u2.
46 # This user is used for checking purpose.
47 CREATE USER u2 PASSWORD HISTORY 3;
48 GRANT SELECT ON test.* TO u2;
49 GRANT SELECT ON test.t1 TO u2;
50 GRANT SELECT (f1) ON test.t1 TO u2;
51 GRANT EXECUTE ON PROCEDURE test.proc1 TO u2;
52 GRANT PROXY ON writer1 TO u2;
53 GRANT BACKUP_ADMIN ON *.* to u2;
54 GRANT writer1 TO u2;
55 SET DEFAULT ROLE writer1 TO u2;
56 ALTER USER u2 IDENTIFIED BY 'newpass1';
57 # Verify that all ACL tables has some rows for u2.
58 SELECT User FROM mysql.user WHERE User='u2';
59 User
60 u2
61 SELECT User FROM mysql.db WHERE User='u2';
62 User
63 u2
64 SELECT User FROM mysql.tables_priv WHERE User='u2';
65 User
66 u2
67 SELECT User FROM mysql.columns_priv WHERE User='u2';
68 User
69 u2
70 SELECT User FROM mysql.procs_priv WHERE User='u2';
71 User
72 u2
73 SELECT User FROM mysql.proxies_priv WHERE User='u2';
74 User
75 u2
76 SELECT User FROM mysql.global_grants WHERE User='u2';
77 User
78 u2
79 SELECT User FROM mysql.password_history WHERE User='u2';
80 User
81 u2
82 SELECT User FROM mysql.default_roles WHERE User='u2';
83 User
84 u2
85 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
86 To_user
87 u2
88 # Backup user 'u2' and rename it as 'u2x'. This row
89 # is used to test DML on mysql.user.
90 CREATE TABLE user_bk AS SELECT * FROM mysql.user WHERE User='u2';
91 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
92 CREATE TABLE user_copy AS SELECT * FROM mysql.user LIMIT 0;
93 CREATE PROCEDURE proc2 ()
94 BEGIN
95 UPDATE mysql.user SET user='u2x' WHERE User='u2';
96 END|
97 CREATE FUNCTION func1 () RETURNS INTEGER
98 BEGIN
99 UPDATE user_copy SET User='u2x'
100 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
101 RETURN 0;
102 END|
103 GRANT EXECUTE ON PROCEDURE test.proc2 TO writer2;
104 GRANT EXECUTE ON FUNCTION test.func1 TO writer2;
105 SET GLOBAL optimizer_switch = "skip_scan=off";
106 # Connections used for testing.
107 # Case 1:
108 # Execute SELECT FOR UPDATE on ACL table after a concurrent
109 # connection has started reading ACL table.
110 #
111 # SQL commands using ACL tables for read operation would skip
112 # acquiring row lock. This will enable execution of
113 # 'SELECT ... FOR UPDATE' statement without blocking.
114 # connection which is reading a ACL table. We test all the ACL tables.
115 #
116 # Run the test for all isolation level for table mysql.user
117 "Case 1.1: Start a transaction in SERIALIZABLE"
118 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
119 START TRANSACTION;
120 SELECT User FROM mysql.user WHERE User='u2';
121 User
122 u2
123 Warnings:
124 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
125 # Make sure no read row lock is held on row 'u2'.
126 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
127 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
128 FROM performance_schema.data_locks
129 WHERE LOCK_TYPE='RECORD' AND
130 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
131 "Allow the same row to be read for update by SELECT FOR UPDATE"
132 connection update_con
133 SELECT User FROM mysql.user
134 WHERE User='u2' FOR UPDATE;
135 User
136 u2
137 # Make sure X record locks are held on row 'u2'
138 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
139 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
140 FROM performance_schema.data_locks
141 WHERE LOCK_TYPE='RECORD' AND
142 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
143 tab INDEX_NAME LOCK_MODE Has_lock
144 mysql.user PRIMARY X Acquired row lock on u2/u2x
145 COMMIT;
146 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
147 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
148 START TRANSACTION;
149 SELECT User FROM mysql.user
150 WHERE User='u2' LOCK IN SHARE MODE;
151 User
152 u2
153 Warnings:
154 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
155 # Make sure no read row lock is held on row 'u2'.
156 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
157 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
158 FROM performance_schema.data_locks
159 WHERE LOCK_TYPE='RECORD' AND
160 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
161 "Allow the same row to be read for update by SELECT FOR UPDATE"
162 connection update_con
163 SELECT User FROM mysql.user
164 WHERE User='u2' FOR UPDATE;
165 User
166 u2
167 # Make sure X record locks are held on row 'u2'
168 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
169 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
170 FROM performance_schema.data_locks
171 WHERE LOCK_TYPE='RECORD' AND
172 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
173 tab INDEX_NAME LOCK_MODE Has_lock
174 mysql.user PRIMARY X Acquired row lock on u2/u2x
175 COMMIT;
176 "Case 1.2: Start a transaction in REPEATABLE READ"
177 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
178 START TRANSACTION;
179 SELECT User FROM mysql.user WHERE User='u2';
180 User
181 u2
182 # Make sure no read row lock is held on row 'u2'.
183 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
184 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
185 FROM performance_schema.data_locks
186 WHERE LOCK_TYPE='RECORD' AND
187 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
188 "Allow the same row to be read for update by SELECT FOR UPDATE"
189 connection update_con
190 SELECT User FROM mysql.user
191 WHERE User='u2' FOR UPDATE;
192 User
193 u2
194 # Make sure X record locks are held on row 'u2'
195 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
196 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
197 FROM performance_schema.data_locks
198 WHERE LOCK_TYPE='RECORD' AND
199 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
200 tab INDEX_NAME LOCK_MODE Has_lock
201 mysql.user PRIMARY X Acquired row lock on u2/u2x
202 COMMIT;
203 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
204 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
205 START TRANSACTION;
206 SELECT User FROM mysql.user
207 WHERE User='u2' LOCK IN SHARE MODE;
208 User
209 u2
210 Warnings:
211 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
212 # Make sure no read row lock is held on row 'u2'.
213 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
214 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
215 FROM performance_schema.data_locks
216 WHERE LOCK_TYPE='RECORD' AND
217 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
218 "Allow the same row to be read for update by SELECT FOR UPDATE"
219 connection update_con
220 SELECT User FROM mysql.user
221 WHERE User='u2' FOR UPDATE;
222 User
223 u2
224 # Make sure X record locks are held on row 'u2'
225 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
226 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
227 FROM performance_schema.data_locks
228 WHERE LOCK_TYPE='RECORD' AND
229 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
230 tab INDEX_NAME LOCK_MODE Has_lock
231 mysql.user PRIMARY X Acquired row lock on u2/u2x
232 COMMIT;
233 "Case 1.3: Start a transaction in READ COMMITTED"
234 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
235 START TRANSACTION;
236 SELECT User FROM mysql.user WHERE User='u2';
237 User
238 u2
239 # Make sure no read row lock is held on row 'u2'.
240 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
241 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
242 FROM performance_schema.data_locks
243 WHERE LOCK_TYPE='RECORD' AND
244 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
245 "Allow the same row to be read for update by SELECT FOR UPDATE"
246 connection update_con
247 SELECT User FROM mysql.user
248 WHERE User='u2' FOR UPDATE;
249 User
250 u2
251 # Make sure X record locks are held on row 'u2'
252 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
253 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
254 FROM performance_schema.data_locks
255 WHERE LOCK_TYPE='RECORD' AND
256 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
257 tab INDEX_NAME LOCK_MODE Has_lock
258 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
259 COMMIT;
260 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
261 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
262 START TRANSACTION;
263 SELECT User FROM mysql.user
264 WHERE User='u2' LOCK IN SHARE MODE;
265 User
266 u2
267 Warnings:
268 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
269 # Make sure no read row lock is held on row 'u2'.
270 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
271 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
272 FROM performance_schema.data_locks
273 WHERE LOCK_TYPE='RECORD' AND
274 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
275 "Allow the same row to be read for update by SELECT FOR UPDATE"
276 connection update_con
277 SELECT User FROM mysql.user
278 WHERE User='u2' FOR UPDATE;
279 User
280 u2
281 # Make sure X record locks are held on row 'u2'
282 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
283 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
284 FROM performance_schema.data_locks
285 WHERE LOCK_TYPE='RECORD' AND
286 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
287 tab INDEX_NAME LOCK_MODE Has_lock
288 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
289 COMMIT;
290 "Case 1.4: Start a transaction in READ UNCOMMITTED"
291 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
292 START TRANSACTION;
293 SELECT User FROM mysql.user WHERE User='u2';
294 User
295 u2
296 # Make sure no read row lock is held on row 'u2'.
297 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
298 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
299 FROM performance_schema.data_locks
300 WHERE LOCK_TYPE='RECORD' AND
301 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
302 "Allow the same row to be read for update by SELECT FOR UPDATE"
303 connection update_con
304 SELECT User FROM mysql.user
305 WHERE User='u2' FOR UPDATE;
306 User
307 u2
308 # Make sure X record locks are held on row 'u2'
309 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
310 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
311 FROM performance_schema.data_locks
312 WHERE LOCK_TYPE='RECORD' AND
313 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
314 tab INDEX_NAME LOCK_MODE Has_lock
315 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
316 COMMIT;
317 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
318 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
319 START TRANSACTION;
320 SELECT User FROM mysql.user
321 WHERE User='u2' LOCK IN SHARE MODE;
322 User
323 u2
324 Warnings:
325 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
326 # Make sure no read row lock is held on row 'u2'.
327 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
328 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
329 FROM performance_schema.data_locks
330 WHERE LOCK_TYPE='RECORD' AND
331 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
332 "Allow the same row to be read for update by SELECT FOR UPDATE"
333 connection update_con
334 SELECT User FROM mysql.user
335 WHERE User='u2' FOR UPDATE;
336 User
337 u2
338 # Make sure X record locks are held on row 'u2'
339 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
340 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
341 FROM performance_schema.data_locks
342 WHERE LOCK_TYPE='RECORD' AND
343 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
344 tab INDEX_NAME LOCK_MODE Has_lock
345 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
346 COMMIT;
347 # Run the test for all isolation level for table mysql.db
348 "Case 1.1: Start a transaction in SERIALIZABLE"
349 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
350 START TRANSACTION;
351 SELECT User FROM mysql.db WHERE User='u2';
352 User
353 u2
354 Warnings:
355 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
356 # Make sure no read row lock is held on row 'u2'.
357 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
358 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
359 FROM performance_schema.data_locks
360 WHERE LOCK_TYPE='RECORD' AND
361 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
362 "Allow the same row to be read for update by SELECT FOR UPDATE"
363 connection update_con
364 SELECT User FROM mysql.db
365 WHERE User='u2' FOR UPDATE;
366 User
367 u2
368 # Make sure X record locks are held on row 'u2'
369 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
370 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
371 FROM performance_schema.data_locks
372 WHERE LOCK_TYPE='RECORD' AND
373 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
374 tab INDEX_NAME LOCK_MODE Has_lock
375 mysql.db User X Acquired row lock on u2/u2x
376 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
377 COMMIT;
378 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
379 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
380 START TRANSACTION;
381 SELECT User FROM mysql.db
382 WHERE User='u2' LOCK IN SHARE MODE;
383 User
384 u2
385 Warnings:
386 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
387 # Make sure no read row lock is held on row 'u2'.
388 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
389 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
390 FROM performance_schema.data_locks
391 WHERE LOCK_TYPE='RECORD' AND
392 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
393 "Allow the same row to be read for update by SELECT FOR UPDATE"
394 connection update_con
395 SELECT User FROM mysql.db
396 WHERE User='u2' FOR UPDATE;
397 User
398 u2
399 # Make sure X record locks are held on row 'u2'
400 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
401 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
402 FROM performance_schema.data_locks
403 WHERE LOCK_TYPE='RECORD' AND
404 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
405 tab INDEX_NAME LOCK_MODE Has_lock
406 mysql.db User X Acquired row lock on u2/u2x
407 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
408 COMMIT;
409 "Case 1.2: Start a transaction in REPEATABLE READ"
410 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
411 START TRANSACTION;
412 SELECT User FROM mysql.db WHERE User='u2';
413 User
414 u2
415 # Make sure no read row lock is held on row 'u2'.
416 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
417 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
418 FROM performance_schema.data_locks
419 WHERE LOCK_TYPE='RECORD' AND
420 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
421 "Allow the same row to be read for update by SELECT FOR UPDATE"
422 connection update_con
423 SELECT User FROM mysql.db
424 WHERE User='u2' FOR UPDATE;
425 User
426 u2
427 # Make sure X record locks are held on row 'u2'
428 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
429 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
430 FROM performance_schema.data_locks
431 WHERE LOCK_TYPE='RECORD' AND
432 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
433 tab INDEX_NAME LOCK_MODE Has_lock
434 mysql.db User X Acquired row lock on u2/u2x
435 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
436 COMMIT;
437 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
438 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
439 START TRANSACTION;
440 SELECT User FROM mysql.db
441 WHERE User='u2' LOCK IN SHARE MODE;
442 User
443 u2
444 Warnings:
445 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
446 # Make sure no read row lock is held on row 'u2'.
447 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
448 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
449 FROM performance_schema.data_locks
450 WHERE LOCK_TYPE='RECORD' AND
451 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
452 "Allow the same row to be read for update by SELECT FOR UPDATE"
453 connection update_con
454 SELECT User FROM mysql.db
455 WHERE User='u2' FOR UPDATE;
456 User
457 u2
458 # Make sure X record locks are held on row 'u2'
459 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
460 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
461 FROM performance_schema.data_locks
462 WHERE LOCK_TYPE='RECORD' AND
463 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
464 tab INDEX_NAME LOCK_MODE Has_lock
465 mysql.db User X Acquired row lock on u2/u2x
466 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
467 COMMIT;
468 "Case 1.3: Start a transaction in READ COMMITTED"
469 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
470 START TRANSACTION;
471 SELECT User FROM mysql.db WHERE User='u2';
472 User
473 u2
474 # Make sure no read row lock is held on row 'u2'.
475 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
476 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
477 FROM performance_schema.data_locks
478 WHERE LOCK_TYPE='RECORD' AND
479 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
480 "Allow the same row to be read for update by SELECT FOR UPDATE"
481 connection update_con
482 SELECT User FROM mysql.db
483 WHERE User='u2' FOR UPDATE;
484 User
485 u2
486 # Make sure X record locks are held on row 'u2'
487 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
488 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
489 FROM performance_schema.data_locks
490 WHERE LOCK_TYPE='RECORD' AND
491 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
492 tab INDEX_NAME LOCK_MODE Has_lock
493 mysql.db User X,REC_NOT_GAP Acquired row lock on u2/u2x
494 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
495 COMMIT;
496 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
497 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
498 START TRANSACTION;
499 SELECT User FROM mysql.db
500 WHERE User='u2' LOCK IN SHARE MODE;
501 User
502 u2
503 Warnings:
504 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
505 # Make sure no read row lock is held on row 'u2'.
506 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
507 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
508 FROM performance_schema.data_locks
509 WHERE LOCK_TYPE='RECORD' AND
510 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
511 "Allow the same row to be read for update by SELECT FOR UPDATE"
512 connection update_con
513 SELECT User FROM mysql.db
514 WHERE User='u2' FOR UPDATE;
515 User
516 u2
517 # Make sure X record locks are held on row 'u2'
518 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
519 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
520 FROM performance_schema.data_locks
521 WHERE LOCK_TYPE='RECORD' AND
522 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
523 tab INDEX_NAME LOCK_MODE Has_lock
524 mysql.db User X,REC_NOT_GAP Acquired row lock on u2/u2x
525 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
526 COMMIT;
527 "Case 1.4: Start a transaction in READ UNCOMMITTED"
528 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
529 START TRANSACTION;
530 SELECT User FROM mysql.db WHERE User='u2';
531 User
532 u2
533 # Make sure no read row lock is held on row 'u2'.
534 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
535 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
536 FROM performance_schema.data_locks
537 WHERE LOCK_TYPE='RECORD' AND
538 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
539 "Allow the same row to be read for update by SELECT FOR UPDATE"
540 connection update_con
541 SELECT User FROM mysql.db
542 WHERE User='u2' FOR UPDATE;
543 User
544 u2
545 # Make sure X record locks are held on row 'u2'
546 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
547 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
548 FROM performance_schema.data_locks
549 WHERE LOCK_TYPE='RECORD' AND
550 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
551 tab INDEX_NAME LOCK_MODE Has_lock
552 mysql.db User X,REC_NOT_GAP Acquired row lock on u2/u2x
553 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
554 COMMIT;
555 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
556 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
557 START TRANSACTION;
558 SELECT User FROM mysql.db
559 WHERE User='u2' LOCK IN SHARE MODE;
560 User
561 u2
562 Warnings:
563 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
564 # Make sure no read row lock is held on row 'u2'.
565 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
566 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
567 FROM performance_schema.data_locks
568 WHERE LOCK_TYPE='RECORD' AND
569 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
570 "Allow the same row to be read for update by SELECT FOR UPDATE"
571 connection update_con
572 SELECT User FROM mysql.db
573 WHERE User='u2' FOR UPDATE;
574 User
575 u2
576 # Make sure X record locks are held on row 'u2'
577 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
578 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
579 FROM performance_schema.data_locks
580 WHERE LOCK_TYPE='RECORD' AND
581 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
582 tab INDEX_NAME LOCK_MODE Has_lock
583 mysql.db User X,REC_NOT_GAP Acquired row lock on u2/u2x
584 mysql.db PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
585 COMMIT;
586 # Run the test for all isolation level for table mysql.tables_priv
587 "Case 1.1: Start a transaction in SERIALIZABLE"
588 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
589 START TRANSACTION;
590 SELECT User FROM mysql.tables_priv WHERE User='u2';
591 User
592 u2
593 Warnings:
594 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
595 # Make sure no read row lock is held on row 'u2'.
596 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
597 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
598 FROM performance_schema.data_locks
599 WHERE LOCK_TYPE='RECORD' AND
600 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
601 "Allow the same row to be read for update by SELECT FOR UPDATE"
602 connection update_con
603 SELECT User FROM mysql.tables_priv
604 WHERE User='u2' FOR UPDATE;
605 User
606 u2
607 # Make sure X record locks are held on row 'u2'
608 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
609 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
610 FROM performance_schema.data_locks
611 WHERE LOCK_TYPE='RECORD' AND
612 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
613 tab INDEX_NAME LOCK_MODE Has_lock
614 mysql.tables_priv Grantor X Acquired row lock on u2/u2x
615 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
616 COMMIT;
617 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
618 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
619 START TRANSACTION;
620 SELECT User FROM mysql.tables_priv
621 WHERE User='u2' LOCK IN SHARE MODE;
622 User
623 u2
624 Warnings:
625 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
626 # Make sure no read row lock is held on row 'u2'.
627 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
628 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
629 FROM performance_schema.data_locks
630 WHERE LOCK_TYPE='RECORD' AND
631 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
632 "Allow the same row to be read for update by SELECT FOR UPDATE"
633 connection update_con
634 SELECT User FROM mysql.tables_priv
635 WHERE User='u2' FOR UPDATE;
636 User
637 u2
638 # Make sure X record locks are held on row 'u2'
639 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
640 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
641 FROM performance_schema.data_locks
642 WHERE LOCK_TYPE='RECORD' AND
643 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
644 tab INDEX_NAME LOCK_MODE Has_lock
645 mysql.tables_priv Grantor X Acquired row lock on u2/u2x
646 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
647 COMMIT;
648 "Case 1.2: Start a transaction in REPEATABLE READ"
649 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
650 START TRANSACTION;
651 SELECT User FROM mysql.tables_priv WHERE User='u2';
652 User
653 u2
654 # Make sure no read row lock is held on row 'u2'.
655 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
656 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
657 FROM performance_schema.data_locks
658 WHERE LOCK_TYPE='RECORD' AND
659 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
660 "Allow the same row to be read for update by SELECT FOR UPDATE"
661 connection update_con
662 SELECT User FROM mysql.tables_priv
663 WHERE User='u2' FOR UPDATE;
664 User
665 u2
666 # Make sure X record locks are held on row 'u2'
667 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
668 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
669 FROM performance_schema.data_locks
670 WHERE LOCK_TYPE='RECORD' AND
671 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
672 tab INDEX_NAME LOCK_MODE Has_lock
673 mysql.tables_priv Grantor X Acquired row lock on u2/u2x
674 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
675 COMMIT;
676 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
677 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
678 START TRANSACTION;
679 SELECT User FROM mysql.tables_priv
680 WHERE User='u2' LOCK IN SHARE MODE;
681 User
682 u2
683 Warnings:
684 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
685 # Make sure no read row lock is held on row 'u2'.
686 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
687 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
688 FROM performance_schema.data_locks
689 WHERE LOCK_TYPE='RECORD' AND
690 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
691 "Allow the same row to be read for update by SELECT FOR UPDATE"
692 connection update_con
693 SELECT User FROM mysql.tables_priv
694 WHERE User='u2' FOR UPDATE;
695 User
696 u2
697 # Make sure X record locks are held on row 'u2'
698 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
699 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
700 FROM performance_schema.data_locks
701 WHERE LOCK_TYPE='RECORD' AND
702 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
703 tab INDEX_NAME LOCK_MODE Has_lock
704 mysql.tables_priv Grantor X Acquired row lock on u2/u2x
705 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
706 COMMIT;
707 "Case 1.3: Start a transaction in READ COMMITTED"
708 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
709 START TRANSACTION;
710 SELECT User FROM mysql.tables_priv WHERE User='u2';
711 User
712 u2
713 # Make sure no read row lock is held on row 'u2'.
714 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
715 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
716 FROM performance_schema.data_locks
717 WHERE LOCK_TYPE='RECORD' AND
718 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
719 "Allow the same row to be read for update by SELECT FOR UPDATE"
720 connection update_con
721 SELECT User FROM mysql.tables_priv
722 WHERE User='u2' FOR UPDATE;
723 User
724 u2
725 # Make sure X record locks are held on row 'u2'
726 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
727 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
728 FROM performance_schema.data_locks
729 WHERE LOCK_TYPE='RECORD' AND
730 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
731 tab INDEX_NAME LOCK_MODE Has_lock
732 mysql.tables_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
733 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
734 COMMIT;
735 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
736 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
737 START TRANSACTION;
738 SELECT User FROM mysql.tables_priv
739 WHERE User='u2' LOCK IN SHARE MODE;
740 User
741 u2
742 Warnings:
743 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
744 # Make sure no read row lock is held on row 'u2'.
745 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
746 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
747 FROM performance_schema.data_locks
748 WHERE LOCK_TYPE='RECORD' AND
749 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
750 "Allow the same row to be read for update by SELECT FOR UPDATE"
751 connection update_con
752 SELECT User FROM mysql.tables_priv
753 WHERE User='u2' FOR UPDATE;
754 User
755 u2
756 # Make sure X record locks are held on row 'u2'
757 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
758 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
759 FROM performance_schema.data_locks
760 WHERE LOCK_TYPE='RECORD' AND
761 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
762 tab INDEX_NAME LOCK_MODE Has_lock
763 mysql.tables_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
764 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
765 COMMIT;
766 "Case 1.4: Start a transaction in READ UNCOMMITTED"
767 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
768 START TRANSACTION;
769 SELECT User FROM mysql.tables_priv WHERE User='u2';
770 User
771 u2
772 # Make sure no read row lock is held on row 'u2'.
773 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
774 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
775 FROM performance_schema.data_locks
776 WHERE LOCK_TYPE='RECORD' AND
777 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
778 "Allow the same row to be read for update by SELECT FOR UPDATE"
779 connection update_con
780 SELECT User FROM mysql.tables_priv
781 WHERE User='u2' FOR UPDATE;
782 User
783 u2
784 # Make sure X record locks are held on row 'u2'
785 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
786 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
787 FROM performance_schema.data_locks
788 WHERE LOCK_TYPE='RECORD' AND
789 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
790 tab INDEX_NAME LOCK_MODE Has_lock
791 mysql.tables_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
792 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
793 COMMIT;
794 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
795 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
796 START TRANSACTION;
797 SELECT User FROM mysql.tables_priv
798 WHERE User='u2' LOCK IN SHARE MODE;
799 User
800 u2
801 Warnings:
802 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
803 # Make sure no read row lock is held on row 'u2'.
804 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
805 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
806 FROM performance_schema.data_locks
807 WHERE LOCK_TYPE='RECORD' AND
808 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
809 "Allow the same row to be read for update by SELECT FOR UPDATE"
810 connection update_con
811 SELECT User FROM mysql.tables_priv
812 WHERE User='u2' FOR UPDATE;
813 User
814 u2
815 # Make sure X record locks are held on row 'u2'
816 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
817 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
818 FROM performance_schema.data_locks
819 WHERE LOCK_TYPE='RECORD' AND
820 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
821 tab INDEX_NAME LOCK_MODE Has_lock
822 mysql.tables_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
823 mysql.tables_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
824 COMMIT;
825 # Run the test for all isolation level for table mysql.columns_priv
826 "Case 1.1: Start a transaction in SERIALIZABLE"
827 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
828 START TRANSACTION;
829 SELECT User FROM mysql.columns_priv WHERE User='u2';
830 User
831 u2
832 Warnings:
833 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
834 # Make sure no read row lock is held on row 'u2'.
835 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
836 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
837 FROM performance_schema.data_locks
838 WHERE LOCK_TYPE='RECORD' AND
839 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
840 "Allow the same row to be read for update by SELECT FOR UPDATE"
841 connection update_con
842 SELECT User FROM mysql.columns_priv
843 WHERE User='u2' FOR UPDATE;
844 User
845 u2
846 # Make sure X record locks are held on row 'u2'
847 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
848 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
849 FROM performance_schema.data_locks
850 WHERE LOCK_TYPE='RECORD' AND
851 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
852 tab INDEX_NAME LOCK_MODE Has_lock
853 mysql.columns_priv PRIMARY X Acquired row lock on u2/u2x
854 COMMIT;
855 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
856 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
857 START TRANSACTION;
858 SELECT User FROM mysql.columns_priv
859 WHERE User='u2' LOCK IN SHARE MODE;
860 User
861 u2
862 Warnings:
863 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
864 # Make sure no read row lock is held on row 'u2'.
865 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
866 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
867 FROM performance_schema.data_locks
868 WHERE LOCK_TYPE='RECORD' AND
869 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
870 "Allow the same row to be read for update by SELECT FOR UPDATE"
871 connection update_con
872 SELECT User FROM mysql.columns_priv
873 WHERE User='u2' FOR UPDATE;
874 User
875 u2
876 # Make sure X record locks are held on row 'u2'
877 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
878 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
879 FROM performance_schema.data_locks
880 WHERE LOCK_TYPE='RECORD' AND
881 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
882 tab INDEX_NAME LOCK_MODE Has_lock
883 mysql.columns_priv PRIMARY X Acquired row lock on u2/u2x
884 COMMIT;
885 "Case 1.2: Start a transaction in REPEATABLE READ"
886 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
887 START TRANSACTION;
888 SELECT User FROM mysql.columns_priv WHERE User='u2';
889 User
890 u2
891 # Make sure no read row lock is held on row 'u2'.
892 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
893 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
894 FROM performance_schema.data_locks
895 WHERE LOCK_TYPE='RECORD' AND
896 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
897 "Allow the same row to be read for update by SELECT FOR UPDATE"
898 connection update_con
899 SELECT User FROM mysql.columns_priv
900 WHERE User='u2' FOR UPDATE;
901 User
902 u2
903 # Make sure X record locks are held on row 'u2'
904 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
905 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
906 FROM performance_schema.data_locks
907 WHERE LOCK_TYPE='RECORD' AND
908 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
909 tab INDEX_NAME LOCK_MODE Has_lock
910 mysql.columns_priv PRIMARY X Acquired row lock on u2/u2x
911 COMMIT;
912 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
913 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
914 START TRANSACTION;
915 SELECT User FROM mysql.columns_priv
916 WHERE User='u2' LOCK IN SHARE MODE;
917 User
918 u2
919 Warnings:
920 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
921 # Make sure no read row lock is held on row 'u2'.
922 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
923 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
924 FROM performance_schema.data_locks
925 WHERE LOCK_TYPE='RECORD' AND
926 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
927 "Allow the same row to be read for update by SELECT FOR UPDATE"
928 connection update_con
929 SELECT User FROM mysql.columns_priv
930 WHERE User='u2' FOR UPDATE;
931 User
932 u2
933 # Make sure X record locks are held on row 'u2'
934 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
935 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
936 FROM performance_schema.data_locks
937 WHERE LOCK_TYPE='RECORD' AND
938 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
939 tab INDEX_NAME LOCK_MODE Has_lock
940 mysql.columns_priv PRIMARY X Acquired row lock on u2/u2x
941 COMMIT;
942 "Case 1.3: Start a transaction in READ COMMITTED"
943 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
944 START TRANSACTION;
945 SELECT User FROM mysql.columns_priv WHERE User='u2';
946 User
947 u2
948 # Make sure no read row lock is held on row 'u2'.
949 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
950 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
951 FROM performance_schema.data_locks
952 WHERE LOCK_TYPE='RECORD' AND
953 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
954 "Allow the same row to be read for update by SELECT FOR UPDATE"
955 connection update_con
956 SELECT User FROM mysql.columns_priv
957 WHERE User='u2' FOR UPDATE;
958 User
959 u2
960 # Make sure X record locks are held on row 'u2'
961 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
962 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
963 FROM performance_schema.data_locks
964 WHERE LOCK_TYPE='RECORD' AND
965 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
966 tab INDEX_NAME LOCK_MODE Has_lock
967 mysql.columns_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
968 COMMIT;
969 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
970 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
971 START TRANSACTION;
972 SELECT User FROM mysql.columns_priv
973 WHERE User='u2' LOCK IN SHARE MODE;
974 User
975 u2
976 Warnings:
977 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
978 # Make sure no read row lock is held on row 'u2'.
979 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
980 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
981 FROM performance_schema.data_locks
982 WHERE LOCK_TYPE='RECORD' AND
983 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
984 "Allow the same row to be read for update by SELECT FOR UPDATE"
985 connection update_con
986 SELECT User FROM mysql.columns_priv
987 WHERE User='u2' FOR UPDATE;
988 User
989 u2
990 # Make sure X record locks are held on row 'u2'
991 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
992 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
993 FROM performance_schema.data_locks
994 WHERE LOCK_TYPE='RECORD' AND
995 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
996 tab INDEX_NAME LOCK_MODE Has_lock
997 mysql.columns_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
998 COMMIT;
999 "Case 1.4: Start a transaction in READ UNCOMMITTED"
1000 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1001 START TRANSACTION;
1002 SELECT User FROM mysql.columns_priv WHERE User='u2';
1003 User
1004 u2
1005 # Make sure no read row lock is held on row 'u2'.
1006 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1007 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1008 FROM performance_schema.data_locks
1009 WHERE LOCK_TYPE='RECORD' AND
1010 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1011 "Allow the same row to be read for update by SELECT FOR UPDATE"
1012 connection update_con
1013 SELECT User FROM mysql.columns_priv
1014 WHERE User='u2' FOR UPDATE;
1015 User
1016 u2
1017 # Make sure X record locks are held on row 'u2'
1018 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1019 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1020 FROM performance_schema.data_locks
1021 WHERE LOCK_TYPE='RECORD' AND
1022 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1023 tab INDEX_NAME LOCK_MODE Has_lock
1024 mysql.columns_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1025 COMMIT;
1026 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
1027 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1028 START TRANSACTION;
1029 SELECT User FROM mysql.columns_priv
1030 WHERE User='u2' LOCK IN SHARE MODE;
1031 User
1032 u2
1033 Warnings:
1034 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1035 # Make sure no read row lock is held on row 'u2'.
1036 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1037 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1038 FROM performance_schema.data_locks
1039 WHERE LOCK_TYPE='RECORD' AND
1040 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1041 "Allow the same row to be read for update by SELECT FOR UPDATE"
1042 connection update_con
1043 SELECT User FROM mysql.columns_priv
1044 WHERE User='u2' FOR UPDATE;
1045 User
1046 u2
1047 # Make sure X record locks are held on row 'u2'
1048 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1049 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1050 FROM performance_schema.data_locks
1051 WHERE LOCK_TYPE='RECORD' AND
1052 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1053 tab INDEX_NAME LOCK_MODE Has_lock
1054 mysql.columns_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1055 COMMIT;
1056 # Run the test for all isolation level for table mysql.procs_priv
1057 "Case 1.1: Start a transaction in SERIALIZABLE"
1058 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1059 START TRANSACTION;
1060 SELECT User FROM mysql.procs_priv WHERE User='u2';
1061 User
1062 u2
1063 Warnings:
1064 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1065 # Make sure no read row lock is held on row 'u2'.
1066 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1067 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1068 FROM performance_schema.data_locks
1069 WHERE LOCK_TYPE='RECORD' AND
1070 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1071 "Allow the same row to be read for update by SELECT FOR UPDATE"
1072 connection update_con
1073 SELECT User FROM mysql.procs_priv
1074 WHERE User='u2' FOR UPDATE;
1075 User
1076 u2
1077 # Make sure X record locks are held on row 'u2'
1078 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1079 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1080 FROM performance_schema.data_locks
1081 WHERE LOCK_TYPE='RECORD' AND
1082 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1083 tab INDEX_NAME LOCK_MODE Has_lock
1084 mysql.procs_priv Grantor X Acquired row lock on u2/u2x
1085 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1086 COMMIT;
1087 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
1088 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1089 START TRANSACTION;
1090 SELECT User FROM mysql.procs_priv
1091 WHERE User='u2' LOCK IN SHARE MODE;
1092 User
1093 u2
1094 Warnings:
1095 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1096 # Make sure no read row lock is held on row 'u2'.
1097 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1098 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1099 FROM performance_schema.data_locks
1100 WHERE LOCK_TYPE='RECORD' AND
1101 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1102 "Allow the same row to be read for update by SELECT FOR UPDATE"
1103 connection update_con
1104 SELECT User FROM mysql.procs_priv
1105 WHERE User='u2' FOR UPDATE;
1106 User
1107 u2
1108 # Make sure X record locks are held on row 'u2'
1109 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1110 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1111 FROM performance_schema.data_locks
1112 WHERE LOCK_TYPE='RECORD' AND
1113 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1114 tab INDEX_NAME LOCK_MODE Has_lock
1115 mysql.procs_priv Grantor X Acquired row lock on u2/u2x
1116 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1117 COMMIT;
1118 "Case 1.2: Start a transaction in REPEATABLE READ"
1119 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1120 START TRANSACTION;
1121 SELECT User FROM mysql.procs_priv WHERE User='u2';
1122 User
1123 u2
1124 # Make sure no read row lock is held on row 'u2'.
1125 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1126 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1127 FROM performance_schema.data_locks
1128 WHERE LOCK_TYPE='RECORD' AND
1129 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1130 "Allow the same row to be read for update by SELECT FOR UPDATE"
1131 connection update_con
1132 SELECT User FROM mysql.procs_priv
1133 WHERE User='u2' FOR UPDATE;
1134 User
1135 u2
1136 # Make sure X record locks are held on row 'u2'
1137 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1138 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1139 FROM performance_schema.data_locks
1140 WHERE LOCK_TYPE='RECORD' AND
1141 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1142 tab INDEX_NAME LOCK_MODE Has_lock
1143 mysql.procs_priv Grantor X Acquired row lock on u2/u2x
1144 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1145 COMMIT;
1146 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
1147 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1148 START TRANSACTION;
1149 SELECT User FROM mysql.procs_priv
1150 WHERE User='u2' LOCK IN SHARE MODE;
1151 User
1152 u2
1153 Warnings:
1154 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1155 # Make sure no read row lock is held on row 'u2'.
1156 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1157 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1158 FROM performance_schema.data_locks
1159 WHERE LOCK_TYPE='RECORD' AND
1160 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1161 "Allow the same row to be read for update by SELECT FOR UPDATE"
1162 connection update_con
1163 SELECT User FROM mysql.procs_priv
1164 WHERE User='u2' FOR UPDATE;
1165 User
1166 u2
1167 # Make sure X record locks are held on row 'u2'
1168 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1169 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1170 FROM performance_schema.data_locks
1171 WHERE LOCK_TYPE='RECORD' AND
1172 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1173 tab INDEX_NAME LOCK_MODE Has_lock
1174 mysql.procs_priv Grantor X Acquired row lock on u2/u2x
1175 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1176 COMMIT;
1177 "Case 1.3: Start a transaction in READ COMMITTED"
1178 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1179 START TRANSACTION;
1180 SELECT User FROM mysql.procs_priv WHERE User='u2';
1181 User
1182 u2
1183 # Make sure no read row lock is held on row 'u2'.
1184 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1185 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1186 FROM performance_schema.data_locks
1187 WHERE LOCK_TYPE='RECORD' AND
1188 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1189 "Allow the same row to be read for update by SELECT FOR UPDATE"
1190 connection update_con
1191 SELECT User FROM mysql.procs_priv
1192 WHERE User='u2' FOR UPDATE;
1193 User
1194 u2
1195 # Make sure X record locks are held on row 'u2'
1196 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1197 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1198 FROM performance_schema.data_locks
1199 WHERE LOCK_TYPE='RECORD' AND
1200 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1201 tab INDEX_NAME LOCK_MODE Has_lock
1202 mysql.procs_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1203 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1204 COMMIT;
1205 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
1206 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1207 START TRANSACTION;
1208 SELECT User FROM mysql.procs_priv
1209 WHERE User='u2' LOCK IN SHARE MODE;
1210 User
1211 u2
1212 Warnings:
1213 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1214 # Make sure no read row lock is held on row 'u2'.
1215 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1216 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1217 FROM performance_schema.data_locks
1218 WHERE LOCK_TYPE='RECORD' AND
1219 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1220 "Allow the same row to be read for update by SELECT FOR UPDATE"
1221 connection update_con
1222 SELECT User FROM mysql.procs_priv
1223 WHERE User='u2' FOR UPDATE;
1224 User
1225 u2
1226 # Make sure X record locks are held on row 'u2'
1227 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1228 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1229 FROM performance_schema.data_locks
1230 WHERE LOCK_TYPE='RECORD' AND
1231 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1232 tab INDEX_NAME LOCK_MODE Has_lock
1233 mysql.procs_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1234 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1235 COMMIT;
1236 "Case 1.4: Start a transaction in READ UNCOMMITTED"
1237 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1238 START TRANSACTION;
1239 SELECT User FROM mysql.procs_priv WHERE User='u2';
1240 User
1241 u2
1242 # Make sure no read row lock is held on row 'u2'.
1243 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1244 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1245 FROM performance_schema.data_locks
1246 WHERE LOCK_TYPE='RECORD' AND
1247 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1248 "Allow the same row to be read for update by SELECT FOR UPDATE"
1249 connection update_con
1250 SELECT User FROM mysql.procs_priv
1251 WHERE User='u2' FOR UPDATE;
1252 User
1253 u2
1254 # Make sure X record locks are held on row 'u2'
1255 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1256 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1257 FROM performance_schema.data_locks
1258 WHERE LOCK_TYPE='RECORD' AND
1259 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1260 tab INDEX_NAME LOCK_MODE Has_lock
1261 mysql.procs_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1262 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1263 COMMIT;
1264 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
1265 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1266 START TRANSACTION;
1267 SELECT User FROM mysql.procs_priv
1268 WHERE User='u2' LOCK IN SHARE MODE;
1269 User
1270 u2
1271 Warnings:
1272 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1273 # Make sure no read row lock is held on row 'u2'.
1274 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1275 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1276 FROM performance_schema.data_locks
1277 WHERE LOCK_TYPE='RECORD' AND
1278 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1279 "Allow the same row to be read for update by SELECT FOR UPDATE"
1280 connection update_con
1281 SELECT User FROM mysql.procs_priv
1282 WHERE User='u2' FOR UPDATE;
1283 User
1284 u2
1285 # Make sure X record locks are held on row 'u2'
1286 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1287 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1288 FROM performance_schema.data_locks
1289 WHERE LOCK_TYPE='RECORD' AND
1290 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1291 tab INDEX_NAME LOCK_MODE Has_lock
1292 mysql.procs_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1293 mysql.procs_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1294 COMMIT;
1295 # Run the test for all isolation level for table mysql.proxies_priv
1296 "Case 1.1: Start a transaction in SERIALIZABLE"
1297 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1298 START TRANSACTION;
1299 SELECT User FROM mysql.proxies_priv WHERE User='u2';
1300 User
1301 u2
1302 Warnings:
1303 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1304 # Make sure no read row lock is held on row 'u2'.
1305 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1306 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1307 FROM performance_schema.data_locks
1308 WHERE LOCK_TYPE='RECORD' AND
1309 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1310 "Allow the same row to be read for update by SELECT FOR UPDATE"
1311 connection update_con
1312 SELECT User FROM mysql.proxies_priv
1313 WHERE User='u2' FOR UPDATE;
1314 User
1315 u2
1316 # Make sure X record locks are held on row 'u2'
1317 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1318 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1319 FROM performance_schema.data_locks
1320 WHERE LOCK_TYPE='RECORD' AND
1321 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1322 tab INDEX_NAME LOCK_MODE Has_lock
1323 mysql.proxies_priv Grantor X Acquired row lock on u2/u2x
1324 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1325 COMMIT;
1326 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
1327 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1328 START TRANSACTION;
1329 SELECT User FROM mysql.proxies_priv
1330 WHERE User='u2' LOCK IN SHARE MODE;
1331 User
1332 u2
1333 Warnings:
1334 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1335 # Make sure no read row lock is held on row 'u2'.
1336 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1337 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1338 FROM performance_schema.data_locks
1339 WHERE LOCK_TYPE='RECORD' AND
1340 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1341 "Allow the same row to be read for update by SELECT FOR UPDATE"
1342 connection update_con
1343 SELECT User FROM mysql.proxies_priv
1344 WHERE User='u2' FOR UPDATE;
1345 User
1346 u2
1347 # Make sure X record locks are held on row 'u2'
1348 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1349 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1350 FROM performance_schema.data_locks
1351 WHERE LOCK_TYPE='RECORD' AND
1352 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1353 tab INDEX_NAME LOCK_MODE Has_lock
1354 mysql.proxies_priv Grantor X Acquired row lock on u2/u2x
1355 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1356 COMMIT;
1357 "Case 1.2: Start a transaction in REPEATABLE READ"
1358 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1359 START TRANSACTION;
1360 SELECT User FROM mysql.proxies_priv WHERE User='u2';
1361 User
1362 u2
1363 # Make sure no read row lock is held on row 'u2'.
1364 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1365 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1366 FROM performance_schema.data_locks
1367 WHERE LOCK_TYPE='RECORD' AND
1368 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1369 "Allow the same row to be read for update by SELECT FOR UPDATE"
1370 connection update_con
1371 SELECT User FROM mysql.proxies_priv
1372 WHERE User='u2' FOR UPDATE;
1373 User
1374 u2
1375 # Make sure X record locks are held on row 'u2'
1376 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1377 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1378 FROM performance_schema.data_locks
1379 WHERE LOCK_TYPE='RECORD' AND
1380 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1381 tab INDEX_NAME LOCK_MODE Has_lock
1382 mysql.proxies_priv Grantor X Acquired row lock on u2/u2x
1383 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1384 COMMIT;
1385 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
1386 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1387 START TRANSACTION;
1388 SELECT User FROM mysql.proxies_priv
1389 WHERE User='u2' LOCK IN SHARE MODE;
1390 User
1391 u2
1392 Warnings:
1393 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1394 # Make sure no read row lock is held on row 'u2'.
1395 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1396 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1397 FROM performance_schema.data_locks
1398 WHERE LOCK_TYPE='RECORD' AND
1399 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1400 "Allow the same row to be read for update by SELECT FOR UPDATE"
1401 connection update_con
1402 SELECT User FROM mysql.proxies_priv
1403 WHERE User='u2' FOR UPDATE;
1404 User
1405 u2
1406 # Make sure X record locks are held on row 'u2'
1407 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1408 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1409 FROM performance_schema.data_locks
1410 WHERE LOCK_TYPE='RECORD' AND
1411 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1412 tab INDEX_NAME LOCK_MODE Has_lock
1413 mysql.proxies_priv Grantor X Acquired row lock on u2/u2x
1414 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1415 COMMIT;
1416 "Case 1.3: Start a transaction in READ COMMITTED"
1417 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1418 START TRANSACTION;
1419 SELECT User FROM mysql.proxies_priv WHERE User='u2';
1420 User
1421 u2
1422 # Make sure no read row lock is held on row 'u2'.
1423 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1424 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1425 FROM performance_schema.data_locks
1426 WHERE LOCK_TYPE='RECORD' AND
1427 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1428 "Allow the same row to be read for update by SELECT FOR UPDATE"
1429 connection update_con
1430 SELECT User FROM mysql.proxies_priv
1431 WHERE User='u2' FOR UPDATE;
1432 User
1433 u2
1434 # Make sure X record locks are held on row 'u2'
1435 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1436 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1437 FROM performance_schema.data_locks
1438 WHERE LOCK_TYPE='RECORD' AND
1439 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1440 tab INDEX_NAME LOCK_MODE Has_lock
1441 mysql.proxies_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1442 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1443 COMMIT;
1444 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
1445 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1446 START TRANSACTION;
1447 SELECT User FROM mysql.proxies_priv
1448 WHERE User='u2' LOCK IN SHARE MODE;
1449 User
1450 u2
1451 Warnings:
1452 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1453 # Make sure no read row lock is held on row 'u2'.
1454 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1455 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1456 FROM performance_schema.data_locks
1457 WHERE LOCK_TYPE='RECORD' AND
1458 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1459 "Allow the same row to be read for update by SELECT FOR UPDATE"
1460 connection update_con
1461 SELECT User FROM mysql.proxies_priv
1462 WHERE User='u2' FOR UPDATE;
1463 User
1464 u2
1465 # Make sure X record locks are held on row 'u2'
1466 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1467 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1468 FROM performance_schema.data_locks
1469 WHERE LOCK_TYPE='RECORD' AND
1470 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1471 tab INDEX_NAME LOCK_MODE Has_lock
1472 mysql.proxies_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1473 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1474 COMMIT;
1475 "Case 1.4: Start a transaction in READ UNCOMMITTED"
1476 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1477 START TRANSACTION;
1478 SELECT User FROM mysql.proxies_priv WHERE User='u2';
1479 User
1480 u2
1481 # Make sure no read row lock is held on row 'u2'.
1482 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1483 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1484 FROM performance_schema.data_locks
1485 WHERE LOCK_TYPE='RECORD' AND
1486 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1487 "Allow the same row to be read for update by SELECT FOR UPDATE"
1488 connection update_con
1489 SELECT User FROM mysql.proxies_priv
1490 WHERE User='u2' FOR UPDATE;
1491 User
1492 u2
1493 # Make sure X record locks are held on row 'u2'
1494 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1495 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1496 FROM performance_schema.data_locks
1497 WHERE LOCK_TYPE='RECORD' AND
1498 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1499 tab INDEX_NAME LOCK_MODE Has_lock
1500 mysql.proxies_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1501 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1502 COMMIT;
1503 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
1504 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1505 START TRANSACTION;
1506 SELECT User FROM mysql.proxies_priv
1507 WHERE User='u2' LOCK IN SHARE MODE;
1508 User
1509 u2
1510 Warnings:
1511 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1512 # Make sure no read row lock is held on row 'u2'.
1513 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1514 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1515 FROM performance_schema.data_locks
1516 WHERE LOCK_TYPE='RECORD' AND
1517 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1518 "Allow the same row to be read for update by SELECT FOR UPDATE"
1519 connection update_con
1520 SELECT User FROM mysql.proxies_priv
1521 WHERE User='u2' FOR UPDATE;
1522 User
1523 u2
1524 # Make sure X record locks are held on row 'u2'
1525 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1526 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1527 FROM performance_schema.data_locks
1528 WHERE LOCK_TYPE='RECORD' AND
1529 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1530 tab INDEX_NAME LOCK_MODE Has_lock
1531 mysql.proxies_priv Grantor X,REC_NOT_GAP Acquired row lock on u2/u2x
1532 mysql.proxies_priv PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1533 COMMIT;
1534 # Run the test for all isolation level for table mysql.global_grants
1535 "Case 1.1: Start a transaction in SERIALIZABLE"
1536 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1537 START TRANSACTION;
1538 SELECT User FROM mysql.global_grants WHERE User='u2';
1539 User
1540 u2
1541 Warnings:
1542 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1543 # Make sure no read row lock is held on row 'u2'.
1544 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1545 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1546 FROM performance_schema.data_locks
1547 WHERE LOCK_TYPE='RECORD' AND
1548 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1549 "Allow the same row to be read for update by SELECT FOR UPDATE"
1550 connection update_con
1551 SELECT User FROM mysql.global_grants
1552 WHERE User='u2' FOR UPDATE;
1553 User
1554 u2
1555 # Make sure X record locks are held on row 'u2'
1556 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1557 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1558 FROM performance_schema.data_locks
1559 WHERE LOCK_TYPE='RECORD' AND
1560 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1561 tab INDEX_NAME LOCK_MODE Has_lock
1562 mysql.global_grants PRIMARY X Acquired row lock on u2/u2x
1563 COMMIT;
1564 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
1565 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1566 START TRANSACTION;
1567 SELECT User FROM mysql.global_grants
1568 WHERE User='u2' LOCK IN SHARE MODE;
1569 User
1570 u2
1571 Warnings:
1572 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1573 # Make sure no read row lock is held on row 'u2'.
1574 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1575 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1576 FROM performance_schema.data_locks
1577 WHERE LOCK_TYPE='RECORD' AND
1578 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1579 "Allow the same row to be read for update by SELECT FOR UPDATE"
1580 connection update_con
1581 SELECT User FROM mysql.global_grants
1582 WHERE User='u2' FOR UPDATE;
1583 User
1584 u2
1585 # Make sure X record locks are held on row 'u2'
1586 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1587 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1588 FROM performance_schema.data_locks
1589 WHERE LOCK_TYPE='RECORD' AND
1590 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1591 tab INDEX_NAME LOCK_MODE Has_lock
1592 mysql.global_grants PRIMARY X Acquired row lock on u2/u2x
1593 COMMIT;
1594 "Case 1.2: Start a transaction in REPEATABLE READ"
1595 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1596 START TRANSACTION;
1597 SELECT User FROM mysql.global_grants WHERE User='u2';
1598 User
1599 u2
1600 # Make sure no read row lock is held on row 'u2'.
1601 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1602 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1603 FROM performance_schema.data_locks
1604 WHERE LOCK_TYPE='RECORD' AND
1605 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1606 "Allow the same row to be read for update by SELECT FOR UPDATE"
1607 connection update_con
1608 SELECT User FROM mysql.global_grants
1609 WHERE User='u2' FOR UPDATE;
1610 User
1611 u2
1612 # Make sure X record locks are held on row 'u2'
1613 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1614 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1615 FROM performance_schema.data_locks
1616 WHERE LOCK_TYPE='RECORD' AND
1617 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1618 tab INDEX_NAME LOCK_MODE Has_lock
1619 mysql.global_grants PRIMARY X Acquired row lock on u2/u2x
1620 COMMIT;
1621 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
1622 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1623 START TRANSACTION;
1624 SELECT User FROM mysql.global_grants
1625 WHERE User='u2' LOCK IN SHARE MODE;
1626 User
1627 u2
1628 Warnings:
1629 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1630 # Make sure no read row lock is held on row 'u2'.
1631 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1632 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1633 FROM performance_schema.data_locks
1634 WHERE LOCK_TYPE='RECORD' AND
1635 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1636 "Allow the same row to be read for update by SELECT FOR UPDATE"
1637 connection update_con
1638 SELECT User FROM mysql.global_grants
1639 WHERE User='u2' FOR UPDATE;
1640 User
1641 u2
1642 # Make sure X record locks are held on row 'u2'
1643 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1644 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1645 FROM performance_schema.data_locks
1646 WHERE LOCK_TYPE='RECORD' AND
1647 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1648 tab INDEX_NAME LOCK_MODE Has_lock
1649 mysql.global_grants PRIMARY X Acquired row lock on u2/u2x
1650 COMMIT;
1651 "Case 1.3: Start a transaction in READ COMMITTED"
1652 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1653 START TRANSACTION;
1654 SELECT User FROM mysql.global_grants WHERE User='u2';
1655 User
1656 u2
1657 # Make sure no read row lock is held on row 'u2'.
1658 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1659 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1660 FROM performance_schema.data_locks
1661 WHERE LOCK_TYPE='RECORD' AND
1662 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1663 "Allow the same row to be read for update by SELECT FOR UPDATE"
1664 connection update_con
1665 SELECT User FROM mysql.global_grants
1666 WHERE User='u2' FOR UPDATE;
1667 User
1668 u2
1669 # Make sure X record locks are held on row 'u2'
1670 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1671 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1672 FROM performance_schema.data_locks
1673 WHERE LOCK_TYPE='RECORD' AND
1674 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1675 tab INDEX_NAME LOCK_MODE Has_lock
1676 mysql.global_grants PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1677 COMMIT;
1678 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
1679 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1680 START TRANSACTION;
1681 SELECT User FROM mysql.global_grants
1682 WHERE User='u2' LOCK IN SHARE MODE;
1683 User
1684 u2
1685 Warnings:
1686 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1687 # Make sure no read row lock is held on row 'u2'.
1688 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1689 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1690 FROM performance_schema.data_locks
1691 WHERE LOCK_TYPE='RECORD' AND
1692 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1693 "Allow the same row to be read for update by SELECT FOR UPDATE"
1694 connection update_con
1695 SELECT User FROM mysql.global_grants
1696 WHERE User='u2' FOR UPDATE;
1697 User
1698 u2
1699 # Make sure X record locks are held on row 'u2'
1700 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1701 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1702 FROM performance_schema.data_locks
1703 WHERE LOCK_TYPE='RECORD' AND
1704 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1705 tab INDEX_NAME LOCK_MODE Has_lock
1706 mysql.global_grants PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1707 COMMIT;
1708 "Case 1.4: Start a transaction in READ UNCOMMITTED"
1709 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1710 START TRANSACTION;
1711 SELECT User FROM mysql.global_grants WHERE User='u2';
1712 User
1713 u2
1714 # Make sure no read row lock is held on row 'u2'.
1715 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1716 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1717 FROM performance_schema.data_locks
1718 WHERE LOCK_TYPE='RECORD' AND
1719 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1720 "Allow the same row to be read for update by SELECT FOR UPDATE"
1721 connection update_con
1722 SELECT User FROM mysql.global_grants
1723 WHERE User='u2' FOR UPDATE;
1724 User
1725 u2
1726 # Make sure X record locks are held on row 'u2'
1727 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1728 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1729 FROM performance_schema.data_locks
1730 WHERE LOCK_TYPE='RECORD' AND
1731 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1732 tab INDEX_NAME LOCK_MODE Has_lock
1733 mysql.global_grants PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1734 COMMIT;
1735 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
1736 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1737 START TRANSACTION;
1738 SELECT User FROM mysql.global_grants
1739 WHERE User='u2' LOCK IN SHARE MODE;
1740 User
1741 u2
1742 Warnings:
1743 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1744 # Make sure no read row lock is held on row 'u2'.
1745 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1746 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1747 FROM performance_schema.data_locks
1748 WHERE LOCK_TYPE='RECORD' AND
1749 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1750 "Allow the same row to be read for update by SELECT FOR UPDATE"
1751 connection update_con
1752 SELECT User FROM mysql.global_grants
1753 WHERE User='u2' FOR UPDATE;
1754 User
1755 u2
1756 # Make sure X record locks are held on row 'u2'
1757 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1758 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1759 FROM performance_schema.data_locks
1760 WHERE LOCK_TYPE='RECORD' AND
1761 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1762 tab INDEX_NAME LOCK_MODE Has_lock
1763 mysql.global_grants PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1764 COMMIT;
1765 # Run the test for all isolation level for table mysql.password_history
1766 "Case 1.1: Start a transaction in SERIALIZABLE"
1767 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1768 START TRANSACTION;
1769 SELECT User FROM mysql.password_history WHERE User='u2';
1770 User
1771 u2
1772 Warnings:
1773 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1774 # Make sure no read row lock is held on row 'u2'.
1775 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1776 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1777 FROM performance_schema.data_locks
1778 WHERE LOCK_TYPE='RECORD' AND
1779 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1780 "Allow the same row to be read for update by SELECT FOR UPDATE"
1781 connection update_con
1782 SELECT User FROM mysql.password_history
1783 WHERE User='u2' FOR UPDATE;
1784 User
1785 u2
1786 # Make sure X record locks are held on row 'u2'
1787 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1788 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1789 FROM performance_schema.data_locks
1790 WHERE LOCK_TYPE='RECORD' AND
1791 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1792 tab INDEX_NAME LOCK_MODE Has_lock
1793 mysql.password_history PRIMARY X Acquired row lock on u2/u2x
1794 COMMIT;
1795 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
1796 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1797 START TRANSACTION;
1798 SELECT User FROM mysql.password_history
1799 WHERE User='u2' LOCK IN SHARE MODE;
1800 User
1801 u2
1802 Warnings:
1803 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1804 # Make sure no read row lock is held on row 'u2'.
1805 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1806 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1807 FROM performance_schema.data_locks
1808 WHERE LOCK_TYPE='RECORD' AND
1809 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1810 "Allow the same row to be read for update by SELECT FOR UPDATE"
1811 connection update_con
1812 SELECT User FROM mysql.password_history
1813 WHERE User='u2' FOR UPDATE;
1814 User
1815 u2
1816 # Make sure X record locks are held on row 'u2'
1817 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1818 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1819 FROM performance_schema.data_locks
1820 WHERE LOCK_TYPE='RECORD' AND
1821 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1822 tab INDEX_NAME LOCK_MODE Has_lock
1823 mysql.password_history PRIMARY X Acquired row lock on u2/u2x
1824 COMMIT;
1825 "Case 1.2: Start a transaction in REPEATABLE READ"
1826 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1827 START TRANSACTION;
1828 SELECT User FROM mysql.password_history WHERE User='u2';
1829 User
1830 u2
1831 # Make sure no read row lock is held on row 'u2'.
1832 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1833 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1834 FROM performance_schema.data_locks
1835 WHERE LOCK_TYPE='RECORD' AND
1836 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1837 "Allow the same row to be read for update by SELECT FOR UPDATE"
1838 connection update_con
1839 SELECT User FROM mysql.password_history
1840 WHERE User='u2' FOR UPDATE;
1841 User
1842 u2
1843 # Make sure X record locks are held on row 'u2'
1844 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1845 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1846 FROM performance_schema.data_locks
1847 WHERE LOCK_TYPE='RECORD' AND
1848 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1849 tab INDEX_NAME LOCK_MODE Has_lock
1850 mysql.password_history PRIMARY X Acquired row lock on u2/u2x
1851 COMMIT;
1852 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
1853 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1854 START TRANSACTION;
1855 SELECT User FROM mysql.password_history
1856 WHERE User='u2' LOCK IN SHARE MODE;
1857 User
1858 u2
1859 Warnings:
1860 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1861 # Make sure no read row lock is held on row 'u2'.
1862 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1863 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1864 FROM performance_schema.data_locks
1865 WHERE LOCK_TYPE='RECORD' AND
1866 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1867 "Allow the same row to be read for update by SELECT FOR UPDATE"
1868 connection update_con
1869 SELECT User FROM mysql.password_history
1870 WHERE User='u2' FOR UPDATE;
1871 User
1872 u2
1873 # Make sure X record locks are held on row 'u2'
1874 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1875 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1876 FROM performance_schema.data_locks
1877 WHERE LOCK_TYPE='RECORD' AND
1878 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1879 tab INDEX_NAME LOCK_MODE Has_lock
1880 mysql.password_history PRIMARY X Acquired row lock on u2/u2x
1881 COMMIT;
1882 "Case 1.3: Start a transaction in READ COMMITTED"
1883 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1884 START TRANSACTION;
1885 SELECT User FROM mysql.password_history WHERE User='u2';
1886 User
1887 u2
1888 # Make sure no read row lock is held on row 'u2'.
1889 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1890 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1891 FROM performance_schema.data_locks
1892 WHERE LOCK_TYPE='RECORD' AND
1893 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1894 "Allow the same row to be read for update by SELECT FOR UPDATE"
1895 connection update_con
1896 SELECT User FROM mysql.password_history
1897 WHERE User='u2' FOR UPDATE;
1898 User
1899 u2
1900 # Make sure X record locks are held on row 'u2'
1901 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1902 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1903 FROM performance_schema.data_locks
1904 WHERE LOCK_TYPE='RECORD' AND
1905 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1906 tab INDEX_NAME LOCK_MODE Has_lock
1907 mysql.password_history PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1908 COMMIT;
1909 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
1910 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
1911 START TRANSACTION;
1912 SELECT User FROM mysql.password_history
1913 WHERE User='u2' LOCK IN SHARE MODE;
1914 User
1915 u2
1916 Warnings:
1917 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1918 # Make sure no read row lock is held on row 'u2'.
1919 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1920 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1921 FROM performance_schema.data_locks
1922 WHERE LOCK_TYPE='RECORD' AND
1923 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1924 "Allow the same row to be read for update by SELECT FOR UPDATE"
1925 connection update_con
1926 SELECT User FROM mysql.password_history
1927 WHERE User='u2' FOR UPDATE;
1928 User
1929 u2
1930 # Make sure X record locks are held on row 'u2'
1931 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1932 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1933 FROM performance_schema.data_locks
1934 WHERE LOCK_TYPE='RECORD' AND
1935 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1936 tab INDEX_NAME LOCK_MODE Has_lock
1937 mysql.password_history PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1938 COMMIT;
1939 "Case 1.4: Start a transaction in READ UNCOMMITTED"
1940 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1941 START TRANSACTION;
1942 SELECT User FROM mysql.password_history WHERE User='u2';
1943 User
1944 u2
1945 # Make sure no read row lock is held on row 'u2'.
1946 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1947 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1948 FROM performance_schema.data_locks
1949 WHERE LOCK_TYPE='RECORD' AND
1950 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1951 "Allow the same row to be read for update by SELECT FOR UPDATE"
1952 connection update_con
1953 SELECT User FROM mysql.password_history
1954 WHERE User='u2' FOR UPDATE;
1955 User
1956 u2
1957 # Make sure X record locks are held on row 'u2'
1958 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1959 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1960 FROM performance_schema.data_locks
1961 WHERE LOCK_TYPE='RECORD' AND
1962 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1963 tab INDEX_NAME LOCK_MODE Has_lock
1964 mysql.password_history PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1965 COMMIT;
1966 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
1967 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1968 START TRANSACTION;
1969 SELECT User FROM mysql.password_history
1970 WHERE User='u2' LOCK IN SHARE MODE;
1971 User
1972 u2
1973 Warnings:
1974 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
1975 # Make sure no read row lock is held on row 'u2'.
1976 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1977 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1978 FROM performance_schema.data_locks
1979 WHERE LOCK_TYPE='RECORD' AND
1980 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
1981 "Allow the same row to be read for update by SELECT FOR UPDATE"
1982 connection update_con
1983 SELECT User FROM mysql.password_history
1984 WHERE User='u2' FOR UPDATE;
1985 User
1986 u2
1987 # Make sure X record locks are held on row 'u2'
1988 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
1989 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
1990 FROM performance_schema.data_locks
1991 WHERE LOCK_TYPE='RECORD' AND
1992 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
1993 tab INDEX_NAME LOCK_MODE Has_lock
1994 mysql.password_history PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
1995 COMMIT;
1996 # Run the test for all isolation level for table mysql.default_roles
1997 "Case 1.1: Start a transaction in SERIALIZABLE"
1998 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1999 START TRANSACTION;
2000 SELECT User FROM mysql.default_roles WHERE User='u2';
2001 User
2002 u2
2003 Warnings:
2004 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2005 # Make sure no read row lock is held on row 'u2'.
2006 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2007 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2008 FROM performance_schema.data_locks
2009 WHERE LOCK_TYPE='RECORD' AND
2010 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2011 "Allow the same row to be read for update by SELECT FOR UPDATE"
2012 connection update_con
2013 SELECT User FROM mysql.default_roles
2014 WHERE User='u2' FOR UPDATE;
2015 User
2016 u2
2017 # Make sure X record locks are held on row 'u2'
2018 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2019 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2020 FROM performance_schema.data_locks
2021 WHERE LOCK_TYPE='RECORD' AND
2022 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2023 tab INDEX_NAME LOCK_MODE Has_lock
2024 mysql.default_roles PRIMARY X Acquired row lock on u2/u2x
2025 COMMIT;
2026 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
2027 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2028 START TRANSACTION;
2029 SELECT User FROM mysql.default_roles
2030 WHERE User='u2' LOCK IN SHARE MODE;
2031 User
2032 u2
2033 Warnings:
2034 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2035 # Make sure no read row lock is held on row 'u2'.
2036 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2037 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2038 FROM performance_schema.data_locks
2039 WHERE LOCK_TYPE='RECORD' AND
2040 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2041 "Allow the same row to be read for update by SELECT FOR UPDATE"
2042 connection update_con
2043 SELECT User FROM mysql.default_roles
2044 WHERE User='u2' FOR UPDATE;
2045 User
2046 u2
2047 # Make sure X record locks are held on row 'u2'
2048 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2049 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2050 FROM performance_schema.data_locks
2051 WHERE LOCK_TYPE='RECORD' AND
2052 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2053 tab INDEX_NAME LOCK_MODE Has_lock
2054 mysql.default_roles PRIMARY X Acquired row lock on u2/u2x
2055 COMMIT;
2056 "Case 1.2: Start a transaction in REPEATABLE READ"
2057 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2058 START TRANSACTION;
2059 SELECT User FROM mysql.default_roles WHERE User='u2';
2060 User
2061 u2
2062 # Make sure no read row lock is held on row 'u2'.
2063 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2064 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2065 FROM performance_schema.data_locks
2066 WHERE LOCK_TYPE='RECORD' AND
2067 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2068 "Allow the same row to be read for update by SELECT FOR UPDATE"
2069 connection update_con
2070 SELECT User FROM mysql.default_roles
2071 WHERE User='u2' FOR UPDATE;
2072 User
2073 u2
2074 # Make sure X record locks are held on row 'u2'
2075 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2076 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2077 FROM performance_schema.data_locks
2078 WHERE LOCK_TYPE='RECORD' AND
2079 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2080 tab INDEX_NAME LOCK_MODE Has_lock
2081 mysql.default_roles PRIMARY X Acquired row lock on u2/u2x
2082 COMMIT;
2083 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
2084 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2085 START TRANSACTION;
2086 SELECT User FROM mysql.default_roles
2087 WHERE User='u2' LOCK IN SHARE MODE;
2088 User
2089 u2
2090 Warnings:
2091 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2092 # Make sure no read row lock is held on row 'u2'.
2093 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2094 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2095 FROM performance_schema.data_locks
2096 WHERE LOCK_TYPE='RECORD' AND
2097 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2098 "Allow the same row to be read for update by SELECT FOR UPDATE"
2099 connection update_con
2100 SELECT User FROM mysql.default_roles
2101 WHERE User='u2' FOR UPDATE;
2102 User
2103 u2
2104 # Make sure X record locks are held on row 'u2'
2105 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2106 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2107 FROM performance_schema.data_locks
2108 WHERE LOCK_TYPE='RECORD' AND
2109 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2110 tab INDEX_NAME LOCK_MODE Has_lock
2111 mysql.default_roles PRIMARY X Acquired row lock on u2/u2x
2112 COMMIT;
2113 "Case 1.3: Start a transaction in READ COMMITTED"
2114 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2115 START TRANSACTION;
2116 SELECT User FROM mysql.default_roles WHERE User='u2';
2117 User
2118 u2
2119 # Make sure no read row lock is held on row 'u2'.
2120 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2121 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2122 FROM performance_schema.data_locks
2123 WHERE LOCK_TYPE='RECORD' AND
2124 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2125 "Allow the same row to be read for update by SELECT FOR UPDATE"
2126 connection update_con
2127 SELECT User FROM mysql.default_roles
2128 WHERE User='u2' FOR UPDATE;
2129 User
2130 u2
2131 # Make sure X record locks are held on row 'u2'
2132 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2133 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2134 FROM performance_schema.data_locks
2135 WHERE LOCK_TYPE='RECORD' AND
2136 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2137 tab INDEX_NAME LOCK_MODE Has_lock
2138 mysql.default_roles PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2139 COMMIT;
2140 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
2141 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2142 START TRANSACTION;
2143 SELECT User FROM mysql.default_roles
2144 WHERE User='u2' LOCK IN SHARE MODE;
2145 User
2146 u2
2147 Warnings:
2148 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2149 # Make sure no read row lock is held on row 'u2'.
2150 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2151 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2152 FROM performance_schema.data_locks
2153 WHERE LOCK_TYPE='RECORD' AND
2154 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2155 "Allow the same row to be read for update by SELECT FOR UPDATE"
2156 connection update_con
2157 SELECT User FROM mysql.default_roles
2158 WHERE User='u2' FOR UPDATE;
2159 User
2160 u2
2161 # Make sure X record locks are held on row 'u2'
2162 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2163 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2164 FROM performance_schema.data_locks
2165 WHERE LOCK_TYPE='RECORD' AND
2166 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2167 tab INDEX_NAME LOCK_MODE Has_lock
2168 mysql.default_roles PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2169 COMMIT;
2170 "Case 1.4: Start a transaction in READ UNCOMMITTED"
2171 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2172 START TRANSACTION;
2173 SELECT User FROM mysql.default_roles WHERE User='u2';
2174 User
2175 u2
2176 # Make sure no read row lock is held on row 'u2'.
2177 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2178 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2179 FROM performance_schema.data_locks
2180 WHERE LOCK_TYPE='RECORD' AND
2181 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2182 "Allow the same row to be read for update by SELECT FOR UPDATE"
2183 connection update_con
2184 SELECT User FROM mysql.default_roles
2185 WHERE User='u2' FOR UPDATE;
2186 User
2187 u2
2188 # Make sure X record locks are held on row 'u2'
2189 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2190 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2191 FROM performance_schema.data_locks
2192 WHERE LOCK_TYPE='RECORD' AND
2193 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2194 tab INDEX_NAME LOCK_MODE Has_lock
2195 mysql.default_roles PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2196 COMMIT;
2197 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
2198 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2199 START TRANSACTION;
2200 SELECT User FROM mysql.default_roles
2201 WHERE User='u2' LOCK IN SHARE MODE;
2202 User
2203 u2
2204 Warnings:
2205 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2206 # Make sure no read row lock is held on row 'u2'.
2207 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2208 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2209 FROM performance_schema.data_locks
2210 WHERE LOCK_TYPE='RECORD' AND
2211 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2212 "Allow the same row to be read for update by SELECT FOR UPDATE"
2213 connection update_con
2214 SELECT User FROM mysql.default_roles
2215 WHERE User='u2' FOR UPDATE;
2216 User
2217 u2
2218 # Make sure X record locks are held on row 'u2'
2219 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2220 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2221 FROM performance_schema.data_locks
2222 WHERE LOCK_TYPE='RECORD' AND
2223 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2224 tab INDEX_NAME LOCK_MODE Has_lock
2225 mysql.default_roles PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2226 COMMIT;
2227 # Run the test for all isolation level for table mysql.role_edges
2228 "Case 1.1: Start a transaction in SERIALIZABLE"
2229 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2230 START TRANSACTION;
2231 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
2232 To_user
2233 u2
2234 Warnings:
2235 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2236 # Make sure no read row lock is held on row 'u2'.
2237 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2238 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2239 FROM performance_schema.data_locks
2240 WHERE LOCK_TYPE='RECORD' AND
2241 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2242 "Allow the same row to be read for update by SELECT FOR UPDATE"
2243 connection update_con
2244 SELECT To_user FROM mysql.role_edges
2245 WHERE To_user='u2' FOR UPDATE;
2246 To_user
2247 u2
2248 # Make sure X record locks are held on row 'u2'
2249 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2250 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2251 FROM performance_schema.data_locks
2252 WHERE LOCK_TYPE='RECORD' AND
2253 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2254 tab INDEX_NAME LOCK_MODE Has_lock
2255 mysql.role_edges PRIMARY X Acquired row lock on u2/u2x
2256 COMMIT;
2257 "Case 1.1: Using SERIALIZABLE with SELECT LOCK IN SHARE MODE"
2258 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2259 START TRANSACTION;
2260 SELECT To_user FROM mysql.role_edges
2261 WHERE To_user='u2' LOCK IN SHARE MODE;
2262 To_user
2263 u2
2264 Warnings:
2265 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2266 # Make sure no read row lock is held on row 'u2'.
2267 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2268 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2269 FROM performance_schema.data_locks
2270 WHERE LOCK_TYPE='RECORD' AND
2271 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2272 "Allow the same row to be read for update by SELECT FOR UPDATE"
2273 connection update_con
2274 SELECT To_user FROM mysql.role_edges
2275 WHERE To_user='u2' FOR UPDATE;
2276 To_user
2277 u2
2278 # Make sure X record locks are held on row 'u2'
2279 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2280 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2281 FROM performance_schema.data_locks
2282 WHERE LOCK_TYPE='RECORD' AND
2283 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2284 tab INDEX_NAME LOCK_MODE Has_lock
2285 mysql.role_edges PRIMARY X Acquired row lock on u2/u2x
2286 COMMIT;
2287 "Case 1.2: Start a transaction in REPEATABLE READ"
2288 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2289 START TRANSACTION;
2290 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
2291 To_user
2292 u2
2293 # Make sure no read row lock is held on row 'u2'.
2294 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2295 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2296 FROM performance_schema.data_locks
2297 WHERE LOCK_TYPE='RECORD' AND
2298 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2299 "Allow the same row to be read for update by SELECT FOR UPDATE"
2300 connection update_con
2301 SELECT To_user FROM mysql.role_edges
2302 WHERE To_user='u2' FOR UPDATE;
2303 To_user
2304 u2
2305 # Make sure X record locks are held on row 'u2'
2306 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2307 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2308 FROM performance_schema.data_locks
2309 WHERE LOCK_TYPE='RECORD' AND
2310 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2311 tab INDEX_NAME LOCK_MODE Has_lock
2312 mysql.role_edges PRIMARY X Acquired row lock on u2/u2x
2313 COMMIT;
2314 "Case 1.2: Using REPEATABLE READ with SELECT LOCK IN SHARE MODE"
2315 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2316 START TRANSACTION;
2317 SELECT To_user FROM mysql.role_edges
2318 WHERE To_user='u2' LOCK IN SHARE MODE;
2319 To_user
2320 u2
2321 Warnings:
2322 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2323 # Make sure no read row lock is held on row 'u2'.
2324 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2325 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2326 FROM performance_schema.data_locks
2327 WHERE LOCK_TYPE='RECORD' AND
2328 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2329 "Allow the same row to be read for update by SELECT FOR UPDATE"
2330 connection update_con
2331 SELECT To_user FROM mysql.role_edges
2332 WHERE To_user='u2' FOR UPDATE;
2333 To_user
2334 u2
2335 # Make sure X record locks are held on row 'u2'
2336 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2337 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2338 FROM performance_schema.data_locks
2339 WHERE LOCK_TYPE='RECORD' AND
2340 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2341 tab INDEX_NAME LOCK_MODE Has_lock
2342 mysql.role_edges PRIMARY X Acquired row lock on u2/u2x
2343 COMMIT;
2344 "Case 1.3: Start a transaction in READ COMMITTED"
2345 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2346 START TRANSACTION;
2347 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
2348 To_user
2349 u2
2350 # Make sure no read row lock is held on row 'u2'.
2351 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2352 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2353 FROM performance_schema.data_locks
2354 WHERE LOCK_TYPE='RECORD' AND
2355 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2356 "Allow the same row to be read for update by SELECT FOR UPDATE"
2357 connection update_con
2358 SELECT To_user FROM mysql.role_edges
2359 WHERE To_user='u2' FOR UPDATE;
2360 To_user
2361 u2
2362 # Make sure X record locks are held on row 'u2'
2363 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2364 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2365 FROM performance_schema.data_locks
2366 WHERE LOCK_TYPE='RECORD' AND
2367 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2368 tab INDEX_NAME LOCK_MODE Has_lock
2369 mysql.role_edges PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2370 COMMIT;
2371 "Case 1.3: Using READ COMMITTED with SELECT LOCK IN SHARE MODE"
2372 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2373 START TRANSACTION;
2374 SELECT To_user FROM mysql.role_edges
2375 WHERE To_user='u2' LOCK IN SHARE MODE;
2376 To_user
2377 u2
2378 Warnings:
2379 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2380 # Make sure no read row lock is held on row 'u2'.
2381 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2382 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2383 FROM performance_schema.data_locks
2384 WHERE LOCK_TYPE='RECORD' AND
2385 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2386 "Allow the same row to be read for update by SELECT FOR UPDATE"
2387 connection update_con
2388 SELECT To_user FROM mysql.role_edges
2389 WHERE To_user='u2' FOR UPDATE;
2390 To_user
2391 u2
2392 # Make sure X record locks are held on row 'u2'
2393 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2394 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2395 FROM performance_schema.data_locks
2396 WHERE LOCK_TYPE='RECORD' AND
2397 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2398 tab INDEX_NAME LOCK_MODE Has_lock
2399 mysql.role_edges PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2400 COMMIT;
2401 "Case 1.4: Start a transaction in READ UNCOMMITTED"
2402 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2403 START TRANSACTION;
2404 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
2405 To_user
2406 u2
2407 # Make sure no read row lock is held on row 'u2'.
2408 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2409 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2410 FROM performance_schema.data_locks
2411 WHERE LOCK_TYPE='RECORD' AND
2412 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2413 "Allow the same row to be read for update by SELECT FOR UPDATE"
2414 connection update_con
2415 SELECT To_user FROM mysql.role_edges
2416 WHERE To_user='u2' FOR UPDATE;
2417 To_user
2418 u2
2419 # Make sure X record locks are held on row 'u2'
2420 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2421 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2422 FROM performance_schema.data_locks
2423 WHERE LOCK_TYPE='RECORD' AND
2424 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2425 tab INDEX_NAME LOCK_MODE Has_lock
2426 mysql.role_edges PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2427 COMMIT;
2428 "Case 1.4: Using READ UNCOMMITTED with SELECT LOCK IN SHARE MODE"
2429 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2430 START TRANSACTION;
2431 SELECT To_user FROM mysql.role_edges
2432 WHERE To_user='u2' LOCK IN SHARE MODE;
2433 To_user
2434 u2
2435 Warnings:
2436 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2437 # Make sure no read row lock is held on row 'u2'.
2438 eval SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2439 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2440 FROM performance_schema.data_locks
2441 WHERE LOCK_TYPE='RECORD' AND
2442 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0)
2443 "Allow the same row to be read for update by SELECT FOR UPDATE"
2444 connection update_con
2445 SELECT To_user FROM mysql.role_edges
2446 WHERE To_user='u2' FOR UPDATE;
2447 To_user
2448 u2
2449 # Make sure X record locks are held on row 'u2'
2450 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2451 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2452 FROM performance_schema.data_locks
2453 WHERE LOCK_TYPE='RECORD' AND
2454 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2455 tab INDEX_NAME LOCK_MODE Has_lock
2456 mysql.role_edges PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
2457 COMMIT;
2458 # Case 2 (with SERIALIZABLE isolation level):
2459 # Using a separate transaction (With START TRANSACTION)
2460 # Read ACL table in SELECT in all isolation mode
2461 # and run DDL/DML on ACL tables in concurrent.
2462 SELECT User FROM mysql.user WHERE User='u2';
2463 User
2464 u2
2465 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2466 START TRANSACTION;
2467 SELECT User FROM mysql.user WHERE User='u2';
2468 User
2469 u2
2470 Warnings:
2471 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2472 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2473 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2474 FROM performance_schema.data_locks
2475 WHERE LOCK_TYPE='RECORD' AND
2476 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2477 tab INDEX_NAME LOCK_MODE Has_lock
2478 DELETE FROM mysql.user WHERE User='u2';
2479 INSERT INTO mysql.user SELECT * FROM user_bk;
2480 REPLACE INTO mysql.user SELECT * FROM user_bk;
2481 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2482 CREATE USER someuser;
2483 ALTER USER someuser IDENTIFIED BY 'somepass';
2484 DROP USER someuser;
2485 COMMIT;
2486 # Case 2.1 (with SERIALIZABLE isolation level):
2487 # Using a separate transaction (With START TRANSACTION)
2488 # Read ACL table in SELECT .. HIGH PRIORITY in all isolation mode
2489 # and run DDL/DML on ACL tables in concurrent.
2490 SELECT User FROM mysql.user WHERE User='u2';
2491 User
2492 u2
2493 Warnings:
2494 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2495 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2496 START TRANSACTION;
2497 SELECT HIGH_PRIORITY User FROM mysql.user WHERE User='u2';
2498 User
2499 u2
2500 Warnings:
2501 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2502 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2503 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2504 FROM performance_schema.data_locks
2505 WHERE LOCK_TYPE='RECORD' AND
2506 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2507 tab INDEX_NAME LOCK_MODE Has_lock
2508 DELETE FROM mysql.user WHERE User='u2';
2509 INSERT INTO mysql.user SELECT * FROM user_bk;
2510 REPLACE INTO mysql.user SELECT * FROM user_bk;
2511 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2512 CREATE USER someuser;
2513 ALTER USER someuser IDENTIFIED BY 'somepass';
2514 DROP USER someuser;
2515 COMMIT;
2516 # Case 3 (with SERIALIZABLE isolation level):
2517 # Using a separate transaction (With START TRANSACTION)
2518 # Read ACL table in SELECT in all isolation mode
2519 # and run DDL/DML on ACL tables in concurrent.
2520 SELECT User FROM mysql.user WHERE User='u2';
2521 User
2522 u2
2523 Warnings:
2524 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2525 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2526 set autocommit=0;
2527 SELECT User FROM mysql.user WHERE User='u2';
2528 User
2529 u2
2530 Warnings:
2531 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2532 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2533 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2534 FROM performance_schema.data_locks
2535 WHERE LOCK_TYPE='RECORD' AND
2536 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2537 tab INDEX_NAME LOCK_MODE Has_lock
2538 DELETE FROM mysql.user WHERE User='u2';
2539 INSERT INTO mysql.user SELECT * FROM user_bk;
2540 REPLACE INTO mysql.user SELECT * FROM user_bk;
2541 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2542 CREATE USER someuser;
2543 ALTER USER someuser IDENTIFIED BY 'somepass';
2544 DROP USER someuser;
2545 COMMIT;
2546 set autocommit=default;
2547 # Case 4 (with SERIALIZABLE isolation level):
2548 # Read ACL table in DO command in different isolation mode
2549 # and run DDL/DML on ACL tables in concurrent.
2550 SELECT User FROM mysql.user WHERE User='u2';
2551 User
2552 u2
2553 Warnings:
2554 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2555 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2556 START TRANSACTION;
2557 DO (SELECT User FROM mysql.user WHERE User='u2');
2558 Warnings:
2559 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2560 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2561 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2562 FROM performance_schema.data_locks
2563 WHERE LOCK_TYPE='RECORD' AND
2564 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2565 tab INDEX_NAME LOCK_MODE Has_lock
2566 DELETE FROM mysql.user WHERE User='u2';
2567 INSERT INTO mysql.user SELECT * FROM user_bk;
2568 REPLACE INTO mysql.user SELECT * FROM user_bk;
2569 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2570 CREATE USER someuser;
2571 ALTER USER someuser IDENTIFIED BY 'somepass';
2572 DROP USER someuser;
2573 COMMIT;
2574 # Case 5 (with SERIALIZABLE isolation level):
2575 # Read ACL table in SET command in different isolation mode
2576 # and run DDL/DML on ACL tables in concurrent.
2577 SELECT User FROM mysql.user WHERE User='u2';
2578 User
2579 u2
2580 Warnings:
2581 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2582 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2583 START TRANSACTION;
2584 SET @u2_host = (SELECT Host FROM mysql.user WHERE User='u2');
2585 Warnings:
2586 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2587 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2588 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2589 FROM performance_schema.data_locks
2590 WHERE LOCK_TYPE='RECORD' AND
2591 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2592 tab INDEX_NAME LOCK_MODE Has_lock
2593 DELETE FROM mysql.user WHERE User='u2';
2594 INSERT INTO mysql.user SELECT * FROM user_bk;
2595 REPLACE INTO mysql.user SELECT * FROM user_bk;
2596 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2597 CREATE USER someuser;
2598 ALTER USER someuser IDENTIFIED BY 'somepass';
2599 DROP USER someuser;
2600 COMMIT;
2601 # Case 6:
2602 # Read ACL table in INSERT ... SELECT command in different
2603 # isolation mode and run DDL/DML on ACL tables in concurrent.
2604 SELECT User FROM mysql.user WHERE User='u2';
2605 User
2606 u2
2607 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2608 START TRANSACTION;
2609 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
2610 Warnings:
2611 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2612 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2613 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2614 FROM performance_schema.data_locks
2615 WHERE LOCK_TYPE='RECORD' AND
2616 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2617 tab INDEX_NAME LOCK_MODE Has_lock
2618 DELETE FROM mysql.user WHERE User='u2';
2619 INSERT INTO mysql.user SELECT * FROM user_bk;
2620 REPLACE INTO mysql.user SELECT * FROM user_bk;
2621 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2622 CREATE USER someuser;
2623 ALTER USER someuser IDENTIFIED BY 'somepass';
2624 DROP USER someuser;
2625 COMMIT;
2626 # Case 7:
2627 # Read ACL table in UPDATE ... WHERE SELECT in different
2628 # isolation mode and run DDL/DML on ACL tables in concurrent.
2629 SELECT User FROM mysql.user WHERE User='u2';
2630 User
2631 u2
2632 Warnings:
2633 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2634 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2635 START TRANSACTION;
2636 UPDATE user_copy SET User='u2x'
2637 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
2638 Warnings:
2639 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2640 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2641 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2642 FROM performance_schema.data_locks
2643 WHERE LOCK_TYPE='RECORD' AND
2644 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2645 tab INDEX_NAME LOCK_MODE Has_lock
2646 DELETE FROM mysql.user WHERE User='u2';
2647 INSERT INTO mysql.user SELECT * FROM user_bk;
2648 REPLACE INTO mysql.user SELECT * FROM user_bk;
2649 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2650 CREATE USER someuser;
2651 ALTER USER someuser IDENTIFIED BY 'somepass';
2652 DROP USER someuser;
2653 COMMIT;
2654 # Case 8:
2655 # Read ACL table in Multi-table UPDATE ... SELECT in different
2656 # isolation mode and run DDL/DML on ACL tables in concurrent.
2657 SELECT User FROM mysql.user WHERE User='u2';
2658 User
2659 u2
2660 Warnings:
2661 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2662 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2663 START TRANSACTION;
2664 UPDATE t1, user_copy SET user_copy.Host='instance1'
2665 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
2666 Warnings:
2667 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2668 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2669 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2670 FROM performance_schema.data_locks
2671 WHERE LOCK_TYPE='RECORD' AND
2672 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2673 tab INDEX_NAME LOCK_MODE Has_lock
2674 DELETE FROM mysql.user WHERE User='u2';
2675 INSERT INTO mysql.user SELECT * FROM user_bk;
2676 REPLACE INTO mysql.user SELECT * FROM user_bk;
2677 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2678 CREATE USER someuser;
2679 ALTER USER someuser IDENTIFIED BY 'somepass';
2680 DROP USER someuser;
2681 COMMIT;
2682 # Case 9:
2683 # Read ACL table in DELETE ... WHERE SELECT in different
2684 # isolation mode and run DDL/DML on ACL tables in concurrent.
2685 SELECT User FROM mysql.user WHERE User='u2';
2686 User
2687 u2
2688 Warnings:
2689 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2690 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2691 START TRANSACTION;
2692 DELETE FROM user_copy
2693 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
2694 Warnings:
2695 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2696 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2697 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2698 FROM performance_schema.data_locks
2699 WHERE LOCK_TYPE='RECORD' AND
2700 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2701 tab INDEX_NAME LOCK_MODE Has_lock
2702 DELETE FROM mysql.user WHERE User='u2';
2703 INSERT INTO mysql.user SELECT * FROM user_bk;
2704 REPLACE INTO mysql.user SELECT * FROM user_bk;
2705 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2706 CREATE USER someuser;
2707 ALTER USER someuser IDENTIFIED BY 'somepass';
2708 DROP USER someuser;
2709 COMMIT;
2710 # Case 10:
2711 # Read ACL table in Multi-table DELETE ... WHERE SELECT in different
2712 # isolation mode and run DDL/DML on ACL tables in concurrent.
2713 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
2714 SELECT User FROM mysql.user WHERE User='u2';
2715 User
2716 u2
2717 Warnings:
2718 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2719 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2720 START TRANSACTION;
2721 DELETE user_copy FROM user_copy JOIN (SELECT * FROM user_copy) tuser_copy
2722 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
2723 Warnings:
2724 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2725 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2726 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2727 FROM performance_schema.data_locks
2728 WHERE LOCK_TYPE='RECORD' AND
2729 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2730 tab INDEX_NAME LOCK_MODE Has_lock
2731 DELETE FROM mysql.user WHERE User='u2';
2732 INSERT INTO mysql.user SELECT * FROM user_bk;
2733 REPLACE INTO mysql.user SELECT * FROM user_bk;
2734 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2735 CREATE USER someuser;
2736 ALTER USER someuser IDENTIFIED BY 'somepass';
2737 DROP USER someuser;
2738 COMMIT;
2739 # Case 11:
2740 # Read ACL table in CREATE TABLE ... SELECT in different
2741 # isolation mode and run DDL/DML on ACL tables in concurrent.
2742 SELECT User FROM mysql.user WHERE User='u2';
2743 User
2744 u2
2745 Warnings:
2746 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2747 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2748 SET SESSION DEBUG_SYNC='create_select_after_write_rows_event SIGNAL parked WAIT_FOR go';
2749 CREATE TABLE tmp AS SELECT * FROM mysql.user WHERE User='u2';
2750 SET DEBUG_SYNC= 'now WAIT_FOR parked';
2751 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2752 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2753 FROM performance_schema.data_locks
2754 WHERE LOCK_TYPE='RECORD' AND
2755 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2756 tab INDEX_NAME LOCK_MODE Has_lock
2757 DELETE FROM mysql.user WHERE User='u2';
2758 INSERT INTO mysql.user SELECT * FROM user_bk;
2759 REPLACE INTO mysql.user SELECT * FROM user_bk;
2760 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2761 CREATE USER someuser;
2762 ALTER USER someuser IDENTIFIED BY 'somepass';
2763 DROP USER someuser;
2764 SET DEBUG_SYNC= 'now SIGNAL go';
2765 Warnings:
2766 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2767 SET SESSION DEBUG_SYNC=RESET;
2768 COMMIT;
2769 SET SESSION DEBUG_SYNC=RESET;
2770 DROP TABLE tmp;
2771 # Case 12:
2772 # Read ACL table in LOCK TABLE for READ mode and
2773 # Make sure a concurrent ACL DML on same table is blocked.
2774 SELECT User FROM mysql.user WHERE User='u2';
2775 User
2776 u2
2777 Warnings:
2778 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2779 LOCK TABLE mysql.user READ;
2780 SET SESSION lock_wait_timeout=1;
2781 SET SESSION innodb_lock_wait_timeout=1;
2782 # Make sure that we do not allow updating ACL table.
2783 INSERT INTO mysql.user SELECT * FROM user_bk;
2784 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2785 REPLACE INTO mysql.user SELECT * FROM user_bk;
2786 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2787 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2788 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2789 DELETE FROM mysql.user WHERE User='u2';
2790 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2791 UNLOCK TABLES;
2792 # Case 13:
2793 # Run FTWRL READ mode and
2794 # make sure a concurrent ACL DML on same table is blocked.
2795 SELECT User FROM mysql.user WHERE User='u2';
2796 User
2797 u2
2798 Warnings:
2799 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2800 FLUSH TABLES WITH READ LOCK;
2801 INSERT INTO mysql.user SELECT * FROM user_bk;
2802 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2803 REPLACE INTO mysql.user SELECT * FROM user_bk;
2804 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2805 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2806 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2807 DELETE FROM mysql.user WHERE User='u2';
2808 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2809 UNLOCK TABLES;
2810 # Case 14:
2811 # Update ACL table in a DML inside a SP in different isolation mode.
2812 # Make sure a concurrent ACL DML on same table is blocked.
2813 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
2814 SELECT User FROM mysql.user WHERE User='u2';
2815 User
2816 u2
2817 Warnings:
2818 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2819 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2820 START TRANSACTION;
2821 CALL proc2();
2822 # Make sure X record locks are held on ACL table.
2823 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2824 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2825 FROM performance_schema.data_locks
2826 WHERE LOCK_TYPE='RECORD' AND
2827 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2828 tab INDEX_NAME LOCK_MODE Has_lock
2829 mysql.user PRIMARY X Acquired row lock on u2/u2x
2830 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
2831 # Make sure that we do not allow updating ACL table.
2832 INSERT INTO mysql.user SELECT * FROM user_bk;
2833 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2834 REPLACE INTO mysql.user SELECT * FROM user_bk;
2835 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2836 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2837 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2838 DELETE FROM mysql.user WHERE User='u2';
2839 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2840 ROLLBACK;
2841 SELECT User FROM mysql.user WHERE User='u2';
2842 User
2843 u2
2844 Warnings:
2845 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2846 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2847 START TRANSACTION;
2848 SELECT func1();
2849 func1()
2850 0
2851 Warnings:
2852 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2853 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2854 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2855 FROM performance_schema.data_locks
2856 WHERE LOCK_TYPE='RECORD' AND
2857 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2858 tab INDEX_NAME LOCK_MODE Has_lock
2859 DELETE FROM mysql.user WHERE User='u2';
2860 INSERT INTO mysql.user SELECT * FROM user_bk;
2861 REPLACE INTO mysql.user SELECT * FROM user_bk;
2862 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
2863 CREATE USER someuser;
2864 ALTER USER someuser IDENTIFIED BY 'somepass';
2865 DROP USER someuser;
2866 ROLLBACK;
2867 # Case 15:
2868 # Reference ACL table in a foreign key. Reading ACL table while
2869 # updating the table in different isolation mode.
2870 # Make sure a concurrent ACL DML on ACL table is blocked.
2871 CREATE TABLE fk_to_acl_table
2872 (Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
2873 User char(32) COLLATE utf8_bin DEFAULT '' NOT NULL,
2874 address CHAR(255),
2875 FOREIGN KEY (Host, User) REFERENCES mysql.user(Host,User));
2876 Warnings:
2877 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2878 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
2879 ALTER TABLE fk_to_acl_table MODIFY COLUMN address char(200),
2880 ALGORITHM=COPY;
2881 SELECT User FROM mysql.user WHERE User='u2';
2882 User
2883 u2
2884 Warnings:
2885 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2886 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2887 START TRANSACTION;
2888 INSERT INTO fk_to_acl_table VALUES ('%', 'u2', '2nd cross, 1st main, downtown');
2889 # Make sure S record locks are held on ACL table.
2890 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2891 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2892 FROM performance_schema.data_locks
2893 WHERE LOCK_TYPE='RECORD' AND
2894 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2895 tab INDEX_NAME LOCK_MODE Has_lock
2896 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
2897 # Make sure that we do not allow updating ACL tables.
2898 REPLACE INTO mysql.user SELECT * FROM user_bk;
2899 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2900 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2901 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2902 DELETE FROM mysql.user WHERE User='u2';
2903 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2904 ROLLBACK;
2905 # Cleanup
2906 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
2907 DROP TABLE fk_to_acl_table;
2908 # Case 16:
2909 # Case 16.1 Update ACL table in a multi-table UPDATE command in
2910 # different isolation level. Make sure a concurrent DML on ACL table
2911 # is blocked.
2912 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
2913 SELECT User FROM mysql.user WHERE User='u2';
2914 User
2915 u2
2916 Warnings:
2917 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2918 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2919 START TRANSACTION;
2920 UPDATE mysql.user u, user_copy SET u.User='u2x' WHERE u.User LIKE 'u2';
2921 # Make sure X record locks are held on ACL table.
2922 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2923 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2924 FROM performance_schema.data_locks
2925 WHERE LOCK_TYPE='RECORD' AND
2926 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2927 tab INDEX_NAME LOCK_MODE Has_lock
2928 mysql.user PRIMARY X Acquired row lock on u2/u2x
2929 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
2930 # Make sure that we do not allow updating ACL tables.
2931 REPLACE INTO mysql.user SELECT * FROM user_bk;
2932 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2933 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2934 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2935 DELETE FROM mysql.user WHERE User='u2';
2936 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2937 ROLLBACK;
2938 # Case 16.2 Read ACL table in a multi-table UPDATE command in
2939 # different isolation level. Ideally concurrent DML on ACL table
2940 # should have been blocked. Due to Bug#31731752 we acquire
2941 # stronger lock on the table and hence acquire X clock on the
2942 # ACL table being read.
2943 SELECT User FROM mysql.user WHERE User='u2';
2944 User
2945 u2
2946 Warnings:
2947 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2948 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2949 START TRANSACTION;
2950 UPDATE mysql.user u1, user_copy u2 SET u2.User='u2x' WHERE u2.User LIKE 'u2';
2951 # Check that X record locks is held on ACL table.
2952 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2953 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2954 FROM performance_schema.data_locks
2955 WHERE LOCK_TYPE='RECORD' AND
2956 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2957 tab INDEX_NAME LOCK_MODE Has_lock
2958 mysql.user PRIMARY S Acquired row lock on u2/u2x
2959 # Make sure that we do not allow updating ACL tables.
2960 REPLACE INTO mysql.user SELECT * FROM user_bk;
2961 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2962 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2963 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2964 DELETE FROM mysql.user WHERE User='u2';
2965 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2966 ROLLBACK;
2967 # Case 17:
2968 # Case 17.1: Delete row in ACL table in a multi-table DELETE in
2969 # different isolation level. Make sure a concurrent DML on ACL
2970 # table is blocked.
2971 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
2972 SELECT User FROM mysql.user WHERE User='u2';
2973 User
2974 u2
2975 Warnings:
2976 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
2977 SELECT User FROM user_copy;
2978 User
2979 u2
2980 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2981 START TRANSACTION;
2982 DELETE u, cu FROM mysql.user u JOIN user_copy cu WHERE u.User LIKE 'u2';
2983 # Make sure X record locks are held on ACL table.
2984 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
2985 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
2986 FROM performance_schema.data_locks
2987 WHERE LOCK_TYPE='RECORD' AND
2988 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
2989 tab INDEX_NAME LOCK_MODE Has_lock
2990 mysql.user PRIMARY X Acquired row lock on u2/u2x
2991 # Make sure that we do not allow updating ACL table.
2992 REPLACE INTO mysql.user SELECT * FROM user_bk;
2993 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2994 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
2995 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2996 DELETE FROM mysql.user WHERE User='u2';
2997 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2998 ROLLBACK;
2999 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
3000 # Case 17.2 Read ACL table in a multi-table DELETE command in
3001 # different isolation level. Make sure a concurrent DML on ACL table
3002 # is not blocked.
3003 SELECT User FROM mysql.user WHERE User='u2';
3004 User
3005 u2
3006 Warnings:
3007 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3008 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3009 START TRANSACTION;
3010 DELETE u1 FROM user_copy u1 JOIN mysql.user u2 ON u2.User = u1.User;
3011 Warnings:
3012 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3013 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3014 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3015 FROM performance_schema.data_locks
3016 WHERE LOCK_TYPE='RECORD' AND
3017 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3018 tab INDEX_NAME LOCK_MODE Has_lock
3019 # Make sure no record locks are held on ACL table.
3020 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3021 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3022 FROM performance_schema.data_locks
3023 WHERE LOCK_TYPE='RECORD' AND
3024 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3025 tab INDEX_NAME LOCK_MODE Has_lock
3026 DELETE FROM mysql.user WHERE User='u2';
3027 INSERT INTO mysql.user SELECT * FROM user_bk;
3028 REPLACE INTO mysql.user SELECT * FROM user_bk;
3029 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3030 CREATE USER someuser;
3031 ALTER USER someuser IDENTIFIED BY 'somepass';
3032 DROP USER someuser;
3033 ROLLBACK;
3034 # Case 18:
3035 # Read ACL table in a DML inside a trigger in different isolation
3036 # mode. Make sure a concurrent ACL DML on same table is blocked.
3037 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3038 Warnings:
3039 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3040 CREATE TABLE tmp1 (f1 INT);
3041 CREATE TRIGGER read_trig1 BEFORE INSERT ON tmp1 FOR EACH ROW
3042 BEGIN
3043 UPDATE user_copy SET User='u2x'
3044 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
3045 END|
3046 CREATE TRIGGER update_trig1 BEFORE UPDATE ON tmp1 FOR EACH ROW
3047 BEGIN
3048 UPDATE mysql.user SET user='u2x' WHERE User='u2';
3049 END|
3050 SELECT User FROM mysql.user WHERE User='u2';
3051 User
3052 u2
3053 Warnings:
3054 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3055 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3056 START TRANSACTION;
3057 INSERT INTO tmp1 VALUES (20);
3058 Warnings:
3059 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3060 # Make sure no record locks are held on ACL table.
3061 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3062 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3063 FROM performance_schema.data_locks
3064 WHERE LOCK_TYPE='RECORD' AND
3065 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3066 tab INDEX_NAME LOCK_MODE Has_lock
3067 DELETE FROM mysql.user WHERE User='u2';
3068 INSERT INTO mysql.user SELECT * FROM user_bk;
3069 REPLACE INTO mysql.user SELECT * FROM user_bk;
3070 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3071 CREATE USER someuser;
3072 ALTER USER someuser IDENTIFIED BY 'somepass';
3073 DROP USER someuser;
3074 COMMIT;
3075 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3076 START TRANSACTION;
3077 UPDATE tmp1 SET f1=30;
3078 # Make sure X record locks are held on ACL table.
3079 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3080 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3081 FROM performance_schema.data_locks
3082 WHERE LOCK_TYPE='RECORD' AND
3083 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3084 tab INDEX_NAME LOCK_MODE Has_lock
3085 mysql.user PRIMARY X Acquired row lock on u2/u2x
3086 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
3087 # Make sure that we do not allow updating ACL table.
3088 INSERT INTO mysql.user SELECT * FROM user_bk;
3089 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3090 REPLACE INTO mysql.user SELECT * FROM user_bk;
3091 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3092 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3093 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3094 DELETE FROM mysql.user WHERE User='u2';
3095 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3096 SET SESSION lock_wait_timeout=default;
3097 SET SESSION innodb_lock_wait_timeout=default;
3098 ROLLBACK;
3099 DROP TABLE tmp1;
3100 DELETE FROM user_copy;
3101 # Case 2 (with REPEATABLE READ isolation level):
3102 # Using a separate transaction (With START TRANSACTION)
3103 # Read ACL table in SELECT in all isolation mode
3104 # and run DDL/DML on ACL tables in concurrent.
3105 SELECT User FROM mysql.user WHERE User='u2';
3106 User
3107 u2
3108 Warnings:
3109 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3110 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3111 START TRANSACTION;
3112 SELECT User FROM mysql.user WHERE User='u2';
3113 User
3114 u2
3115 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3116 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3117 FROM performance_schema.data_locks
3118 WHERE LOCK_TYPE='RECORD' AND
3119 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3120 tab INDEX_NAME LOCK_MODE Has_lock
3121 DELETE FROM mysql.user WHERE User='u2';
3122 INSERT INTO mysql.user SELECT * FROM user_bk;
3123 REPLACE INTO mysql.user SELECT * FROM user_bk;
3124 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3125 CREATE USER someuser;
3126 ALTER USER someuser IDENTIFIED BY 'somepass';
3127 DROP USER someuser;
3128 COMMIT;
3129 # Case 2.1 (with REPEATABLE READ isolation level):
3130 # Using a separate transaction (With START TRANSACTION)
3131 # Read ACL table in SELECT .. HIGH PRIORITY in all isolation mode
3132 # and run DDL/DML on ACL tables in concurrent.
3133 SELECT User FROM mysql.user WHERE User='u2';
3134 User
3135 u2
3136 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3137 START TRANSACTION;
3138 SELECT HIGH_PRIORITY User FROM mysql.user WHERE User='u2';
3139 User
3140 u2
3141 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3142 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3143 FROM performance_schema.data_locks
3144 WHERE LOCK_TYPE='RECORD' AND
3145 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3146 tab INDEX_NAME LOCK_MODE Has_lock
3147 DELETE FROM mysql.user WHERE User='u2';
3148 INSERT INTO mysql.user SELECT * FROM user_bk;
3149 REPLACE INTO mysql.user SELECT * FROM user_bk;
3150 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3151 CREATE USER someuser;
3152 ALTER USER someuser IDENTIFIED BY 'somepass';
3153 DROP USER someuser;
3154 COMMIT;
3155 # Case 3 (with REPEATABLE READ isolation level):
3156 # Using a separate transaction (With START TRANSACTION)
3157 # Read ACL table in SELECT in all isolation mode
3158 # and run DDL/DML on ACL tables in concurrent.
3159 SELECT User FROM mysql.user WHERE User='u2';
3160 User
3161 u2
3162 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3163 set autocommit=0;
3164 SELECT User FROM mysql.user WHERE User='u2';
3165 User
3166 u2
3167 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3168 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3169 FROM performance_schema.data_locks
3170 WHERE LOCK_TYPE='RECORD' AND
3171 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3172 tab INDEX_NAME LOCK_MODE Has_lock
3173 DELETE FROM mysql.user WHERE User='u2';
3174 INSERT INTO mysql.user SELECT * FROM user_bk;
3175 REPLACE INTO mysql.user SELECT * FROM user_bk;
3176 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3177 CREATE USER someuser;
3178 ALTER USER someuser IDENTIFIED BY 'somepass';
3179 DROP USER someuser;
3180 COMMIT;
3181 set autocommit=default;
3182 # Case 4 (with REPEATABLE READ isolation level):
3183 # Read ACL table in DO command in different isolation mode
3184 # and run DDL/DML on ACL tables in concurrent.
3185 SELECT User FROM mysql.user WHERE User='u2';
3186 User
3187 u2
3188 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3189 START TRANSACTION;
3190 DO (SELECT User FROM mysql.user WHERE User='u2');
3191 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3192 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3193 FROM performance_schema.data_locks
3194 WHERE LOCK_TYPE='RECORD' AND
3195 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3196 tab INDEX_NAME LOCK_MODE Has_lock
3197 DELETE FROM mysql.user WHERE User='u2';
3198 INSERT INTO mysql.user SELECT * FROM user_bk;
3199 REPLACE INTO mysql.user SELECT * FROM user_bk;
3200 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3201 CREATE USER someuser;
3202 ALTER USER someuser IDENTIFIED BY 'somepass';
3203 DROP USER someuser;
3204 COMMIT;
3205 # Case 5 (with REPEATABLE READ isolation level):
3206 # Read ACL table in SET command in different isolation mode
3207 # and run DDL/DML on ACL tables in concurrent.
3208 SELECT User FROM mysql.user WHERE User='u2';
3209 User
3210 u2
3211 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3212 START TRANSACTION;
3213 SET @u2_host = (SELECT Host FROM mysql.user WHERE User='u2');
3214 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3215 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3216 FROM performance_schema.data_locks
3217 WHERE LOCK_TYPE='RECORD' AND
3218 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3219 tab INDEX_NAME LOCK_MODE Has_lock
3220 DELETE FROM mysql.user WHERE User='u2';
3221 INSERT INTO mysql.user SELECT * FROM user_bk;
3222 REPLACE INTO mysql.user SELECT * FROM user_bk;
3223 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3224 CREATE USER someuser;
3225 ALTER USER someuser IDENTIFIED BY 'somepass';
3226 DROP USER someuser;
3227 COMMIT;
3228 # Case 6:
3229 # Read ACL table in INSERT ... SELECT command in different
3230 # isolation mode and run DDL/DML on ACL tables in concurrent.
3231 SELECT User FROM mysql.user WHERE User='u2';
3232 User
3233 u2
3234 Warnings:
3235 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
3236 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3237 START TRANSACTION;
3238 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3239 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3240 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3241 FROM performance_schema.data_locks
3242 WHERE LOCK_TYPE='RECORD' AND
3243 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3244 tab INDEX_NAME LOCK_MODE Has_lock
3245 DELETE FROM mysql.user WHERE User='u2';
3246 INSERT INTO mysql.user SELECT * FROM user_bk;
3247 REPLACE INTO mysql.user SELECT * FROM user_bk;
3248 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3249 CREATE USER someuser;
3250 ALTER USER someuser IDENTIFIED BY 'somepass';
3251 DROP USER someuser;
3252 COMMIT;
3253 # Case 7:
3254 # Read ACL table in UPDATE ... WHERE SELECT in different
3255 # isolation mode and run DDL/DML on ACL tables in concurrent.
3256 SELECT User FROM mysql.user WHERE User='u2';
3257 User
3258 u2
3259 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3260 START TRANSACTION;
3261 UPDATE user_copy SET User='u2x'
3262 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
3263 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3264 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3265 FROM performance_schema.data_locks
3266 WHERE LOCK_TYPE='RECORD' AND
3267 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3268 tab INDEX_NAME LOCK_MODE Has_lock
3269 DELETE FROM mysql.user WHERE User='u2';
3270 INSERT INTO mysql.user SELECT * FROM user_bk;
3271 REPLACE INTO mysql.user SELECT * FROM user_bk;
3272 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3273 CREATE USER someuser;
3274 ALTER USER someuser IDENTIFIED BY 'somepass';
3275 DROP USER someuser;
3276 COMMIT;
3277 # Case 8:
3278 # Read ACL table in Multi-table UPDATE ... SELECT in different
3279 # isolation mode and run DDL/DML on ACL tables in concurrent.
3280 SELECT User FROM mysql.user WHERE User='u2';
3281 User
3282 u2
3283 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3284 START TRANSACTION;
3285 UPDATE t1, user_copy SET user_copy.Host='instance1'
3286 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3287 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3288 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3289 FROM performance_schema.data_locks
3290 WHERE LOCK_TYPE='RECORD' AND
3291 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3292 tab INDEX_NAME LOCK_MODE Has_lock
3293 DELETE FROM mysql.user WHERE User='u2';
3294 INSERT INTO mysql.user SELECT * FROM user_bk;
3295 REPLACE INTO mysql.user SELECT * FROM user_bk;
3296 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3297 CREATE USER someuser;
3298 ALTER USER someuser IDENTIFIED BY 'somepass';
3299 DROP USER someuser;
3300 COMMIT;
3301 # Case 9:
3302 # Read ACL table in DELETE ... WHERE SELECT in different
3303 # isolation mode and run DDL/DML on ACL tables in concurrent.
3304 SELECT User FROM mysql.user WHERE User='u2';
3305 User
3306 u2
3307 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3308 START TRANSACTION;
3309 DELETE FROM user_copy
3310 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3311 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3312 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3313 FROM performance_schema.data_locks
3314 WHERE LOCK_TYPE='RECORD' AND
3315 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3316 tab INDEX_NAME LOCK_MODE Has_lock
3317 DELETE FROM mysql.user WHERE User='u2';
3318 INSERT INTO mysql.user SELECT * FROM user_bk;
3319 REPLACE INTO mysql.user SELECT * FROM user_bk;
3320 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3321 CREATE USER someuser;
3322 ALTER USER someuser IDENTIFIED BY 'somepass';
3323 DROP USER someuser;
3324 COMMIT;
3325 # Case 10:
3326 # Read ACL table in Multi-table DELETE ... WHERE SELECT in different
3327 # isolation mode and run DDL/DML on ACL tables in concurrent.
3328 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3329 SELECT User FROM mysql.user WHERE User='u2';
3330 User
3331 u2
3332 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3333 START TRANSACTION;
3334 DELETE user_copy FROM user_copy JOIN (SELECT * FROM user_copy) tuser_copy
3335 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3336 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3337 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3338 FROM performance_schema.data_locks
3339 WHERE LOCK_TYPE='RECORD' AND
3340 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3341 tab INDEX_NAME LOCK_MODE Has_lock
3342 DELETE FROM mysql.user WHERE User='u2';
3343 INSERT INTO mysql.user SELECT * FROM user_bk;
3344 REPLACE INTO mysql.user SELECT * FROM user_bk;
3345 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3346 CREATE USER someuser;
3347 ALTER USER someuser IDENTIFIED BY 'somepass';
3348 DROP USER someuser;
3349 COMMIT;
3350 # Case 11:
3351 # Read ACL table in CREATE TABLE ... SELECT in different
3352 # isolation mode and run DDL/DML on ACL tables in concurrent.
3353 SELECT User FROM mysql.user WHERE User='u2';
3354 User
3355 u2
3356 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3357 SET SESSION DEBUG_SYNC='create_select_after_write_rows_event SIGNAL parked WAIT_FOR go';
3358 CREATE TABLE tmp AS SELECT * FROM mysql.user WHERE User='u2';
3359 SET DEBUG_SYNC= 'now WAIT_FOR parked';
3360 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3361 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3362 FROM performance_schema.data_locks
3363 WHERE LOCK_TYPE='RECORD' AND
3364 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3365 tab INDEX_NAME LOCK_MODE Has_lock
3366 DELETE FROM mysql.user WHERE User='u2';
3367 INSERT INTO mysql.user SELECT * FROM user_bk;
3368 REPLACE INTO mysql.user SELECT * FROM user_bk;
3369 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3370 CREATE USER someuser;
3371 ALTER USER someuser IDENTIFIED BY 'somepass';
3372 DROP USER someuser;
3373 SET DEBUG_SYNC= 'now SIGNAL go';
3374 SET SESSION DEBUG_SYNC=RESET;
3375 COMMIT;
3376 SET SESSION DEBUG_SYNC=RESET;
3377 DROP TABLE tmp;
3378 # Case 12:
3379 # Read ACL table in LOCK TABLE for READ mode and
3380 # Make sure a concurrent ACL DML on same table is blocked.
3381 SELECT User FROM mysql.user WHERE User='u2';
3382 User
3383 u2
3384 LOCK TABLE mysql.user READ;
3385 SET SESSION lock_wait_timeout=1;
3386 SET SESSION innodb_lock_wait_timeout=1;
3387 # Make sure that we do not allow updating ACL table.
3388 INSERT INTO mysql.user SELECT * FROM user_bk;
3389 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3390 REPLACE INTO mysql.user SELECT * FROM user_bk;
3391 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3392 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3393 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3394 DELETE FROM mysql.user WHERE User='u2';
3395 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3396 UNLOCK TABLES;
3397 # Case 13:
3398 # Run FTWRL READ mode and
3399 # make sure a concurrent ACL DML on same table is blocked.
3400 SELECT User FROM mysql.user WHERE User='u2';
3401 User
3402 u2
3403 FLUSH TABLES WITH READ LOCK;
3404 INSERT INTO mysql.user SELECT * FROM user_bk;
3405 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3406 REPLACE INTO mysql.user SELECT * FROM user_bk;
3407 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3408 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3409 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3410 DELETE FROM mysql.user WHERE User='u2';
3411 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3412 UNLOCK TABLES;
3413 # Case 14:
3414 # Update ACL table in a DML inside a SP in different isolation mode.
3415 # Make sure a concurrent ACL DML on same table is blocked.
3416 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3417 SELECT User FROM mysql.user WHERE User='u2';
3418 User
3419 u2
3420 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3421 START TRANSACTION;
3422 CALL proc2();
3423 # Make sure X record locks are held on ACL table.
3424 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3425 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3426 FROM performance_schema.data_locks
3427 WHERE LOCK_TYPE='RECORD' AND
3428 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3429 tab INDEX_NAME LOCK_MODE Has_lock
3430 mysql.user PRIMARY X Acquired row lock on u2/u2x
3431 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
3432 # Make sure that we do not allow updating ACL table.
3433 INSERT INTO mysql.user SELECT * FROM user_bk;
3434 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3435 REPLACE INTO mysql.user SELECT * FROM user_bk;
3436 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3437 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3438 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3439 DELETE FROM mysql.user WHERE User='u2';
3440 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3441 ROLLBACK;
3442 SELECT User FROM mysql.user WHERE User='u2';
3443 User
3444 u2
3445 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3446 START TRANSACTION;
3447 SELECT func1();
3448 func1()
3449 0
3450 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3451 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3452 FROM performance_schema.data_locks
3453 WHERE LOCK_TYPE='RECORD' AND
3454 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3455 tab INDEX_NAME LOCK_MODE Has_lock
3456 DELETE FROM mysql.user WHERE User='u2';
3457 INSERT INTO mysql.user SELECT * FROM user_bk;
3458 REPLACE INTO mysql.user SELECT * FROM user_bk;
3459 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3460 CREATE USER someuser;
3461 ALTER USER someuser IDENTIFIED BY 'somepass';
3462 DROP USER someuser;
3463 ROLLBACK;
3464 # Case 15:
3465 # Reference ACL table in a foreign key. Reading ACL table while
3466 # updating the table in different isolation mode.
3467 # Make sure a concurrent ACL DML on ACL table is blocked.
3468 CREATE TABLE fk_to_acl_table
3469 (Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
3470 User char(32) COLLATE utf8_bin DEFAULT '' NOT NULL,
3471 address CHAR(255),
3472 FOREIGN KEY (Host, User) REFERENCES mysql.user(Host,User));
3473 Warnings:
3474 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
3475 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
3476 ALTER TABLE fk_to_acl_table MODIFY COLUMN address char(200),
3477 ALGORITHM=COPY;
3478 SELECT User FROM mysql.user WHERE User='u2';
3479 User
3480 u2
3481 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3482 START TRANSACTION;
3483 INSERT INTO fk_to_acl_table VALUES ('%', 'u2', '2nd cross, 1st main, downtown');
3484 # Make sure S record locks are held on ACL table.
3485 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3486 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3487 FROM performance_schema.data_locks
3488 WHERE LOCK_TYPE='RECORD' AND
3489 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3490 tab INDEX_NAME LOCK_MODE Has_lock
3491 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
3492 # Make sure that we do not allow updating ACL tables.
3493 REPLACE INTO mysql.user SELECT * FROM user_bk;
3494 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3495 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3496 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3497 DELETE FROM mysql.user WHERE User='u2';
3498 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3499 ROLLBACK;
3500 # Cleanup
3501 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
3502 DROP TABLE fk_to_acl_table;
3503 # Case 16:
3504 # Case 16.1 Update ACL table in a multi-table UPDATE command in
3505 # different isolation level. Make sure a concurrent DML on ACL table
3506 # is blocked.
3507 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
3508 SELECT User FROM mysql.user WHERE User='u2';
3509 User
3510 u2
3511 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3512 START TRANSACTION;
3513 UPDATE mysql.user u, user_copy SET u.User='u2x' WHERE u.User LIKE 'u2';
3514 # Make sure X record locks are held on ACL table.
3515 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3516 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3517 FROM performance_schema.data_locks
3518 WHERE LOCK_TYPE='RECORD' AND
3519 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3520 tab INDEX_NAME LOCK_MODE Has_lock
3521 mysql.user PRIMARY X Acquired row lock on u2/u2x
3522 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
3523 # Make sure that we do not allow updating ACL tables.
3524 REPLACE INTO mysql.user SELECT * FROM user_bk;
3525 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3526 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3527 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3528 DELETE FROM mysql.user WHERE User='u2';
3529 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3530 ROLLBACK;
3531 # Case 16.2 Read ACL table in a multi-table UPDATE command in
3532 # different isolation level. Ideally concurrent DML on ACL table
3533 # should have been blocked. Due to Bug#31731752 we acquire
3534 # stronger lock on the table and hence acquire X clock on the
3535 # ACL table being read.
3536 SELECT User FROM mysql.user WHERE User='u2';
3537 User
3538 u2
3539 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3540 START TRANSACTION;
3541 UPDATE mysql.user u1, user_copy u2 SET u2.User='u2x' WHERE u2.User LIKE 'u2';
3542 # Check that X record locks is held on ACL table.
3543 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3544 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3545 FROM performance_schema.data_locks
3546 WHERE LOCK_TYPE='RECORD' AND
3547 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3548 tab INDEX_NAME LOCK_MODE Has_lock
3549 mysql.user PRIMARY S Acquired row lock on u2/u2x
3550 # Make sure that we do not allow updating ACL tables.
3551 REPLACE INTO mysql.user SELECT * FROM user_bk;
3552 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3553 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3554 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3555 DELETE FROM mysql.user WHERE User='u2';
3556 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3557 ROLLBACK;
3558 # Case 17:
3559 # Case 17.1: Delete row in ACL table in a multi-table DELETE in
3560 # different isolation level. Make sure a concurrent DML on ACL
3561 # table is blocked.
3562 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
3563 SELECT User FROM mysql.user WHERE User='u2';
3564 User
3565 u2
3566 SELECT User FROM user_copy;
3567 User
3568 u2
3569 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3570 START TRANSACTION;
3571 DELETE u, cu FROM mysql.user u JOIN user_copy cu WHERE u.User LIKE 'u2';
3572 # Make sure X record locks are held on ACL table.
3573 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3574 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3575 FROM performance_schema.data_locks
3576 WHERE LOCK_TYPE='RECORD' AND
3577 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3578 tab INDEX_NAME LOCK_MODE Has_lock
3579 mysql.user PRIMARY X Acquired row lock on u2/u2x
3580 # Make sure that we do not allow updating ACL table.
3581 REPLACE INTO mysql.user SELECT * FROM user_bk;
3582 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3583 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3584 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3585 DELETE FROM mysql.user WHERE User='u2';
3586 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3587 ROLLBACK;
3588 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
3589 # Case 17.2 Read ACL table in a multi-table DELETE command in
3590 # different isolation level. Make sure a concurrent DML on ACL table
3591 # is not blocked.
3592 SELECT User FROM mysql.user WHERE User='u2';
3593 User
3594 u2
3595 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3596 START TRANSACTION;
3597 DELETE u1 FROM user_copy u1 JOIN mysql.user u2 ON u2.User = u1.User;
3598 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3599 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3600 FROM performance_schema.data_locks
3601 WHERE LOCK_TYPE='RECORD' AND
3602 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3603 tab INDEX_NAME LOCK_MODE Has_lock
3604 # Make sure no record locks are held on ACL table.
3605 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3606 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3607 FROM performance_schema.data_locks
3608 WHERE LOCK_TYPE='RECORD' AND
3609 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3610 tab INDEX_NAME LOCK_MODE Has_lock
3611 DELETE FROM mysql.user WHERE User='u2';
3612 INSERT INTO mysql.user SELECT * FROM user_bk;
3613 REPLACE INTO mysql.user SELECT * FROM user_bk;
3614 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3615 CREATE USER someuser;
3616 ALTER USER someuser IDENTIFIED BY 'somepass';
3617 DROP USER someuser;
3618 ROLLBACK;
3619 # Case 18:
3620 # Read ACL table in a DML inside a trigger in different isolation
3621 # mode. Make sure a concurrent ACL DML on same table is blocked.
3622 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3623 CREATE TABLE tmp1 (f1 INT);
3624 CREATE TRIGGER read_trig1 BEFORE INSERT ON tmp1 FOR EACH ROW
3625 BEGIN
3626 UPDATE user_copy SET User='u2x'
3627 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
3628 END|
3629 CREATE TRIGGER update_trig1 BEFORE UPDATE ON tmp1 FOR EACH ROW
3630 BEGIN
3631 UPDATE mysql.user SET user='u2x' WHERE User='u2';
3632 END|
3633 SELECT User FROM mysql.user WHERE User='u2';
3634 User
3635 u2
3636 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3637 START TRANSACTION;
3638 INSERT INTO tmp1 VALUES (20);
3639 # Make sure no record locks are held on ACL table.
3640 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3641 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3642 FROM performance_schema.data_locks
3643 WHERE LOCK_TYPE='RECORD' AND
3644 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3645 tab INDEX_NAME LOCK_MODE Has_lock
3646 DELETE FROM mysql.user WHERE User='u2';
3647 INSERT INTO mysql.user SELECT * FROM user_bk;
3648 REPLACE INTO mysql.user SELECT * FROM user_bk;
3649 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3650 CREATE USER someuser;
3651 ALTER USER someuser IDENTIFIED BY 'somepass';
3652 DROP USER someuser;
3653 COMMIT;
3654 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3655 START TRANSACTION;
3656 UPDATE tmp1 SET f1=30;
3657 # Make sure X record locks are held on ACL table.
3658 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3659 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3660 FROM performance_schema.data_locks
3661 WHERE LOCK_TYPE='RECORD' AND
3662 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3663 tab INDEX_NAME LOCK_MODE Has_lock
3664 mysql.user PRIMARY X Acquired row lock on u2/u2x
3665 mysql.user PRIMARY X,GAP Acquired row lock on u2/u2x
3666 # Make sure that we do not allow updating ACL table.
3667 INSERT INTO mysql.user SELECT * FROM user_bk;
3668 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3669 REPLACE INTO mysql.user SELECT * FROM user_bk;
3670 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3671 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3672 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3673 DELETE FROM mysql.user WHERE User='u2';
3674 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3675 SET SESSION lock_wait_timeout=default;
3676 SET SESSION innodb_lock_wait_timeout=default;
3677 ROLLBACK;
3678 DROP TABLE tmp1;
3679 DELETE FROM user_copy;
3680 # Case 2 (with READ COMMITTED isolation level):
3681 # Using a separate transaction (With START TRANSACTION)
3682 # Read ACL table in SELECT in all isolation mode
3683 # and run DDL/DML on ACL tables in concurrent.
3684 SELECT User FROM mysql.user WHERE User='u2';
3685 User
3686 u2
3687 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3688 START TRANSACTION;
3689 SELECT User FROM mysql.user WHERE User='u2';
3690 User
3691 u2
3692 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3693 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3694 FROM performance_schema.data_locks
3695 WHERE LOCK_TYPE='RECORD' AND
3696 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3697 tab INDEX_NAME LOCK_MODE Has_lock
3698 DELETE FROM mysql.user WHERE User='u2';
3699 INSERT INTO mysql.user SELECT * FROM user_bk;
3700 REPLACE INTO mysql.user SELECT * FROM user_bk;
3701 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3702 CREATE USER someuser;
3703 ALTER USER someuser IDENTIFIED BY 'somepass';
3704 DROP USER someuser;
3705 COMMIT;
3706 # Case 2.1 (with READ COMMITTED isolation level):
3707 # Using a separate transaction (With START TRANSACTION)
3708 # Read ACL table in SELECT .. HIGH PRIORITY in all isolation mode
3709 # and run DDL/DML on ACL tables in concurrent.
3710 SELECT User FROM mysql.user WHERE User='u2';
3711 User
3712 u2
3713 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3714 START TRANSACTION;
3715 SELECT HIGH_PRIORITY User FROM mysql.user WHERE User='u2';
3716 User
3717 u2
3718 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3719 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3720 FROM performance_schema.data_locks
3721 WHERE LOCK_TYPE='RECORD' AND
3722 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3723 tab INDEX_NAME LOCK_MODE Has_lock
3724 DELETE FROM mysql.user WHERE User='u2';
3725 INSERT INTO mysql.user SELECT * FROM user_bk;
3726 REPLACE INTO mysql.user SELECT * FROM user_bk;
3727 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3728 CREATE USER someuser;
3729 ALTER USER someuser IDENTIFIED BY 'somepass';
3730 DROP USER someuser;
3731 COMMIT;
3732 # Case 3 (with READ COMMITTED isolation level):
3733 # Using a separate transaction (With START TRANSACTION)
3734 # Read ACL table in SELECT in all isolation mode
3735 # and run DDL/DML on ACL tables in concurrent.
3736 SELECT User FROM mysql.user WHERE User='u2';
3737 User
3738 u2
3739 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3740 set autocommit=0;
3741 SELECT User FROM mysql.user WHERE User='u2';
3742 User
3743 u2
3744 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3745 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3746 FROM performance_schema.data_locks
3747 WHERE LOCK_TYPE='RECORD' AND
3748 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3749 tab INDEX_NAME LOCK_MODE Has_lock
3750 DELETE FROM mysql.user WHERE User='u2';
3751 INSERT INTO mysql.user SELECT * FROM user_bk;
3752 REPLACE INTO mysql.user SELECT * FROM user_bk;
3753 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3754 CREATE USER someuser;
3755 ALTER USER someuser IDENTIFIED BY 'somepass';
3756 DROP USER someuser;
3757 COMMIT;
3758 set autocommit=default;
3759 # Case 4 (with READ COMMITTED isolation level):
3760 # Read ACL table in DO command in different isolation mode
3761 # and run DDL/DML on ACL tables in concurrent.
3762 SELECT User FROM mysql.user WHERE User='u2';
3763 User
3764 u2
3765 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3766 START TRANSACTION;
3767 DO (SELECT User FROM mysql.user WHERE User='u2');
3768 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3769 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3770 FROM performance_schema.data_locks
3771 WHERE LOCK_TYPE='RECORD' AND
3772 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3773 tab INDEX_NAME LOCK_MODE Has_lock
3774 DELETE FROM mysql.user WHERE User='u2';
3775 INSERT INTO mysql.user SELECT * FROM user_bk;
3776 REPLACE INTO mysql.user SELECT * FROM user_bk;
3777 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3778 CREATE USER someuser;
3779 ALTER USER someuser IDENTIFIED BY 'somepass';
3780 DROP USER someuser;
3781 COMMIT;
3782 # Case 5 (with READ COMMITTED isolation level):
3783 # Read ACL table in SET command in different isolation mode
3784 # and run DDL/DML on ACL tables in concurrent.
3785 SELECT User FROM mysql.user WHERE User='u2';
3786 User
3787 u2
3788 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3789 START TRANSACTION;
3790 SET @u2_host = (SELECT Host FROM mysql.user WHERE User='u2');
3791 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3792 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3793 FROM performance_schema.data_locks
3794 WHERE LOCK_TYPE='RECORD' AND
3795 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3796 tab INDEX_NAME LOCK_MODE Has_lock
3797 DELETE FROM mysql.user WHERE User='u2';
3798 INSERT INTO mysql.user SELECT * FROM user_bk;
3799 REPLACE INTO mysql.user SELECT * FROM user_bk;
3800 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3801 CREATE USER someuser;
3802 ALTER USER someuser IDENTIFIED BY 'somepass';
3803 DROP USER someuser;
3804 COMMIT;
3805 # Case 6:
3806 # Read ACL table in INSERT ... SELECT command in different
3807 # isolation mode and run DDL/DML on ACL tables in concurrent.
3808 SELECT User FROM mysql.user WHERE User='u2';
3809 User
3810 u2
3811 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3812 START TRANSACTION;
3813 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3814 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3815 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3816 FROM performance_schema.data_locks
3817 WHERE LOCK_TYPE='RECORD' AND
3818 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3819 tab INDEX_NAME LOCK_MODE Has_lock
3820 DELETE FROM mysql.user WHERE User='u2';
3821 INSERT INTO mysql.user SELECT * FROM user_bk;
3822 REPLACE INTO mysql.user SELECT * FROM user_bk;
3823 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3824 CREATE USER someuser;
3825 ALTER USER someuser IDENTIFIED BY 'somepass';
3826 DROP USER someuser;
3827 COMMIT;
3828 # Case 7:
3829 # Read ACL table in UPDATE ... WHERE SELECT in different
3830 # isolation mode and run DDL/DML on ACL tables in concurrent.
3831 SELECT User FROM mysql.user WHERE User='u2';
3832 User
3833 u2
3834 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3835 START TRANSACTION;
3836 UPDATE user_copy SET User='u2x'
3837 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
3838 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3839 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3840 FROM performance_schema.data_locks
3841 WHERE LOCK_TYPE='RECORD' AND
3842 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3843 tab INDEX_NAME LOCK_MODE Has_lock
3844 DELETE FROM mysql.user WHERE User='u2';
3845 INSERT INTO mysql.user SELECT * FROM user_bk;
3846 REPLACE INTO mysql.user SELECT * FROM user_bk;
3847 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3848 CREATE USER someuser;
3849 ALTER USER someuser IDENTIFIED BY 'somepass';
3850 DROP USER someuser;
3851 COMMIT;
3852 # Case 8:
3853 # Read ACL table in Multi-table UPDATE ... SELECT in different
3854 # isolation mode and run DDL/DML on ACL tables in concurrent.
3855 SELECT User FROM mysql.user WHERE User='u2';
3856 User
3857 u2
3858 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3859 START TRANSACTION;
3860 UPDATE t1, user_copy SET user_copy.Host='instance1'
3861 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3862 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3863 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3864 FROM performance_schema.data_locks
3865 WHERE LOCK_TYPE='RECORD' AND
3866 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3867 tab INDEX_NAME LOCK_MODE Has_lock
3868 DELETE FROM mysql.user WHERE User='u2';
3869 INSERT INTO mysql.user SELECT * FROM user_bk;
3870 REPLACE INTO mysql.user SELECT * FROM user_bk;
3871 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3872 CREATE USER someuser;
3873 ALTER USER someuser IDENTIFIED BY 'somepass';
3874 DROP USER someuser;
3875 COMMIT;
3876 # Case 9:
3877 # Read ACL table in DELETE ... WHERE SELECT in different
3878 # isolation mode and run DDL/DML on ACL tables in concurrent.
3879 SELECT User FROM mysql.user WHERE User='u2';
3880 User
3881 u2
3882 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3883 START TRANSACTION;
3884 DELETE FROM user_copy
3885 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3886 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3887 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3888 FROM performance_schema.data_locks
3889 WHERE LOCK_TYPE='RECORD' AND
3890 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3891 tab INDEX_NAME LOCK_MODE Has_lock
3892 DELETE FROM mysql.user WHERE User='u2';
3893 INSERT INTO mysql.user SELECT * FROM user_bk;
3894 REPLACE INTO mysql.user SELECT * FROM user_bk;
3895 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3896 CREATE USER someuser;
3897 ALTER USER someuser IDENTIFIED BY 'somepass';
3898 DROP USER someuser;
3899 COMMIT;
3900 # Case 10:
3901 # Read ACL table in Multi-table DELETE ... WHERE SELECT in different
3902 # isolation mode and run DDL/DML on ACL tables in concurrent.
3903 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3904 SELECT User FROM mysql.user WHERE User='u2';
3905 User
3906 u2
3907 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3908 START TRANSACTION;
3909 DELETE user_copy FROM user_copy JOIN (SELECT * FROM user_copy) tuser_copy
3910 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
3911 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3912 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3913 FROM performance_schema.data_locks
3914 WHERE LOCK_TYPE='RECORD' AND
3915 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3916 tab INDEX_NAME LOCK_MODE Has_lock
3917 DELETE FROM mysql.user WHERE User='u2';
3918 INSERT INTO mysql.user SELECT * FROM user_bk;
3919 REPLACE INTO mysql.user SELECT * FROM user_bk;
3920 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3921 CREATE USER someuser;
3922 ALTER USER someuser IDENTIFIED BY 'somepass';
3923 DROP USER someuser;
3924 COMMIT;
3925 # Case 11:
3926 # Read ACL table in CREATE TABLE ... SELECT in different
3927 # isolation mode and run DDL/DML on ACL tables in concurrent.
3928 SELECT User FROM mysql.user WHERE User='u2';
3929 User
3930 u2
3931 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3932 SET SESSION DEBUG_SYNC='create_select_after_write_rows_event SIGNAL parked WAIT_FOR go';
3933 CREATE TABLE tmp AS SELECT * FROM mysql.user WHERE User='u2';
3934 SET DEBUG_SYNC= 'now WAIT_FOR parked';
3935 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
3936 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
3937 FROM performance_schema.data_locks
3938 WHERE LOCK_TYPE='RECORD' AND
3939 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
3940 tab INDEX_NAME LOCK_MODE Has_lock
3941 DELETE FROM mysql.user WHERE User='u2';
3942 INSERT INTO mysql.user SELECT * FROM user_bk;
3943 REPLACE INTO mysql.user SELECT * FROM user_bk;
3944 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
3945 CREATE USER someuser;
3946 ALTER USER someuser IDENTIFIED BY 'somepass';
3947 DROP USER someuser;
3948 SET DEBUG_SYNC= 'now SIGNAL go';
3949 SET SESSION DEBUG_SYNC=RESET;
3950 COMMIT;
3951 SET SESSION DEBUG_SYNC=RESET;
3952 DROP TABLE tmp;
3953 # Case 12:
3954 # Read ACL table in LOCK TABLE for READ mode and
3955 # Make sure a concurrent ACL DML on same table is blocked.
3956 SELECT User FROM mysql.user WHERE User='u2';
3957 User
3958 u2
3959 LOCK TABLE mysql.user READ;
3960 SET SESSION lock_wait_timeout=1;
3961 SET SESSION innodb_lock_wait_timeout=1;
3962 # Make sure that we do not allow updating ACL table.
3963 INSERT INTO mysql.user SELECT * FROM user_bk;
3964 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3965 REPLACE INTO mysql.user SELECT * FROM user_bk;
3966 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3967 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3968 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3969 DELETE FROM mysql.user WHERE User='u2';
3970 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3971 UNLOCK TABLES;
3972 # Case 13:
3973 # Run FTWRL READ mode and
3974 # make sure a concurrent ACL DML on same table is blocked.
3975 SELECT User FROM mysql.user WHERE User='u2';
3976 User
3977 u2
3978 FLUSH TABLES WITH READ LOCK;
3979 INSERT INTO mysql.user SELECT * FROM user_bk;
3980 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3981 REPLACE INTO mysql.user SELECT * FROM user_bk;
3982 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3983 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
3984 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3985 DELETE FROM mysql.user WHERE User='u2';
3986 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
3987 UNLOCK TABLES;
3988 # Case 14:
3989 # Update ACL table in a DML inside a SP in different isolation mode.
3990 # Make sure a concurrent ACL DML on same table is blocked.
3991 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
3992 SELECT User FROM mysql.user WHERE User='u2';
3993 User
3994 u2
3995 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3996 START TRANSACTION;
3997 CALL proc2();
3998 # Make sure X record locks are held on ACL table.
3999 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4000 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4001 FROM performance_schema.data_locks
4002 WHERE LOCK_TYPE='RECORD' AND
4003 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4004 tab INDEX_NAME LOCK_MODE Has_lock
4005 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4006 # Make sure that we do not allow updating ACL table.
4007 INSERT INTO mysql.user SELECT * FROM user_bk;
4008 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4009 REPLACE INTO mysql.user SELECT * FROM user_bk;
4010 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4011 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4012 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4013 DELETE FROM mysql.user WHERE User='u2';
4014 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4015 ROLLBACK;
4016 SELECT User FROM mysql.user WHERE User='u2';
4017 User
4018 u2
4019 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4020 START TRANSACTION;
4021 SELECT func1();
4022 func1()
4023 0
4024 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4025 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4026 FROM performance_schema.data_locks
4027 WHERE LOCK_TYPE='RECORD' AND
4028 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4029 tab INDEX_NAME LOCK_MODE Has_lock
4030 DELETE FROM mysql.user WHERE User='u2';
4031 INSERT INTO mysql.user SELECT * FROM user_bk;
4032 REPLACE INTO mysql.user SELECT * FROM user_bk;
4033 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4034 CREATE USER someuser;
4035 ALTER USER someuser IDENTIFIED BY 'somepass';
4036 DROP USER someuser;
4037 ROLLBACK;
4038 # Case 15:
4039 # Reference ACL table in a foreign key. Reading ACL table while
4040 # updating the table in different isolation mode.
4041 # Make sure a concurrent ACL DML on ACL table is blocked.
4042 CREATE TABLE fk_to_acl_table
4043 (Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
4044 User char(32) COLLATE utf8_bin DEFAULT '' NOT NULL,
4045 address CHAR(255),
4046 FOREIGN KEY (Host, User) REFERENCES mysql.user(Host,User));
4047 Warnings:
4048 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
4049 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4050 ALTER TABLE fk_to_acl_table MODIFY COLUMN address char(200),
4051 ALGORITHM=COPY;
4052 SELECT User FROM mysql.user WHERE User='u2';
4053 User
4054 u2
4055 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4056 START TRANSACTION;
4057 INSERT INTO fk_to_acl_table VALUES ('%', 'u2', '2nd cross, 1st main, downtown');
4058 # Make sure S record locks are held on ACL table.
4059 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4060 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4061 FROM performance_schema.data_locks
4062 WHERE LOCK_TYPE='RECORD' AND
4063 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4064 tab INDEX_NAME LOCK_MODE Has_lock
4065 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
4066 # Make sure that we do not allow updating ACL tables.
4067 REPLACE INTO mysql.user SELECT * FROM user_bk;
4068 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4069 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4070 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4071 DELETE FROM mysql.user WHERE User='u2';
4072 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4073 ROLLBACK;
4074 # Cleanup
4075 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
4076 DROP TABLE fk_to_acl_table;
4077 # Case 16:
4078 # Case 16.1 Update ACL table in a multi-table UPDATE command in
4079 # different isolation level. Make sure a concurrent DML on ACL table
4080 # is blocked.
4081 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4082 SELECT User FROM mysql.user WHERE User='u2';
4083 User
4084 u2
4085 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4086 START TRANSACTION;
4087 UPDATE mysql.user u, user_copy SET u.User='u2x' WHERE u.User LIKE 'u2';
4088 # Make sure X record locks are held on ACL table.
4089 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4090 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4091 FROM performance_schema.data_locks
4092 WHERE LOCK_TYPE='RECORD' AND
4093 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4094 tab INDEX_NAME LOCK_MODE Has_lock
4095 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4096 # Make sure that we do not allow updating ACL tables.
4097 REPLACE INTO mysql.user SELECT * FROM user_bk;
4098 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4099 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4100 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4101 DELETE FROM mysql.user WHERE User='u2';
4102 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4103 ROLLBACK;
4104 # Case 16.2 Read ACL table in a multi-table UPDATE command in
4105 # different isolation level. Ideally concurrent DML on ACL table
4106 # should have been blocked. Due to Bug#31731752 we acquire
4107 # stronger lock on the table and hence acquire X clock on the
4108 # ACL table being read.
4109 SELECT User FROM mysql.user WHERE User='u2';
4110 User
4111 u2
4112 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4113 START TRANSACTION;
4114 UPDATE mysql.user u1, user_copy u2 SET u2.User='u2x' WHERE u2.User LIKE 'u2';
4115 # Check that X record locks is held on ACL table.
4116 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4117 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4118 FROM performance_schema.data_locks
4119 WHERE LOCK_TYPE='RECORD' AND
4120 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4121 tab INDEX_NAME LOCK_MODE Has_lock
4122 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
4123 # Make sure that we do not allow updating ACL tables.
4124 REPLACE INTO mysql.user SELECT * FROM user_bk;
4125 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4126 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4127 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4128 DELETE FROM mysql.user WHERE User='u2';
4129 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4130 ROLLBACK;
4131 # Case 17:
4132 # Case 17.1: Delete row in ACL table in a multi-table DELETE in
4133 # different isolation level. Make sure a concurrent DML on ACL
4134 # table is blocked.
4135 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4136 SELECT User FROM mysql.user WHERE User='u2';
4137 User
4138 u2
4139 SELECT User FROM user_copy;
4140 User
4141 u2
4142 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4143 START TRANSACTION;
4144 DELETE u, cu FROM mysql.user u JOIN user_copy cu WHERE u.User LIKE 'u2';
4145 # Make sure X record locks are held on ACL table.
4146 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4147 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4148 FROM performance_schema.data_locks
4149 WHERE LOCK_TYPE='RECORD' AND
4150 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4151 tab INDEX_NAME LOCK_MODE Has_lock
4152 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4153 # Make sure that we do not allow updating ACL table.
4154 REPLACE INTO mysql.user SELECT * FROM user_bk;
4155 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4156 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4157 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4158 DELETE FROM mysql.user WHERE User='u2';
4159 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4160 ROLLBACK;
4161 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
4162 # Case 17.2 Read ACL table in a multi-table DELETE command in
4163 # different isolation level. Make sure a concurrent DML on ACL table
4164 # is not blocked.
4165 SELECT User FROM mysql.user WHERE User='u2';
4166 User
4167 u2
4168 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4169 START TRANSACTION;
4170 DELETE u1 FROM user_copy u1 JOIN mysql.user u2 ON u2.User = u1.User;
4171 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4172 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4173 FROM performance_schema.data_locks
4174 WHERE LOCK_TYPE='RECORD' AND
4175 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4176 tab INDEX_NAME LOCK_MODE Has_lock
4177 # Make sure no record locks are held on ACL table.
4178 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4179 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4180 FROM performance_schema.data_locks
4181 WHERE LOCK_TYPE='RECORD' AND
4182 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4183 tab INDEX_NAME LOCK_MODE Has_lock
4184 DELETE FROM mysql.user WHERE User='u2';
4185 INSERT INTO mysql.user SELECT * FROM user_bk;
4186 REPLACE INTO mysql.user SELECT * FROM user_bk;
4187 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4188 CREATE USER someuser;
4189 ALTER USER someuser IDENTIFIED BY 'somepass';
4190 DROP USER someuser;
4191 ROLLBACK;
4192 # Case 18:
4193 # Read ACL table in a DML inside a trigger in different isolation
4194 # mode. Make sure a concurrent ACL DML on same table is blocked.
4195 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
4196 CREATE TABLE tmp1 (f1 INT);
4197 CREATE TRIGGER read_trig1 BEFORE INSERT ON tmp1 FOR EACH ROW
4198 BEGIN
4199 UPDATE user_copy SET User='u2x'
4200 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
4201 END|
4202 CREATE TRIGGER update_trig1 BEFORE UPDATE ON tmp1 FOR EACH ROW
4203 BEGIN
4204 UPDATE mysql.user SET user='u2x' WHERE User='u2';
4205 END|
4206 SELECT User FROM mysql.user WHERE User='u2';
4207 User
4208 u2
4209 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4210 START TRANSACTION;
4211 INSERT INTO tmp1 VALUES (20);
4212 # Make sure no record locks are held on ACL table.
4213 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4214 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4215 FROM performance_schema.data_locks
4216 WHERE LOCK_TYPE='RECORD' AND
4217 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4218 tab INDEX_NAME LOCK_MODE Has_lock
4219 DELETE FROM mysql.user WHERE User='u2';
4220 INSERT INTO mysql.user SELECT * FROM user_bk;
4221 REPLACE INTO mysql.user SELECT * FROM user_bk;
4222 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4223 CREATE USER someuser;
4224 ALTER USER someuser IDENTIFIED BY 'somepass';
4225 DROP USER someuser;
4226 COMMIT;
4227 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4228 START TRANSACTION;
4229 UPDATE tmp1 SET f1=30;
4230 # Make sure X record locks are held on ACL table.
4231 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4232 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4233 FROM performance_schema.data_locks
4234 WHERE LOCK_TYPE='RECORD' AND
4235 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4236 tab INDEX_NAME LOCK_MODE Has_lock
4237 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4238 # Make sure that we do not allow updating ACL table.
4239 INSERT INTO mysql.user SELECT * FROM user_bk;
4240 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4241 REPLACE INTO mysql.user SELECT * FROM user_bk;
4242 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4243 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4244 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4245 DELETE FROM mysql.user WHERE User='u2';
4246 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4247 SET SESSION lock_wait_timeout=default;
4248 SET SESSION innodb_lock_wait_timeout=default;
4249 ROLLBACK;
4250 DROP TABLE tmp1;
4251 DELETE FROM user_copy;
4252 # Case 2 (with READ UNCOMMITTED isolation level):
4253 # Using a separate transaction (With START TRANSACTION)
4254 # Read ACL table in SELECT in all isolation mode
4255 # and run DDL/DML on ACL tables in concurrent.
4256 SELECT User FROM mysql.user WHERE User='u2';
4257 User
4258 u2
4259 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4260 START TRANSACTION;
4261 SELECT User FROM mysql.user WHERE User='u2';
4262 User
4263 u2
4264 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4265 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4266 FROM performance_schema.data_locks
4267 WHERE LOCK_TYPE='RECORD' AND
4268 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4269 tab INDEX_NAME LOCK_MODE Has_lock
4270 DELETE FROM mysql.user WHERE User='u2';
4271 INSERT INTO mysql.user SELECT * FROM user_bk;
4272 REPLACE INTO mysql.user SELECT * FROM user_bk;
4273 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4274 CREATE USER someuser;
4275 ALTER USER someuser IDENTIFIED BY 'somepass';
4276 DROP USER someuser;
4277 COMMIT;
4278 # Case 2.1 (with READ UNCOMMITTED isolation level):
4279 # Using a separate transaction (With START TRANSACTION)
4280 # Read ACL table in SELECT .. HIGH PRIORITY in all isolation mode
4281 # and run DDL/DML on ACL tables in concurrent.
4282 SELECT User FROM mysql.user WHERE User='u2';
4283 User
4284 u2
4285 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4286 START TRANSACTION;
4287 SELECT HIGH_PRIORITY User FROM mysql.user WHERE User='u2';
4288 User
4289 u2
4290 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4291 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4292 FROM performance_schema.data_locks
4293 WHERE LOCK_TYPE='RECORD' AND
4294 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4295 tab INDEX_NAME LOCK_MODE Has_lock
4296 DELETE FROM mysql.user WHERE User='u2';
4297 INSERT INTO mysql.user SELECT * FROM user_bk;
4298 REPLACE INTO mysql.user SELECT * FROM user_bk;
4299 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4300 CREATE USER someuser;
4301 ALTER USER someuser IDENTIFIED BY 'somepass';
4302 DROP USER someuser;
4303 COMMIT;
4304 # Case 3 (with READ UNCOMMITTED isolation level):
4305 # Using a separate transaction (With START TRANSACTION)
4306 # Read ACL table in SELECT in all isolation mode
4307 # and run DDL/DML on ACL tables in concurrent.
4308 SELECT User FROM mysql.user WHERE User='u2';
4309 User
4310 u2
4311 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4312 set autocommit=0;
4313 SELECT User FROM mysql.user WHERE User='u2';
4314 User
4315 u2
4316 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4317 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4318 FROM performance_schema.data_locks
4319 WHERE LOCK_TYPE='RECORD' AND
4320 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4321 tab INDEX_NAME LOCK_MODE Has_lock
4322 DELETE FROM mysql.user WHERE User='u2';
4323 INSERT INTO mysql.user SELECT * FROM user_bk;
4324 REPLACE INTO mysql.user SELECT * FROM user_bk;
4325 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4326 CREATE USER someuser;
4327 ALTER USER someuser IDENTIFIED BY 'somepass';
4328 DROP USER someuser;
4329 COMMIT;
4330 set autocommit=default;
4331 # Case 4 (with READ UNCOMMITTED isolation level):
4332 # Read ACL table in DO command in different isolation mode
4333 # and run DDL/DML on ACL tables in concurrent.
4334 SELECT User FROM mysql.user WHERE User='u2';
4335 User
4336 u2
4337 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4338 START TRANSACTION;
4339 DO (SELECT User FROM mysql.user WHERE User='u2');
4340 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4341 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4342 FROM performance_schema.data_locks
4343 WHERE LOCK_TYPE='RECORD' AND
4344 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4345 tab INDEX_NAME LOCK_MODE Has_lock
4346 DELETE FROM mysql.user WHERE User='u2';
4347 INSERT INTO mysql.user SELECT * FROM user_bk;
4348 REPLACE INTO mysql.user SELECT * FROM user_bk;
4349 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4350 CREATE USER someuser;
4351 ALTER USER someuser IDENTIFIED BY 'somepass';
4352 DROP USER someuser;
4353 COMMIT;
4354 # Case 5 (with READ UNCOMMITTED isolation level):
4355 # Read ACL table in SET command in different isolation mode
4356 # and run DDL/DML on ACL tables in concurrent.
4357 SELECT User FROM mysql.user WHERE User='u2';
4358 User
4359 u2
4360 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4361 START TRANSACTION;
4362 SET @u2_host = (SELECT Host FROM mysql.user WHERE User='u2');
4363 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4364 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4365 FROM performance_schema.data_locks
4366 WHERE LOCK_TYPE='RECORD' AND
4367 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4368 tab INDEX_NAME LOCK_MODE Has_lock
4369 DELETE FROM mysql.user WHERE User='u2';
4370 INSERT INTO mysql.user SELECT * FROM user_bk;
4371 REPLACE INTO mysql.user SELECT * FROM user_bk;
4372 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4373 CREATE USER someuser;
4374 ALTER USER someuser IDENTIFIED BY 'somepass';
4375 DROP USER someuser;
4376 COMMIT;
4377 # Case 6:
4378 # Read ACL table in INSERT ... SELECT command in different
4379 # isolation mode and run DDL/DML on ACL tables in concurrent.
4380 SELECT User FROM mysql.user WHERE User='u2';
4381 User
4382 u2
4383 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4384 START TRANSACTION;
4385 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
4386 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4387 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4388 FROM performance_schema.data_locks
4389 WHERE LOCK_TYPE='RECORD' AND
4390 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4391 tab INDEX_NAME LOCK_MODE Has_lock
4392 DELETE FROM mysql.user WHERE User='u2';
4393 INSERT INTO mysql.user SELECT * FROM user_bk;
4394 REPLACE INTO mysql.user SELECT * FROM user_bk;
4395 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4396 CREATE USER someuser;
4397 ALTER USER someuser IDENTIFIED BY 'somepass';
4398 DROP USER someuser;
4399 COMMIT;
4400 # Case 7:
4401 # Read ACL table in UPDATE ... WHERE SELECT in different
4402 # isolation mode and run DDL/DML on ACL tables in concurrent.
4403 SELECT User FROM mysql.user WHERE User='u2';
4404 User
4405 u2
4406 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4407 START TRANSACTION;
4408 UPDATE user_copy SET User='u2x'
4409 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
4410 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4411 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4412 FROM performance_schema.data_locks
4413 WHERE LOCK_TYPE='RECORD' AND
4414 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4415 tab INDEX_NAME LOCK_MODE Has_lock
4416 DELETE FROM mysql.user WHERE User='u2';
4417 INSERT INTO mysql.user SELECT * FROM user_bk;
4418 REPLACE INTO mysql.user SELECT * FROM user_bk;
4419 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4420 CREATE USER someuser;
4421 ALTER USER someuser IDENTIFIED BY 'somepass';
4422 DROP USER someuser;
4423 COMMIT;
4424 # Case 8:
4425 # Read ACL table in Multi-table UPDATE ... SELECT in different
4426 # isolation mode and run DDL/DML on ACL tables in concurrent.
4427 SELECT User FROM mysql.user WHERE User='u2';
4428 User
4429 u2
4430 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4431 START TRANSACTION;
4432 UPDATE t1, user_copy SET user_copy.Host='instance1'
4433 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
4434 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4435 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4436 FROM performance_schema.data_locks
4437 WHERE LOCK_TYPE='RECORD' AND
4438 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4439 tab INDEX_NAME LOCK_MODE Has_lock
4440 DELETE FROM mysql.user WHERE User='u2';
4441 INSERT INTO mysql.user SELECT * FROM user_bk;
4442 REPLACE INTO mysql.user SELECT * FROM user_bk;
4443 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4444 CREATE USER someuser;
4445 ALTER USER someuser IDENTIFIED BY 'somepass';
4446 DROP USER someuser;
4447 COMMIT;
4448 # Case 9:
4449 # Read ACL table in DELETE ... WHERE SELECT in different
4450 # isolation mode and run DDL/DML on ACL tables in concurrent.
4451 SELECT User FROM mysql.user WHERE User='u2';
4452 User
4453 u2
4454 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4455 START TRANSACTION;
4456 DELETE FROM user_copy
4457 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
4458 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4459 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4460 FROM performance_schema.data_locks
4461 WHERE LOCK_TYPE='RECORD' AND
4462 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4463 tab INDEX_NAME LOCK_MODE Has_lock
4464 DELETE FROM mysql.user WHERE User='u2';
4465 INSERT INTO mysql.user SELECT * FROM user_bk;
4466 REPLACE INTO mysql.user SELECT * FROM user_bk;
4467 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4468 CREATE USER someuser;
4469 ALTER USER someuser IDENTIFIED BY 'somepass';
4470 DROP USER someuser;
4471 COMMIT;
4472 # Case 10:
4473 # Read ACL table in Multi-table DELETE ... WHERE SELECT in different
4474 # isolation mode and run DDL/DML on ACL tables in concurrent.
4475 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
4476 SELECT User FROM mysql.user WHERE User='u2';
4477 User
4478 u2
4479 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4480 START TRANSACTION;
4481 DELETE user_copy FROM user_copy JOIN (SELECT * FROM user_copy) tuser_copy
4482 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
4483 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4484 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4485 FROM performance_schema.data_locks
4486 WHERE LOCK_TYPE='RECORD' AND
4487 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4488 tab INDEX_NAME LOCK_MODE Has_lock
4489 DELETE FROM mysql.user WHERE User='u2';
4490 INSERT INTO mysql.user SELECT * FROM user_bk;
4491 REPLACE INTO mysql.user SELECT * FROM user_bk;
4492 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4493 CREATE USER someuser;
4494 ALTER USER someuser IDENTIFIED BY 'somepass';
4495 DROP USER someuser;
4496 COMMIT;
4497 # Case 11:
4498 # Read ACL table in CREATE TABLE ... SELECT in different
4499 # isolation mode and run DDL/DML on ACL tables in concurrent.
4500 SELECT User FROM mysql.user WHERE User='u2';
4501 User
4502 u2
4503 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4504 SET SESSION DEBUG_SYNC='create_select_after_write_rows_event SIGNAL parked WAIT_FOR go';
4505 CREATE TABLE tmp AS SELECT * FROM mysql.user WHERE User='u2';
4506 SET DEBUG_SYNC= 'now WAIT_FOR parked';
4507 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4508 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4509 FROM performance_schema.data_locks
4510 WHERE LOCK_TYPE='RECORD' AND
4511 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4512 tab INDEX_NAME LOCK_MODE Has_lock
4513 DELETE FROM mysql.user WHERE User='u2';
4514 INSERT INTO mysql.user SELECT * FROM user_bk;
4515 REPLACE INTO mysql.user SELECT * FROM user_bk;
4516 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4517 CREATE USER someuser;
4518 ALTER USER someuser IDENTIFIED BY 'somepass';
4519 DROP USER someuser;
4520 SET DEBUG_SYNC= 'now SIGNAL go';
4521 SET SESSION DEBUG_SYNC=RESET;
4522 COMMIT;
4523 SET SESSION DEBUG_SYNC=RESET;
4524 DROP TABLE tmp;
4525 # Case 12:
4526 # Read ACL table in LOCK TABLE for READ mode and
4527 # Make sure a concurrent ACL DML on same table is blocked.
4528 SELECT User FROM mysql.user WHERE User='u2';
4529 User
4530 u2
4531 LOCK TABLE mysql.user READ;
4532 SET SESSION lock_wait_timeout=1;
4533 SET SESSION innodb_lock_wait_timeout=1;
4534 # Make sure that we do not allow updating ACL table.
4535 INSERT INTO mysql.user SELECT * FROM user_bk;
4536 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4537 REPLACE INTO mysql.user SELECT * FROM user_bk;
4538 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4539 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4540 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4541 DELETE FROM mysql.user WHERE User='u2';
4542 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4543 UNLOCK TABLES;
4544 # Case 13:
4545 # Run FTWRL READ mode and
4546 # make sure a concurrent ACL DML on same table is blocked.
4547 SELECT User FROM mysql.user WHERE User='u2';
4548 User
4549 u2
4550 FLUSH TABLES WITH READ LOCK;
4551 INSERT INTO mysql.user SELECT * FROM user_bk;
4552 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4553 REPLACE INTO mysql.user SELECT * FROM user_bk;
4554 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4555 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4556 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4557 DELETE FROM mysql.user WHERE User='u2';
4558 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4559 UNLOCK TABLES;
4560 # Case 14:
4561 # Update ACL table in a DML inside a SP in different isolation mode.
4562 # Make sure a concurrent ACL DML on same table is blocked.
4563 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
4564 SELECT User FROM mysql.user WHERE User='u2';
4565 User
4566 u2
4567 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4568 START TRANSACTION;
4569 CALL proc2();
4570 # Make sure X record locks are held on ACL table.
4571 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4572 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4573 FROM performance_schema.data_locks
4574 WHERE LOCK_TYPE='RECORD' AND
4575 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4576 tab INDEX_NAME LOCK_MODE Has_lock
4577 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4578 # Make sure that we do not allow updating ACL table.
4579 INSERT INTO mysql.user SELECT * FROM user_bk;
4580 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4581 REPLACE INTO mysql.user SELECT * FROM user_bk;
4582 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4583 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4584 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4585 DELETE FROM mysql.user WHERE User='u2';
4586 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4587 ROLLBACK;
4588 SELECT User FROM mysql.user WHERE User='u2';
4589 User
4590 u2
4591 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4592 START TRANSACTION;
4593 SELECT func1();
4594 func1()
4595 0
4596 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4597 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4598 FROM performance_schema.data_locks
4599 WHERE LOCK_TYPE='RECORD' AND
4600 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4601 tab INDEX_NAME LOCK_MODE Has_lock
4602 DELETE FROM mysql.user WHERE User='u2';
4603 INSERT INTO mysql.user SELECT * FROM user_bk;
4604 REPLACE INTO mysql.user SELECT * FROM user_bk;
4605 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4606 CREATE USER someuser;
4607 ALTER USER someuser IDENTIFIED BY 'somepass';
4608 DROP USER someuser;
4609 ROLLBACK;
4610 # Case 15:
4611 # Reference ACL table in a foreign key. Reading ACL table while
4612 # updating the table in different isolation mode.
4613 # Make sure a concurrent ACL DML on ACL table is blocked.
4614 CREATE TABLE fk_to_acl_table
4615 (Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
4616 User char(32) COLLATE utf8_bin DEFAULT '' NOT NULL,
4617 address CHAR(255),
4618 FOREIGN KEY (Host, User) REFERENCES mysql.user(Host,User));
4619 Warnings:
4620 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
4621 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4622 ALTER TABLE fk_to_acl_table MODIFY COLUMN address char(200),
4623 ALGORITHM=COPY;
4624 SELECT User FROM mysql.user WHERE User='u2';
4625 User
4626 u2
4627 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4628 START TRANSACTION;
4629 INSERT INTO fk_to_acl_table VALUES ('%', 'u2', '2nd cross, 1st main, downtown');
4630 # Make sure S record locks are held on ACL table.
4631 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4632 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4633 FROM performance_schema.data_locks
4634 WHERE LOCK_TYPE='RECORD' AND
4635 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4636 tab INDEX_NAME LOCK_MODE Has_lock
4637 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
4638 # Make sure that we do not allow updating ACL tables.
4639 REPLACE INTO mysql.user SELECT * FROM user_bk;
4640 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4641 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4642 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4643 DELETE FROM mysql.user WHERE User='u2';
4644 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4645 ROLLBACK;
4646 # Cleanup
4647 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
4648 DROP TABLE fk_to_acl_table;
4649 # Case 16:
4650 # Case 16.1 Update ACL table in a multi-table UPDATE command in
4651 # different isolation level. Make sure a concurrent DML on ACL table
4652 # is blocked.
4653 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4654 SELECT User FROM mysql.user WHERE User='u2';
4655 User
4656 u2
4657 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4658 START TRANSACTION;
4659 UPDATE mysql.user u, user_copy SET u.User='u2x' WHERE u.User LIKE 'u2';
4660 # Make sure X record locks are held on ACL table.
4661 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4662 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4663 FROM performance_schema.data_locks
4664 WHERE LOCK_TYPE='RECORD' AND
4665 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4666 tab INDEX_NAME LOCK_MODE Has_lock
4667 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4668 # Make sure that we do not allow updating ACL tables.
4669 REPLACE INTO mysql.user SELECT * FROM user_bk;
4670 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4671 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4672 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4673 DELETE FROM mysql.user WHERE User='u2';
4674 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4675 ROLLBACK;
4676 # Case 16.2 Read ACL table in a multi-table UPDATE command in
4677 # different isolation level. Ideally concurrent DML on ACL table
4678 # should have been blocked. Due to Bug#31731752 we acquire
4679 # stronger lock on the table and hence acquire X clock on the
4680 # ACL table being read.
4681 SELECT User FROM mysql.user WHERE User='u2';
4682 User
4683 u2
4684 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4685 START TRANSACTION;
4686 UPDATE mysql.user u1, user_copy u2 SET u2.User='u2x' WHERE u2.User LIKE 'u2';
4687 # Check that X record locks is held on ACL table.
4688 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4689 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4690 FROM performance_schema.data_locks
4691 WHERE LOCK_TYPE='RECORD' AND
4692 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4693 tab INDEX_NAME LOCK_MODE Has_lock
4694 mysql.user PRIMARY S,REC_NOT_GAP Acquired row lock on u2/u2x
4695 # Make sure that we do not allow updating ACL tables.
4696 REPLACE INTO mysql.user SELECT * FROM user_bk;
4697 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4698 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4699 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4700 DELETE FROM mysql.user WHERE User='u2';
4701 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4702 ROLLBACK;
4703 # Case 17:
4704 # Case 17.1: Delete row in ACL table in a multi-table DELETE in
4705 # different isolation level. Make sure a concurrent DML on ACL
4706 # table is blocked.
4707 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
4708 SELECT User FROM mysql.user WHERE User='u2';
4709 User
4710 u2
4711 SELECT User FROM user_copy;
4712 User
4713 u2
4714 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4715 START TRANSACTION;
4716 DELETE u, cu FROM mysql.user u JOIN user_copy cu WHERE u.User LIKE 'u2';
4717 # Make sure X record locks are held on ACL table.
4718 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4719 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4720 FROM performance_schema.data_locks
4721 WHERE LOCK_TYPE='RECORD' AND
4722 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4723 tab INDEX_NAME LOCK_MODE Has_lock
4724 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4725 # Make sure that we do not allow updating ACL table.
4726 REPLACE INTO mysql.user SELECT * FROM user_bk;
4727 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4728 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4729 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4730 DELETE FROM mysql.user WHERE User='u2';
4731 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4732 ROLLBACK;
4733 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
4734 # Case 17.2 Read ACL table in a multi-table DELETE command in
4735 # different isolation level. Make sure a concurrent DML on ACL table
4736 # is not blocked.
4737 SELECT User FROM mysql.user WHERE User='u2';
4738 User
4739 u2
4740 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4741 START TRANSACTION;
4742 DELETE u1 FROM user_copy u1 JOIN mysql.user u2 ON u2.User = u1.User;
4743 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4744 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4745 FROM performance_schema.data_locks
4746 WHERE LOCK_TYPE='RECORD' AND
4747 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4748 tab INDEX_NAME LOCK_MODE Has_lock
4749 # Make sure no record locks are held on ACL table.
4750 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4751 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4752 FROM performance_schema.data_locks
4753 WHERE LOCK_TYPE='RECORD' AND
4754 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4755 tab INDEX_NAME LOCK_MODE Has_lock
4756 DELETE FROM mysql.user WHERE User='u2';
4757 INSERT INTO mysql.user SELECT * FROM user_bk;
4758 REPLACE INTO mysql.user SELECT * FROM user_bk;
4759 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4760 CREATE USER someuser;
4761 ALTER USER someuser IDENTIFIED BY 'somepass';
4762 DROP USER someuser;
4763 ROLLBACK;
4764 # Case 18:
4765 # Read ACL table in a DML inside a trigger in different isolation
4766 # mode. Make sure a concurrent ACL DML on same table is blocked.
4767 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
4768 CREATE TABLE tmp1 (f1 INT);
4769 CREATE TRIGGER read_trig1 BEFORE INSERT ON tmp1 FOR EACH ROW
4770 BEGIN
4771 UPDATE user_copy SET User='u2x'
4772 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
4773 END|
4774 CREATE TRIGGER update_trig1 BEFORE UPDATE ON tmp1 FOR EACH ROW
4775 BEGIN
4776 UPDATE mysql.user SET user='u2x' WHERE User='u2';
4777 END|
4778 SELECT User FROM mysql.user WHERE User='u2';
4779 User
4780 u2
4781 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4782 START TRANSACTION;
4783 INSERT INTO tmp1 VALUES (20);
4784 # Make sure no record locks are held on ACL table.
4785 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4786 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4787 FROM performance_schema.data_locks
4788 WHERE LOCK_TYPE='RECORD' AND
4789 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4790 tab INDEX_NAME LOCK_MODE Has_lock
4791 DELETE FROM mysql.user WHERE User='u2';
4792 INSERT INTO mysql.user SELECT * FROM user_bk;
4793 REPLACE INTO mysql.user SELECT * FROM user_bk;
4794 UPDATE mysql.user SET user='u2' WHERE user LIKE 'u2x';
4795 CREATE USER someuser;
4796 ALTER USER someuser IDENTIFIED BY 'somepass';
4797 DROP USER someuser;
4798 COMMIT;
4799 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4800 START TRANSACTION;
4801 UPDATE tmp1 SET f1=30;
4802 # Make sure X record locks are held on ACL table.
4803 SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
4804 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
4805 FROM performance_schema.data_locks
4806 WHERE LOCK_TYPE='RECORD' AND
4807 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
4808 tab INDEX_NAME LOCK_MODE Has_lock
4809 mysql.user PRIMARY X,REC_NOT_GAP Acquired row lock on u2/u2x
4810 # Make sure that we do not allow updating ACL table.
4811 INSERT INTO mysql.user SELECT * FROM user_bk;
4812 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4813 REPLACE INTO mysql.user SELECT * FROM user_bk;
4814 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4815 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
4816 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4817 DELETE FROM mysql.user WHERE User='u2';
4818 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
4819 SET SESSION lock_wait_timeout=default;
4820 SET SESSION innodb_lock_wait_timeout=default;
4821 ROLLBACK;
4822 DROP TABLE tmp1;
4823 DELETE FROM user_copy;
4824 Cleanup
4825 DROP PROCEDURE proc1;
4826 DROP PROCEDURE proc2;
4827 DROP FUNCTION func1;
4828 DROP USER u1;
4829 DROP USER u2;
4830 DROP USER writer1;
4831 DROP USER writer2;
4832 DROP TABLE t1;
4833 DROP TABLE user_copy;
4834 DROP TABLE user_bk;