enq: TX – row lock contention wait event

enq: TX – row lock contention can be regarded as a row lock conflict wait event in operations.

This event indicates that a session is waiting for another session’s row lock. If the waiting time associated with this wait event is very high, it may be responsible for performance issues in the application.

TX enqueue is acquired privately when a transaction initiates the first change, and is held in the process until COMMIT or ROLLBACK is executed.

TX enqueue has several states:

TX Enqueue in Mode 6:
This happens when a session is waiting for a row-level lock held by another session. This occurs when a user deletes or updates a row, while another user wants to do the same.

TX Enqueue in Mode 4:

This happens if the unique index is waiting for a session because of potential copies. If two sessions try to insert the same key value, the second session must wait to see if an ORA-0001 error should occur.

In order to resolve the waiting in both cases, COMMIT or ROLLBACK must be performed in the first session that holds the lock.

You can see which SQL clause is waiting with the following queries:

or

You can see which session blocks to which sessions, using the following query:

This wait event can be resolved by the application-side modification. The information identified by the above queries can be shared to direct the application developer.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *