Diagnosing Oracle Wait for TX Enqueue in Mode 4–Unique Key Enforcement

By: Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan


Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10g, the wait event name is enq: TX—row lock contention.) This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if “ORA-00001 unique constraint (%s.%s) violated” should be raised.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn’t mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions—one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.

Fig 6-17

 

 

 

 

 

Your action items are: find out who is blocking, the name of the resource being competed for, and the SQL statements executed by the waiting and the blocking session. This is an application issue, and the fix must come from the application.

 

Leave a Reply