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:
Post a Comment