how to resolve a ‘enq: TX – row lock contention’ event

Senario:two update sessions were operated on the same table,you will see a “TX -row lock” event
in the third console that one session hold the TX lock resource which other is trying to request.
Normally,the requester can do nothing except waiting,the only solution is to revise the app logic on the resource-holder,let’s check out the example.

prepare the materials
——————————————————————————————-
DROP TABLE tx_eg;
CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) ) INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
COMMIT

——————————————————————————————-
–Session#1:
update tx_eg set txt=’Garbage’ where num=1;

–Session#2:
update tx_eg set txt=’Garbage’ where num=1;

–DBA#3
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’;
/*display the TX lock info*/

SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
———- — ———- ———- ———- ———-
147 TX 524297 261 0 6
158 TX 524297 261 6 0

you can see 147 is requesting the resource and 158 is the TX lock resource-holder

select * from v$lock where type=’TX’ and request>0;
/*display who is waiting for a TX lock*/
SQL> select * from v$lock where type=’TX’ and request>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- — ———- ———- ———- ———- ———- ———-
3043444C 30434460 147 TX 524297 261 0 6 4261 0

select * from v$lock where type=’TX’ and lmode>0;
/*display who is holding a TX lock and ‘BLOCK=1’ means it’s blocking a session who wants the TX lock*/

SQL> select * from v$lock where type='TX' and lmode>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- — ———- ———- ———- ———- ———- ———-
2FA16060 2FA16084 158 TX 524297 261 6 0 3988 1

sid=147 is the unlucky session who is blocking and waiting for TX lock.

select sid,p1raw, p2, p3,event from v$session_wait where wait_time=0 and event like ‘%enq%’;
/*display the sid info for a enq: TX – row lock contention who is waiting for the TX lock resource*/

SQL> select sid,p1raw, p2, p3,event from v$session_wait where wait_time=0 and event like ‘%enq%’;

SID P1RAW P2 P3 EVENT
———- ——– ———- ———- —————————————————————-
147 54580006 524297 261 enq: TX – row lock contention

this is a “enq: TX – row lock contention”, the only way to resolve this problem is to notify the unproper behavior of sid=158,commit or rollback the session.

you can use this to show some detail
//
select osuser,machine,program,Module,sid,serial#,event,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

//
select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=147;
/*the waiter is waiting for TX lock in order to lock ‘ROW_WAIT_ROW’
in file ‘ROW_WAIT_FILE’ block ‘ROW_WAIT_BLOCK’ of object ‘ROW_WAIT_OBJ’
this could be used to locate the object in contention
*/

SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=147
2 /

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————— ————-
51931 4 144 0

the sid=147 session is visit the 144 block# of 51931# object.

ok let’s figure out the object which is in contention

SQL> select object_id,object_name from dba_objects where owner=’SCOTT’ and object_id=51931

OBJECT_ID OBJECT_NAME
———- ——————–
51931 TX_EG

table TX_EG of scott was in contention

after locating the object in contention,you can find out the session who need TX lock resource and manually kill it using alter system kill session ‘sid,serial#’

SQL>select sid,serial# from v$session where sid=147;

SQL> select sid,serial# from v$session where sid=147;

SID SERIAL#
———- ———-
147 91
alter system kill session ‘sid,serial#’;
//kill session should be agreed on by developer or well considerated

but the key to take over the issue is to find out the improper logic of the “TX lock holder”

catch the sid=158 SQLTEX

//
SELECT SQL_TEXT
FROM v$sqltext a
WHERE (a.HASH_VALUE, a.ADDRESS) IN
(SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
where b.sid = &sid
and b.serial# = &serial)

get the serial# of sid=158 for locating the sqltext

——————————————————————————————–
SQL> SELECT SQL_TEXT
FROM v$sqltext a
WHERE (a.HASH_VALUE, a.ADDRESS) IN
2 3 4 (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, prev_sql_addr, sql_address)
5 6 FROM v$session b
where b.sid = &sid
7 8 and b.serial# = &serial)
9 /
Enter value for sid: 158
old 7: where b.sid = &sid
new 7: where b.sid = 158
Enter value for serial: 89
old 8: and b.serial# = &serial)
new 8: and b.serial# = 89)

SQL_TEXT
—————————————————————-
update tx_eg set txt=’Garbage’ where num=1

—————————————————————-
add the commit…