Tuesday, May 13, 2008

What is enq: TX - row lock contention

Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Several Situation of TX enqueue:
--------------------------------------

1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

3)Waits for TX in mode 4 is also possible if the session is waiting due to 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 bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

Troubleshooting:

for which SQL currently is waiting to,

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,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

Realted Documents

Classes of Oracle Wait Events
ORA-00054: resource busy and acquire with NOWAIT specified
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT

6 comments:

Anonymous said...

This is a wonderful, useful and well written post. Thank you.

Richard said...

Thank you. I got what I wanted.

Nennik&Dony said...

how to avoid this TX - row lock contention?
is it possible that network problem will create this error?

Abhijeet Gandhi said...

Thanks. Great post. helped me with my issue.

Ashok said...

This is well-written post. Was helpful to clarify analysis of an issue. Thank you.

Arju said...

@Nennok,
no it will not be due to your network problem.