Lock Types - Oracle Database Transactions and Locking Revealed - page 37

EODA@ORA12CR1> insert into t2 values ( 1 );
1 row created.

EODA@ORA12CR1> select (select username
2 from v$session
3 where sid = v$lock.sid) username,
4 sid,
5 id1,
6 id2,
7 lmode,
8 request, block, v$lock.type
9 from v$lock
10 where 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

EODA@ORA12CR1> select object_name, object_id
2 from user_objects
3 where object_id in (244271,244270);

OBJECT_NAME OBJECT_ID
----------- ----------
T2 244271
T1 244270

■ the AE lock is an edition lock, available in oracle 11 g 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.

Note

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.