아래 내용은 Tom Kyte 의 최근 저서인
Expert Oracle Database Architecture 에서 발췌한 내용입니다.
참고하세요...
DDL Locks
DDL locks are automatically placed against objects during a DDL operation to
protect them from changes by other sessions. For example, if I perform the DDL
operation ALTERTABLE T, the table T will have an exclusive DDL lock placed
against it, preventing other sessions from getting DDL locks and TM locks on
this table. DDL locks are held for the duration of the DDL statement and are
released immediately afterward. This is done, in effect, by always wrapping DDL
statements in implicit commits (or a commit/rollback pair). For this reason, DDL
always commits in Oracle. Every CREATE, ALTER, and so on statement is really
executed as shown in this pseudo-code:
Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;
So, DDL will always commit, even if it is unsuccessful. DDL starts by
committing—be aware of this. It commits first so that if it has to roll back, it
will not roll back your transaction. If you execute DDL, it will make permanent
any outstanding work you have performed, even if the DDL is not successful. If
you need to execute DDL, but you do not want it to commit your existing
transaction, you may use an autonomous transaction.
There are three types of DDL locks:
* Exclusive DDL locks: These prevent other sessions from gaining a DDL
lock or TM (DML) lock themselves. This means that you may query a table during a
DDL operation, but you may not modify it in any way.
* Share DDL locks: These protect the structure of the referenced object
against modification by other sessions, but allow modifications to the data.
* Breakable parse locks: These allow an object, such as a query plan
cached in the shared pool, to register its reliance on some other object. If you
perform DDL against that object, Oracle will review the list of objects that
have registered their dependence and invalidate them. Hence, these locks are
“breakable”—they do not prevent the DDL from occurring.
Most DDL takes an exclusive DDL lock. If you issue a statement such as
Alter table t add new_column date;
the table T will be unavailable for modifications during the execution of that
statement. The table may be queried using SELECT during this time, but most
other operations will be prevented, including all DDL statements. In Oracle,
some DDL operations may now take place without DDL locks. For example, I can
issue the following:
create index t_idx on t(x) ONLINE;
The ONLINE keyword modifies the method by which the index is actually built.
Instead of taking an exclusive DDL lock, preventing modifications of data,
Oracle will only attempt to acquire a low-level (mode 2) TM lock on the table.
This will effectively prevent other DDL from taking place, but it will allow DML
to occur normally. Oracle accomplishes this feat by keeping a record of
modifications made to the table during the DDL statement and applying these
changes to the new index as it finishes the CREATE. This greatly increases the
availability of data.
Other types of DDL take share DDL locks. These are taken out against dependent
objects when you create stored, compiled objects, such as procedures and views.
For example, if you execute
Create view MyView
as
select *
from emp, dept
where emp.deptno = dept.deptno;
share DDL locks will be placed against both EMP and DEPT, while the CREATE VIEW
command is being processed. You can modify the contents of these tables, but you
cannot modify their structure.
The last type of DDL lock is a breakable parse lock. When your session parses a
statement, a parse lock is taken against every object referenced by that
statement. These locks are taken in order to allow the parsed, cached statement
to be invalidated (flushed) in the shared pool if a referenced object is dropped
or altered in some way.
A view that is invaluable for looking at this information is DBA_DDL_LOCKS.
There is no V$ view for you to look at. The DBA_DDL_LOCKS view is built on the
more mysterious X$ tables and, by default, it will not be installed in your
database. You can install this and other locking views by running the
catblock.sql script found in the directory [ORACLE_HOME]/rdbms/admin. This
script must be executed as the user SYS in order to succeed. Once you have
executed this script, you can run a query against the view. For example, in a
single-user database I see the following:
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks;
SID OWNER NAME TYPE HELD REQUEST
----
---------------------
----
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
161 OPS$TKYTE OPS$TKYTE 18 Null None
161 SYS DBMS_OUTPUT Body Null None
161 SYS DATABASE 18 Null None
161 SYS DBMS_UTILITY Table/Procedure/Type Null None
161 SYS DBMS_UTILITY Table/Procedure/Type Null None
161 SYS PLITBLM Table/Procedure/Type Null None
161 SYS DBMS_APPLICATION_INFO Body Null None
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
11 rows selected.
These are all the objects that my session is “locking.” I have breakable parse
locks on a couple of the DBMS_* packages. These are a side effect of using
SQL*Plus; it calls DBMS_APPLICATION_INFO, for example. I may see more than one
copy of various objects here—this is normal, and it just means I have more than
one thing I’m using in the shared pool that references these objects. It is
interesting to note that in the view, the OWNER column is not the owner of the
lock; rather, it is the owner of the object being locked. This is why you see
many SYS rows. SYS owns these packages, but they all belong to my session.
To see a breakable parse lock in action, let’s first create and run a stored
procedure, P:
ops$tkyte@ORA10G> create or replace procedure p as begin null; end;
2 /
Procedure created.
ops$tkyte@ORA10G> exec p
PL/SQL procedure successfully completed.
The procedure, P, will now show up in the DBA_DDL_LOCKS view. We have a parse
lock on it:
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 /
SID OWNER NAME TYPE HELD REQUEST
----
---------------------
----
161 OPS$TKYTE P Table/Procedure/Type Null None
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
...
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
12 rows selected.
We then recompile our procedure and query the view again:
ops$tkyte@ORA10G> alter procedure p compile;
Procedure altered.
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 /
SID OWNER NAME TYPE HELD REQUEST
----
---------------------
----
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
...
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
11 rows selected.
We find that P is now missing from the view. Our parse lock has been broken.
This view is useful to you, as a developer, when it is found that some piece of
code won’t compile in the test or development system—it hangs and eventually
times out. This indicates that someone else is using it (actually running it),
and you can use this view to see who that might be. The same will happen with
GRANTS and other types of DDL against the object. You cannot grant EXECUTE on a
procedure that is running, for example. You can use the same method to discover
the potential blockers and waiters.
