A wait for the TX enqueue in mode 6 (P1 = 1415053318, P1RAW = 54580006) is the most common enqueue wait. (In Oracle Database 10g, the wait event name is enq: TX—row lock contention.) This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction. This usually is an application issue. The waiting session will wait until the blocking session commits or rolls back its transaction. There is no other way to release the lock. (Killing the blocking session will cause its transaction to be rolled back.)
The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:
Whenever you see an enqueue wait event for the TX enqueue, the first step is to find out who the blocker is and if there are multiple waiters for the same resource by using the following query. If the blocking session is an ad-hoc process, then the user may be taking a break. In this case, ask the user to commit or roll back the transaction. If the blocking session is a batch or OLTP application process, then check to see if the session is still “alive.” It may be a live Oracle session, but its parent process may be dead or hung. In this case, chances are you will have to kill the session to release the locks. Be sure to confirm with the application before killing a production process.
You can discover the resource that is being competed for. The resource ID is available in the V$LOCK.ID1 column of the DML lock (TM) for that transaction. It is also available in the V$SESSION.ROW_WAIT_OBJ# of the waiting session. The following query retrieves the resource of the TX enqueue wait:
Don’t forget to extract the SQL statement that is executed by the waiting session as well as the SQL statement that is executed by the blocking session. These statements will give you an idea of what the waiting session is trying to do and what the blocking session is doing. They are also important points of reference for the application developers so that they can quickly locate the modules. (By the way, the SQL statement that is currently being executed by the blocking session is not necessarily the statement that holds the lock. The statement that holds the lock might have been run a long time ago.)
[…] Diagnosing Oracle Wait for TX Enqueue in Mode 6 (Row Lock Contention) […]