Unindexed foreign key columns are the primary cause of TM lock contention in mode 3. However, this only applies to databases prior to Oracle9i Database. Depending on the operation, when foreign key columns are not indexed, Oracle either takes up a DML share lock (S – mode 4) or share row exclusive lock (SRX – mode 5) on the child table whenever the parent key or row is modified. (The share row exclusive lock is taken on the child table when the parent row is deleted and the foreign key constraint is created with the ON DELETE CASCADE option. Without this option, Oracle takes the share lock.) The share lock or share row exclusive lock on the child table prohibits other processes from getting a row exclusive lock (RX—mode 3) on the table. The waiting session will wait until the blocking session commits or rolls back its transaction.
Here is a philosophical question for you: Are you going to start building new indexes for all the foreign key columns in your databases? DBAs are divided on this. Our take is that you should hold your horses and don’t get carried away building new indexes just yet. If you do, you will introduce many new indexes to the database, some that are unnecessary. For example, you don’t need to create new indexes on foreign key columns when the parent tables they reference are static. You only need to create indexes on foreign key columns of the child table that is being identified by the enqueue wait event. The object ID for the child table is recorded in the P2 column, which corresponds to the ID1 column of the V$LOCK view. Query the DBA_OBJECTS view using the object ID and you will see the name of the child table. Yes, you will be operating in reactive mode, but it beats creating unnecessary indexes in the database, which not only wastes storage and increases maintenance, but may open up another can of worms for SQL tuning.
Following is an Oracle8i Database V$LOCK view output of a TM enqueue wait in mode 3 that is caused by an unindexed foreign key column. Notice the blocking session holds two TM locks: one for the parent table (ID1=3185) and the other for the child table (ID=3187). The share row exclusive lock (mode 5) on the child table prevents the row exclusive lock (mode 3) request from the waiting session (SID=9).
The same steps that are used to produce the preceding TM enqueue contention are repeated in an Oracle9i Database;
following is the output. In this case, the ID of the child table is 128955 and the ID of the parent table is 128953. Notice that there is no blocking lock. Unindexed foreign keys are no longer an issue starting in Oracle9i Database.
Finally, TM enqueue contention in mode 3 can also occur when a table is explicitly locked in the share mode or higher and there are concurrent DML activities against the table. This is common with applications that use old third-party vendor codes. You can query the V$SQLAREA view for the LOCK TABLE statement. Following is an example of what this contention will look like in the V$LOCK view:
Leave a Reply