1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
SET sql_mode=ORACLE;
DO GET_LOCK('lock',300);
connect conn1,localhost,root,,;
SET sql_mode=ORACLE;
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY pkg1 AS
PROCEDURE p1 AS
BEGIN
DO GET_LOCK('lock',300);
END;
FUNCTION f1 RETURN INT AS
BEGIN
CALL p1;
RETURN 1;
END;
END;
$$
SELECT pkg1.f1();
connection default;
connect conn2,localhost,root,,;
SET sql_mode=ORACLE;
DROP PACKAGE pkg1;
connection default;
SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME
FROM INFORMATION_SCHEMA.PROCESSLIST
LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO
ON (ID=THREAD_ID)
ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE;
CONN 0
INFO SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME
FROM INFORMATION_SCHEMA.PROCESSLIST
LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO
ON (ID=THREAD_ID)
ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE
STATE Filling schema table
LOCK_MODE MDL_SHARED_NO_WRITE
LOCK_TYPE User lock
TABLE_NAME
CONN 1
INFO DO GET_LOCK('lock',300)
STATE User lock
LOCK_MODE MDL_SHARED
LOCK_TYPE Stored package body metadata lock
TABLE_NAME pkg1
CONN 1
INFO DO GET_LOCK('lock',300)
STATE User lock
LOCK_MODE MDL_SHARED
LOCK_TYPE Stored function metadata lock
TABLE_NAME pkg1.f1
CONN 1
INFO DO GET_LOCK('lock',300)
STATE User lock
LOCK_MODE MDL_SHARED
LOCK_TYPE Stored procedure metadata lock
TABLE_NAME pkg1.p1
CONN 2
INFO DROP PACKAGE pkg1
STATE Waiting for stored package body metadata lock
LOCK_MODE MDL_BACKUP_DDL
LOCK_TYPE Backup lock
TABLE_NAME
CONN 2
INFO DROP PACKAGE pkg1
STATE Waiting for stored package body metadata lock
LOCK_MODE MDL_INTENTION_EXCLUSIVE
LOCK_TYPE Schema metadata lock
TABLE_NAME
DO RELEASE_LOCK('lock');
connection conn1;
pkg1.f1()
1
disconnect conn1;
connection conn2;
disconnect conn2;
connection default;
|
