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 ALTER TABLE T, the table T will in general have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table.
Note In older versions of Oracle, ALTER TABLE T would have an exclusive DDL lock placed against it. In this example, table T prevents other sessions from performing DDL and acquiring TM locks (used to modify the contents of the table). Now, many ALTER commands can be performed online—without preventing modifications.
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:
BeginCommit;DDL-STATEMENTCommit;ExceptionWhen 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
SQL> alter table t move;
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 other DDL statements. In Oracle, some DDL operations may now take place without DDL locks. For example, I can issue the following:
SQL> 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 action. This greatly increases the availability of data. To see this for yourself, you could create a table of some size:
SQL> create table t as select * from all_objects; Table created.
SQL> select object_id from user_objects where object_name = ‘T’;OBJECT_ID
And then run the create index against that table:
SQL> create index t_idx on t(owner,object_type,object_name) ONLINE;
While at the same time running this query in another session to see the locks taken against that newly created table (remember, ID1=244277 is specific to my example, you’ll want to use your object ID):
SQL> select (select usernamefrom v$sessionwhere sid = v$lock.sid) username,sid,id1,id2,lmode,request, block, v$lock.type from v$lockwhere id1 = 244277;
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
EODA 22 244277 0 3 0 0 DL
EODA 22 244277 0 3 0 0 DL
EODA 22 244277 0 2 0 0 TM
EODA 22 244277 0 4 0 0 OD
So, here we see four locks taken out against our object. The two DL locks are direct load locks. They are used to prevent a direct path load into our base table while the index creation is taking place (which implies, of course, that you cannot directly path load the table and create the index simultaneously).
The OD lock is a lock that permits truly online DDL. In the past (older versions of Oracle), online DDL such as CREATE INDEX ONLINE was not 100 percent online. It would take a lock at the beginning and end of the CREATE statement—preventing other concurrent activities (modifications of the base table data). It was mostly online but not completely online.
Starting with 11g, the CREATE INDEX ONLINE command is completely online; it does not require exclusionary locks at the beginning/end of the command. Part of the implementation to accomplish this feat was the introduction of the OD (online DDL) lock; it is used internally to allow truly online DDL operations.
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 the following, share DDL locks will be placed against both EMP and DEPT while the CREATE VIEW command is being processed:
Create view MyViewasselect emp.empno, emp.ename, dept.deptno, dept.dname from emp, dept where emp.deptno = dept.deptno;
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.
Leave a Reply