Waiting on a lock held by another session

Time spent waiting on another session's lock is not solved in the SQL of the session that waits: the cause is in the transaction that holds the lock, and that transaction does not appear in the trace. The work is finding who holds it and why.

Symptoms

In the Diagnosis, the finding Waiting on a lock held by another session (BLOQUEIO) appears. The text says how much of the total time went to the Application wait class, as a percentage and in seconds, lists the events of the class that weighed and ends with View profile by class.

The finding appears when the Application class weighs on the total time. The severity is medium and becomes high when the class weighs more. If one event of the class dominates the profile, the report does not repeat the dominant component finding: this finding already covers that time.

These events belong to the Application class, according to the Oracle 19c wait_class:

• enq: TX - row lock contention: a row changed by another transaction that has not finished yet.
• enq: TM - contention: a lock on the whole table.
• enq: UL - contention: a lock requested by the application itself with DBMS_LOCK.
• enq: KO - fast object checkpoint and enq: RO - fast object reuse: writing or discarding the object's blocks before a direct read, TRUNCATE or DROP.
• SQL*Net break/reset to client and SQL*Net break/reset to dblink: a call interrupted or an error returned to the client.

An event the report does not classify appears in the profile as Unclassified and does not count for this finding.

Cause

The session requested a lock that another session held and waited until it was released. The title of the finding speaks of a lock, but the event that weighs tells what happened:

• enq: TX - row lock contention: a long transaction or a late commit holding rows; several sessions updating the same record (counter, balance, next document number); two sessions inserting the same unique key.
• enq: TM - contention: almost always an unindexed foreign key. When the key is changed or the row is deleted in the parent table, Oracle locks the whole child table. LOCK TABLE and DDL also cause this wait.
• enq: UL - contention: the process holding the application lock takes long to release it.
• SQL*Net break/reset to client: not a lock. The call was interrupted or returned an error to the application.

Resolution

Start with the event the finding lists first. The cases go from the most to the least common.

Find the session that holds the lock

While the wait is happening, the blocked session shows who is holding it. Run this in the database of the trace; in RAC, gv$session covers all instances:

select inst_id, sid, serial#, username, event, seconds_in_wait,
       blocking_instance, blocking_session,
       final_blocking_instance, final_blocking_session,
       sql_id, row_wait_obj#
  from gv$session
 where blocking_session is not null
 order by seconds_in_wait desc;

The session in final_blocking_session is the one at the head of the queue. See what it executed last and since when its transaction has been open:

select s.inst_id, s.sid, s.serial#, s.username, s.module, s.status,
       s.sql_id, s.prev_sql_id, t.start_time
  from gv$session s
  join gv$transaction t
    on t.inst_id = s.inst_id
   and t.addr = s.taddr
 where s.inst_id = &inst_id
   and s.sid = &sid;

If the wait is over, ASH keeps blocking_session per sample in v$active_session_history and dba_hist_active_sess_history. ASH requires the Oracle Diagnostics Pack license. Another way out is to also turn on tracing in the session that holds the lock, the next time.

Shorten the transaction that holds the rows

• Commit at the end of each unit of work, without leaving the transaction open while waiting for the user, another system or a file.
• For a record that many sessions update (counter, balance, numbering control), replace the single row with a sequence or with separate rows per session or per range.
• When the rules allow it, lock before changing with select ... for update wait and a timeout, or skip locked in a work queue, instead of waiting with no limit.

Table lock: index the foreign key

For enq: TM - contention, list the foreign keys of the schema without an index that starts with their columns, in the same order. Replace EXEMPLO with the owner of the tables:

select c.owner, c.table_name, c.constraint_name
  from dba_constraints c
 where c.constraint_type = 'R'
   and c.owner = 'EXEMPLO'
   and not exists (
         select 1
           from dba_cons_columns cc
           join dba_ind_columns ic
             on ic.table_owner = cc.owner
            and ic.table_name = cc.table_name
            and ic.column_name = cc.column_name
            and ic.column_position = cc.position
          where cc.owner = c.owner
            and cc.constraint_name = c.constraint_name
          group by ic.index_owner, ic.index_name
         having count(*) = (select count(*)
                              from dba_cons_columns x
                             where x.owner = c.owner
                               and x.constraint_name = c.constraint_name))
 order by c.table_name, c.constraint_name;

Create the index on the child table with the key columns, in the order of the constraint:

create index exemplo.filha_pai_ix on exemplo.filha (pai_id);

On a large table in use, create index ... online (Enterprise Edition) does not lock DML during the creation. To know which table was locked, the object # field of the WAIT line of enq: TM - contention in the trace is its object_id:

grep "enq: TM - contention" arquivo.trc | head

The Oracle documentation on data concurrency and consistency explains DML locks and the effect of an unindexed foreign key.

Same key inserted by two sessions

On the WAIT line of enq: TX - row lock contention, the name|mode field gives the lock mode. 1415053318 is mode 6: the row was changed by another transaction. 1415053316 is mode 4: usually two sessions inserting the same unique or primary key, or a bitmap index under concurrent DML. To count the waits in mode 4:

grep "enq: TX - row lock contention" arquivo.trc | grep -c "name|mode=1415053316"

In mode 4, review how the application generates the key and whether the same load runs twice at the same time. On an OLTP table, replace the bitmap index with a B-tree index.

Other events of the class

• enq: UL - contention: request the DBMS_LOCK locks always in the same order and release them early, with release_on_commit or DBMS_LOCK.RELEASE.
• SQL*Net break/reset to client: go to Oracle errors in the trace. The wait disappears when the error stops happening.
• enq: KO - fast object checkpoint: a direct read of a table that changes all the time. Check the plan of the statement that waited.
• enq: RO - fast object reuse: repeated TRUNCATE or DROP, often inside a loop.

How to confirm

1. Apply one fix at a time: the index, the earlier commit or the lock order.
2. Turn on tracing of the same operation with waits (turn on tracing with DBMS_MONITOR).
3. Upload the new file and open the report.
4. Compare the Application row in By wait class and the event rows in the profile.

Result: the Application class no longer weighs on the total time and the finding disappears from the Diagnosis. In the profile, the event that weighed drops in Occurrences and in Duration (s).

In the report

The sample report has no wait in the Application class; the links open the same sections in it.

• Diagnosis: the finding, with the View profile by class link, which leads to the Application row of By wait class.
• By wait class: the Application row, with the events in Top components. Click the class name to open the card with what it measures and where to look (wait classes).
• Response time profile: each event with Occurrences, Avg (s) and Max (s). A few long waits point to a transaction forgotten open; many short ones, to a contended row (profile).
• Top statements by component and by class: the statements that were blocked (statements by component).
• Objects with the most wait time: the obj# of the contended row or table. In the console, the number opens the object page, with ready-made queries for the columns and indexes of the table (objects).
• Latency distribution: the duration buckets of each event (latency).

If the sessions ended up waiting for each other in a cycle, Oracle returns ORA-00060: see deadlock between sessions.