In a busy and healthy database environment, it is unavoidable that multiple sessions will attempt to modify the same row or set of rows simultaneously. This leads to situations where one session must wait for another to finish before proceeding, resulting in the “enq: TX – row lock contention” wait event. While some level of this contention is expected in a high-traffic database, it is crucial to ensure that it does not become excessive, as excessive contention can significantly hinder scalability. This section explores an example of the process for diagnosing this event.

Symptoms in AWR Report

From the “Top 10 Foreground Events by Total Wait Time” section, we observe that:

• The “enq: TX – row lock contention” event occurred 20 times.
• The average wait time exceeded 4 seconds.
• It accounted for 21.6% of total DB time.

Next, we can identify which segments were involved in the contention using the “Segments by Row Lock Waits” subsection of the “Segment Statistics” section.

Here, the “WAREHOUSES” table is responsible for all 20 instances of contention.

Within the “SQL Statistics” section, we find a suspicious SQL statement. Normally, the sum of the %CPU and %IO values for a SQL statement should be close to 100%, as shown by other statements. However, for this query, the sum is only 2.15%, indicating that most of its time is spent waiting on something other than CPU or I/O.

A reasonable assumption is that the missing portion of this SQL’s time is spent on the “enq: TX – row lock contention” wait, based on the following evidence:

• The only significant wait event besides CPU and IO in the top event list is “enq: TX – row lock contention”.
• The total elapsed time of this SQL is 90.74 seconds, which almost matches the 88.4 seconds attributed to the “enq: TX – row lock contention” event.
• The SQL text begins with “UPDATE WAREHOUSES SET WAREHOUS…”, which corresponds to the object identified earlier in the “Segments by Row Lock Waits” section.

Of course, you can use the script $ORACLE_HOME/rdbms/admin/awrsqrpt.sql to generate a SQL-specific AWR report for deeper analysis, but in this case, we will skip that step.

Pinpointing the Locked Rows with ASH

At this stage, we have identified the SQL statement and the object involved in the “enq: TX – row lock contention” wait event. Typically, it is developers, rather than DBAs, are responsible for resolving row lock contention issues by modifying application logic or SQL code. However, DBAs can assist developers further by pinpointing the the specific rows experiencing blocking. This information can help developers determine which part of the application is causing the issue.

When Active Session History (ASH) captures the “enq: TX – row lock contention” wait, it also records four columns: CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, and CURRENT_ROW#. These values can be combined to reconstruct the ROWID of the locked record. The query below demonstrates how to materialize this information:

SELECT ob.owner, 
       ob.object_name,
       dbms_rowid.rowid_create(
           rowid_type => 1,
           object_number => ob.data_object_id,
           relative_fno => sh.current_file#,
           block_number => sh.current_block#,
           row_number => sh.current_row#
       ) AS row_id,
       COUNT(*) AS occurrence_count
  FROM dba_hist_active_sess_history sh
  JOIN dba_objects ob ON sh.current_obj# = ob.object_id
 WHERE sh.snap_id = 2226
   AND sh.event = 'enq: TX - row lock contention'
 GROUP BY ob.owner, 
          ob.object_name, 
          dbms_rowid.rowid_create(
              rowid_type => 1,
              object_number => ob.data_object_id,
              relative_fno => sh.current_file#,
              block_number => sh.current_block#,
              row_number => sh.current_row#
          )
 ORDER BY occurrence_count DESC;

The query output is as follows:

OWNER   OBJECT_NAME   ROW_ID                 OCCURRENCE_COUNT
YUAN    WAREHOUSES    AAAR2FAAFAAJibBABo                    5
YUAN    WAREHOUSES    AAAR2FAAFAAJibBAC6                    2
YUAN    WAREHOUSES    AAAR2FAAFAAJibBACM                    2
YUAN    WAREHOUSES    AAAR2FAAFAAJibBABU                    1
YUAN    WAREHOUSES    AAAR2FAAFAAJibBACU                    1
YUAN    WAREHOUSES    AAAR2FAAFAAJibBAAV                    1

6 rows selected.

Among the captured records, the row with ROWID ‘AAAR2FAAFAAJibBABo’ appears most frequently, with five occurrences. To examine the contents of this record, you can query it directly:

SQL> select * from yuan.warehouses where rowid='AAAR2FAAFAAJibBABo';

In this way, we can identify the most contended row.

This approach illustrates how AWR and ASH complement each other: AWR provides the macro-level view of contention, while ASH drills down to the micro-level details, helping both DBAs and developers resolve lock contention efficiently.