Question: I am getting the following wait
event:
enq: TX - row lock contention
I do not understand the reason for this event.
How do I diagnose the "enq: TX - row lock contention" event?
Answer: The "enq: TX - row lock
contention" event often indicates an application level locking
problem. The TX enqueue is the transaction enqueue (a.k.a. enq: TX - contention) and can also be related to
buffer
busy waits, in conditions where multiple transaction attempt to
update the same data blocks. TX enqueue is issued when a transaction
makes its first change, and released when the transaction performs a
COMMIT or ROLLBACK.
The wait event "enq: TX - row lock contention" corresponds to
several situations of TX enqueue. Waits for TX in mode 6 occur when
a session is waiting for a
row level lock that is already held by another session. This
occurs when one application is updating or deleting a row that
another session is also trying to update or delete. This will
generate a wait event "enq: TX - row lock contention". To solve this
particular instance, the session holding the lock must perform a
COMMIT or ROLLBACK.
In mode 4, a TX wait can occur if there is a potential duplicate
in a unique index. When two sessions try to insert the same key
value the second session must wait to see if an ORA-001 should be
raised. This can cause the "enq: TX - row lock contention" wait
event. This wait event can be handled by having the session holding
the lock perform a COMMIT or ROLLBACK.
The wait event "enq: TX - row lock contention" can also occur in
mode 4 when a session is waiting on a shared bitmap index fragment.
Bitmap indexes index key values and a range of ROWIDs. Each
entry in a bitmap index can cover many rows in the actual table. If
two sessions want to update rows covered by the same index fragment,
then the second session must wait for the first session to perform
either a COMMIT or a ROLLBACK by waiting for the TX lock in mode 4,
otherwise it will generate the "enq: TX - row lock contention" wait
event.
To troubleshoot the wait event "enq: TX - row lock contention",
use the following SQL:
For which SQL is currently waiting on:
select
sid,
sql_text
from
v$session s,
v$sql q
where
sid in
(select
sid
from
v$session
where
state in ('WAITING')
and
wait_class != 'Idle'
and
event='enq: TX - row lock contention'
and
(q.sql_id = s.sql_id or q.sql_id =
s.prev_sql_id));
The blocking session is:
select
blocking_session,
sid, serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
