TM locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If another user attempts to perform DDL on the table while you have a TM lock on it, they’ll receive the following error message:
drop table dept
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Note You can set DDL_LOCK_TIMEOUT in order to have DDL wait. This is achieved typically via the ALTER SESSION command. For example, you could issue ALTER SESSION SET DDL_LOCK_TIMEOUT=60; before issuing the DROP TABLE command. The DROP TABLE command issued would then wait 60 seconds before returning an error (or it could succeed, of course, as well).
The ORA-00054 message is a confusing message at first, since there is no direct method to specify NOWAIT or WAIT on a DROP TABLE at all. It is just the generic message you get when you attempt to perform an operation that would be blocked, but the operation does not permit blocking. As you’ve seen before, it’s the same message you get if you issue a SELECT FOR UPDATE NOWAIT against a locked row.
The following shows how these locks would appear in the V$LOCK table:
$ sqlplus eoda/foo@PDB1
SQL> create table t1 ( x int );Table created.
SQL> create table t2 ( x int );Table created.
SQL> insert into t1 values ( 1 );1 row created.
SQL> insert into t2 values ( 1 );1 row created.
SQL> select (select username
from v$sessionwhere sid = v$lock.sid) username,sid,id1,id2,lmode,request, block, v$lock.type
from v$lockwhere sid = sys_context(‘userenv’,’sid’);
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
EODA 22 133 0 4 0 0 AE
EODA 22 244271 0 3 0 0 TM
EODA 22 244270 0 3 0 0 TM
EODA 22 1966095 152 6 0 0 TX
SQL> select object_name, object_id from user_objects where object_id in (244271,244270);
OBJECT_NAM OBJECT_ID
T2 244271
T1 244270
Note The AE lock is an edition lock, available in Oracle 11g and above. It is part of the edition-based redefinition feature (not covered in this particular book). ID1 is the object ID of the edition that SID is using currently. This edition lock protects the referenced edition from modification (dropping of the edition, for example) in much the same way the TM locks protect the tables they point to from structural modification.
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 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.
Leave a Reply