Resolving Issues Where 'enq: TX - row lock contention' Waits are Occurring

Resolving Issues Where 'enq: TX - row lock contention' Waits are Occurring

TROUBLESHOOTING STEPS

Brief Definition:

This note covers issues where the Guided Resolution Tool indicates contention on "TX" enqueues with waits for 'enq: TX - row lock contention' .

Problem Confirmation:

The list below is a set of data that can be used to confirm that this is the correct solution based upon the symptoms observed:

• 'enq: TX - row lock contention' is a significant component of the DB time
• 'CPU Busy Time' is not greater than 80% of the 'CPU total time'
• The time spent actively in the local database is significant
• Only certain sessions, queries or jobs are experiencing slowness (not throughout the database)

A typical "Top 5 Timed Foreground Events" section of an AWR report might look like the following:

Greater than 90% of the database time is spent waiting for 'enq: TX - row lock contention' and the duration of the waits is long. The database is showing significant activity for the period:

This confirmation data relates to information that can most easily be found in an Automatic Workload Repository (AWR) report from the period in question. For information about collecting and interpreting AWR reports see:

Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point
Document 1359094.1 How to Use AWR Reports to Diagnose Database Performance Issues

Troubleshooting 'enq: TX - row lock contention'  waits

The TX Lock "Transaction Enqueue"  is used to maintain the integrity of a transaction while it is executing preventing other sessions from modifying the same data at the same time. If contention is occurring, 'enq: TX - row lock contention' is likely to become a significant component of the DB time and affect the performance of other sessions. Once you have established that you have high waits for 'enq: TX - row lock contention', the next stage is to identify the objects and the SQL involved.

You can identify the segments involved in the contention by looking at the Segments by Row Lock Waits section in the AWR report:

 >>  >> 

From the "Top 5 Timed Foreground Events" section there were > 8000 waits. From the Segments by Row Lock Waits section, the top 2 objects make up the vast majority of that 8000 and so it is likely that these are the objects involved in the high waits that are seen. From here look for SQL statements that involve modifications to these objects and are waiting for 'enq: TX - row lock contention'. You can identify these statements in an ASH report for the same period or in an ADDM report.

NOTE: TX lock is an application coding, design and usage problem and can ONLY be fixed by changing application code with more frequent and explicit COMMIT statements and any other minor code changes. Oracle Support cannot fix TX lock wait issues other than helping to identify the objects and commands causing the waits. Please work with Developers to fix the code and to alleviate TX lock waits.

The duration of the waits for  'enq: TX - row lock contention' is important:

• If waits are short, this tends to indicate that the concurrency on the objects involved is high and that the locks are doing their job in protecting the data entered by multiple users. If excessive, then you may need to look at the application and determine why so much pressure is being placed on the same objects at the same time and whether this load could not be spread.
  
• If the waits are long, something is holding the locks for a long period, which is inadvisable if concurrent access is expected. If so, this would tend to indicate a more serious issue. Examples of longer duration lock scenarios are covered in:
  
  
  Document 62354.1 TX Transaction locks - Example wait scenarios

Additionally, you can find information relating to other 'enq: TX - ...' variants in the following articles::

Document 873243.1 Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment.
Document 1472175.1 Troubleshooting waits for 'enq: TX - allocate ITL entry'
Document 1946502.1 Resolving Issues Where 'enq: TX - contention' Waits are Occurring

For more details generally on waits for "enq: TX - row lock contention" see:

Document 1966048.1 WAITEVENT: "enq: TX - row lock contention" Reference Note

For information about TX locks, refer to:

Document 197057.1 TX Lock "Transaction Enqueue"

For more general information on locks and locking see:

Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks

Measuring Success

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.

Known Issues

REFERENCES

NOTE:62354.1 - Waits for 'Enq: TX - ...' Type Events - Transaction (TX) Lock Example Scenarios
NOTE:197057.1 - TX Lock "Transaction Enqueue" 
NOTE:1392319.1 - Master Note: Locks, Enqueues and Deadlocks


=================================================================


Note---> This informationmation taken from oracle metalink. all copy rights oracle only.