Tuesday, July 19, 2016

Waits for 'Enq: Tx - Row Lock Contention'

Waits for 'Enq: Tx - Row Lock Contention'


We have experienced a performance issue.  Analysis of the Automatic Workload Repository (AWR) report shows the enqueue event "TX - Row Lock Contention", significant time was waited on this event.
This also generates the deadlock situation if locks are not getting cleared and sometimes ora-600 can be seen in alert.log file.

Large wait events and significant time waited on TX lock, seems poor programming logic or application design issue.
This type of lock only be solved to optimize programming logic and application design issue and transaction control. One needs to work with developer to optimize such large events.

This is obvious that lock can acquire if any active transaction going on. So it is recommended that this transaction should be committing or rollback before some other process/batch acquire lock on same rows.
There are different areas of TX contention, such waits starting wiht 'enq: TX'. Below are some events related to enq: TX wait events,

enq: TX - contention
enq: TX - row lock contention
enq: TX - allocate ITL entry
enq: TX - index contention


Refer below Oracle metalink note for some wait scenarios for enq: TX type lock.
Waits for 'Enq: Tx - Row Lock Contention' - Wait Scenario Examples (Doc ID 62354.1)


Must read below FAQs which helps you to understand basic things of TX and how to find it.

FAQs


Q. What is TX locks?
Ans. A TX is row lock on single row in a table. A transaction acquires a row lock for each row when it modified by INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement

Q. How to find session holding the lock?
Ans. Use following query to identify it.
SELECT * FROM v$lock WHERE type='TX' AND lmode > 0;

Q. How to find session waiting for the lock?
Ans. Use following query to identify it.
SELECT * FROM v$lock WHERE type='TX' AND request>0;

Q. What are the reasons for TX contention?
Ans. The main reasons for contention to occur are:

Waits due to a Row being locked by an active transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block(INITRANS and MAXTRANS)
Waits due to rows being covered by the same BITMAP index fragment

Q. v$ tables, which helps to identify waited sessions and locks,
Ans. You can query on v$session_wait, v$lock, v$session, v$locked_objects to dig the problem.



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

No comments: