Database Reference
In-Depth Information
Whereas we get only one
TX
lock per transaction, we can get as many
TM
locks as the objects we modify. Here, the
interesting thing is that the
ID1
column for the TM lock is the object ID of the DML-locked object, so it is easy to find
the object on which the lock is being held.
An interesting aside to the TM lock: the total number of TM locks allowed in the system is configurable by you
(for details, see the
DML_LOCKS
parameter definition in the
Oracle Database Reference
manual). It may, in fact, be set
to zero. This does not mean that your database becomes a read-only database (no locks), but rather that DDL is not
permitted. This is useful in very specialized applications, such as RAC implementations, to reduce the amount of
intra-instance coordination that would otherwise take place. You can also remove the ability to gain TM locks on an
object-by-object basis using the
ALTER TABLE <TABLENAME> DISABLE TABLE LOCK
command. This is a quick way to
make it harder to accidentally drop a table, as you will have to reenable the table lock before dropping the table. It can
also be used to detect a full table lock as a result of the unindexed foreign key we discussed previously.
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
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.
■
oracle 11
g
has modified what used to be a rule. in the past, 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.
Note
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.
