Oracle lock 3: DDL lock, oracle lock 3ddl
When an ongoing DDL operation is executed or a schema object (Note) is referenced, the Data dcictionary (DDL) Lock is used to protect the definition of the schema object, only a single schema object is locked during DDL operation. The database never locks the entire data dictionary.
The Oracle database automatically requests the DDL lock. the user cannot display the request DDL lock. For example, if you create a stored procedure, the database automatically requests DDL locks for the objects referenced in the stored procedure. These DDL locks prevent the object from being changed or deleted before the stored procedure is compiled.
Exclusive DDL lock
One row of DDL locks prevents other sessions from obtaining DDL or DML locks. Most DDL operations, except as described in the "share DDL lock", require the DDL lock for the resource. For example, when the alter table is adding a column to the TABLE, drop table cannot be executed, and vice versa.
DDL locks remain until the DDL operation is completed and automatically submitted. During this period, if other operations need to use this schema object, wait until the old DDL lock is released.
Share DDL lock
The shared DDL lock prevents conflicting DDL operations that may damage the structure, but allows parallel execution of similar DDL operations.
For example, when a create procedure statement is executed, the transaction requires the DDL lock to be shared for all referenced tables, other transactions can concurrently create stored procedures that reference the same table and request to share DDL locks on the same table. However, no transaction can request a row of DDL locks on any referenced table.
The shared DDL lock continues until the DDL operation is completed and automatically committed. Therefore, the transaction that obtains the shared DDL lock is ensured that the schema object remains unchanged during the execution of the transaction.
Resolution locks that can be interrupted
Each schema object referenced in the SQL statement and PL/SQL block uses a parsing lock, because if a referenced object is changed or deleted, the corresponding shared SQL domain (Note) becomes invalid. A parsing lock is called a breakable parse lock because it does not block any DDL operations and can be interrupted to allow conflicting DDL operations.
A parsing lock is required when parsing SQL statements. If the statement's shared SQL domain is kept in the shared pool, the lock will be held all the time.
Note:
Schema is a set of database object names;
A schema object is the logical structure of the data stored in a schema;
Shared SQL area refers to the parse tree and execution plan saved for an SQL statement in the shared pool, A statement only has one shared SQL domain.
