Oracle enq tx - row lock contention

August 3, 2021

I will explain Enq: TX – Row lock contention Wait Event in Oracle Database in this post.

Enq: TX – Row lock contention

This type of event is application level locking problem that occurs when a user session is trying to update or delete a row held by another session, which is an application design problem.

The TX Lock “Transaction Enqueue”  is used to maintain the integrity of a transaction while it is executing preventing other sessions from modifying the same data at the same time. If contention is occurring, ‘enq: TX – row lock contention’ is likely to become a significant component of the DB time and affect the performance of other sessions. Once you have established that you have high waits for ‘enq: TX – row lock contention’, the next stage is to identify the objects and the SQL involved.

Normally, when a transaction is finished, commit or rollback must be executed to release related rows.

The solution to this problem is that if the session that holds the row is active, then execute commit statement, if it is not active, kill the session or execute rollback the session.

You can find the sql statements that are waiting on enq: TX – row lock contention wait event using the following script.

select v.sql_text,v.sql_fulltext,sub.* from gv$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('02/08/2021  07:30:02','DD/MM/YYYY HH24:MI:SS')
and
to_date('02/08/2021 15:10:02','DD/MM/YYYY HH24:MI:SS')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1 desc) sub where sub.sql_id=v.sql_id;

Find Oracle blocking sessions

You can find historical blocking session and lock with below scripts. Change date time column which is specified with bold character.

select v.sql_text,v.sql_fulltext,sub.* from v$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('27/02/2019 07:30:02','DD/MM/YYYY HH24:MI:SS')
and
to_date('28/02/2019 15:10:02','DD/MM/YYYY HH24:MI:SS')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1 desc) sub where sub.sql_id=v.sql_id;

You can find momentarily blocking session and lock with below scripts.

select s1.username || '@' || s1.machine
|| ' ( THIS SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

You can generate Kill script of Blocking sessions like below.

SELECT 
'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''';',sid,username,serial#,process,NVL (sql_id, 0),
sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait
FROM gv$session s WHERE blocking_session_status = 'VALID'
OR sid IN (SELECT blocking_session
FROM gv$session WHERE blocking_session_status = 'VALID');

Alter system kill session

query result will be like below if you have blocking session in your database. You can execute the following script to kill blocking sessions.

alter system kill session '1,39390,@1';
alter system kill session '2,2536,@1';
alter system kill session '3,26324,@1';

Find and Kill locked Objects

You can find the all locked objects and users for any Schema User with the following script. This scripts generate the kill session scripts also. You can use it to kill these sessions after review the sessions.

SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.inst_id,
b.status,
b.osuser,
b.machine,'alter system kill session ''' || b.sid || ',' || b.serial# || ',@'|| b.inst_id ||''';'
FROM gv$locked_object a, gv$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id and c.owner= 'SCHEMA_NAME';

You can find the all locked objects and users for any Schema User with the following script. This scripts generate the kill session scripts also. You can use it to kill these sessions after review the sessions.

SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.inst_id,
b.status,
b.osuser,
b.machine,'alter system kill session ''' || b.sid || ',' || b.serial# || ',@'|| b.inst_id ||''';'
FROM gv$locked_object a, gv$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id and c.object_name='TABLE_NAME';

Read the following post to learn more details about Wait events in Oracle.

Oracle Wait Events and Their Solutions in Oracle Database

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 1,196 views last month,  1 views today