Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-package-mdl.test

raw
1 --source include/have_metadata_lock_info.inc
2
3 #
4 # This test demonstrates that:
5 # - A call to a package routine acquires a shared MDL lock on the entire package
6 # - "DROP PACKAGE" waits until the currently running package routines end
7 #
8
9 SET sql_mode=ORACLE;
10 DO GET_LOCK('lock',300);
11
12
13 #
14 # conn1 will execute package pkg1 routines and
15 # and therefore acquire a shared MDL on "package body pkg1"
16 #
17
18 connect (conn1,localhost,root,,);
19 SET sql_mode=ORACLE;
20 let $conn1_id= `SELECT CONNECTION_ID()`;
21 DELIMITER $$;
22 CREATE PACKAGE pkg1 AS
23 PROCEDURE p1;
24 FUNCTION f1 RETURN INT;
25 END;
26 $$
27 CREATE PACKAGE BODY pkg1 AS
28 PROCEDURE p1 AS
29 BEGIN
30 DO GET_LOCK('lock',300);
31 END;
32 FUNCTION f1 RETURN INT AS
33 BEGIN
34 CALL p1;
35 RETURN 1;
36 END;
37 END;
38 $$
39 DELIMITER ;$$
40 send SELECT pkg1.f1();
41
42 #
43 # wait for conn1 to actually start execution of pkg1.p1
44 #
45
46 connection default;
47 let $wait_timeout= 60;
48 let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
49 WHERE ID=$conn1_id AND INFO LIKE '%GET_LOCK%' AND STATE='User lock';
50 --source include/wait_condition.inc
51
52
53 #
54 # conn2 will do "DROP PACKAGE pkg1".
55 # It will acquire an exclusive MDL on "package body pkg1", and therefore
56 # it should wait until conn1 ends the package routine execution
57 #
58
59 connect (conn2,localhost,root,,);
60 let $conn2_id= `SELECT CONNECTION_ID()`;
61 SET sql_mode=ORACLE;
62 send DROP PACKAGE pkg1;
63
64 #
65 # wait for conn2 to actually enter the "DROP" statement and get locked by conn1
66 #
67 connection default;
68 let $wait_timeout= 60;
69 let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
70 WHERE ID=$conn2_id
71 AND INFO LIKE '%DROP PACKAGE%'
72 AND STATE='Waiting for stored package body metadata lock';
73 --source include/wait_condition.inc
74
75 #
76 # Now we have three threads involved.
77 # The following I_S query will check that the threads are in these states:
78 #
79 # default (0) - is holding a user lock 'lock'
80 # conn1 (1) - is executing the package procedure test.pkg1.p1,
81 # is holding a shared MDL on 'package body pkg1',
82 # is waiting for the user lock 'lock' to be released
83 # conn2 (2) - is waiting for 'conn1' to end execution of test.pkg1.* routines,
84 # to acquire an exclusive MDL on 'package body pkg1',
85 # to DROP the package pkg1
86 #
87 CREATE TEMPORARY TABLE mythreads(t_id INT UNSIGNED NOT NULL, t_name VARCHAR(16));
88 evalp INSERT INTO mythreads VALUES(CONNECTION_ID(), 'default_id'),($conn1_id, 'conn1_id'),($conn2_id, 'conn2_id');
89 --vertical_results
90 SELECT t_name AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME
91 FROM INFORMATION_SCHEMA.PROCESSLIST
92 INNER JOIN mythreads
93 ON (ID=t_id)
94 LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO
95 ON (ID=THREAD_ID)
96 ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE;
97 --horizontal_results
98 DROP TABLE mythreads;
99
100 #
101 # Now let conn1 finish the package routine execution
102 #
103 DO RELEASE_LOCK('lock');
104 connection conn1;
105 reap;
106 disconnect conn1;
107
108 #
109 # Now conn2 should actually DROP the package
110 #
111 connection conn2;
112 reap;
113 disconnect conn2;
114
115 connection default;