Save big on AI tools today!

Row Lock Contentions - Finding the culprit

Row Lock Contentions - Finding the culprit

Angudi Tech

4/27/20262 min read

Today, we are addressing a real-life situation in which one of the application modules is unable to complete its process due to being stuck somewhere in the database. The application team has reported that they are unable to progress with their module, which has been running for the last few minutes. According to the application team, this module typically completes every day within seconds.

DBA begins by investigating the database to determine if there are any blocking sessions, including the specified session among those blocking sessions. And DBA finds there are some blocking sessions in the database using below sql,

SELECT sql_id,

row_wait_obj# AS object_id,

row_wait_file# AS datafile_id,

row_wait_block# AS datablock_id,

row_wait_row# AS rowinfo

FROM gv$session

WHERE event='enq: TX - row lock contention'

AND state='WAITING';

Wherein gv$session view is a global dynamic performance view, which is available in RAC environment and is fully based on X$KSUSE internal view. From sql we can find events with 'enq: TX - row lock contention' and state column value to “WAITING”(This means currently session is waiting for something). We here find datafile_id, the datafile which is being used in stuck transaction. Datablock_id and rowinfo gives more indepth information about blocked session. Received object_id makes more sense here, since all queries refer database objects through object name, which we can find as below,

select object_name from dba_objects where object_id = <object_id>;

We can check for all the sessions holding this object through below sql,

select a.sid, a.serial#

from gv$session a, gv$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='<OBECT_NAME>';

Instead, contentions can be found straight way using below sql,

SELECT gvs.inst_id,DECODE (request, 0, 'Holder: ', 'waiter:') || gvl.sid sess,

status,

id1,

id2,

lmode,

request,

gvl.TYPE

FROM gv$lock gvl, gv$session gvs

WHERE (id1, id2, gvl.TYPE) IN (SELECT id1, id2, TYPE

FROM gv$lock

WHERE request > 0)

AND gvl.sid = gvs.sid and gvl.inst_id=gvs.inst_id

ORDER BY id1, request;

In this context, it is essential to emphasize the significance of holding session, clarifying its purpose, and understanding why it has restricted access for others.

Get session address from below sql,

select SADDR from gv$session where sid=<session_id> and inst_id=<inst_id>;

Use above found SADDR to search for the statement causing contention,

select inst_id,sid,sql_id,sql_text,LAST_SQL_ACTIVE_TIME, CURSOR_TYPE from gv$open_cursor where saddr='<SADDR_ID>' and inst_id=INST_ID ;

The found sql details must be shared with application team, so appropriate decision can be taken to release it from the database.