Save big on AI tools today!

Row Lock Contentions – Digging past to resolve permanently

Oracle Database Contentions

Angudi Tech

6/1/20262 min read

Row Lock Contentions – Digging past to resolve permanently.

This represents one of the most prevalent and significant challenges encountered daily in a production environment. At times, we are aware of the exact location of the issue, while at other times, we resort to randomly terminating blocking sessions. Although terminating sessions is not the most effective method of addressing the problem, time constraints often prevent further investigation. Therefore, it is essential to examine past occurrences to gain a clearer understanding of the issue and work towards a permanent resolution.

Use below attached query to find contention details;

SELECT

level,

sample_time,

session_id blocked_sid,

CONNECT_BY_ROOT blocking_session,

sys_connect_by_path (blocking_session, '/') || '/' || session_id blocking_path

FROM

(

SELECT

s.session_id,

s.blocking_session,

s.sample_time

FROM

dba_active_sess_history s

WHERE

s.snap_id BETWEEN '&StSnap' and '&EdSnap'

AND s.blocking_session IS NOT NULL

AND s.event IN (

'enq: TX – row lock contention',

'enq: TM – contention',

'enq: UL – contention',

'enq: TX – allocate ITL entry'

)

UNION

SELECT

s.session_id,

s.blocking_session,

s.sample_time

FROM

dba_active_sess_history s

WHERE

s.snap_id BETWEEN '&StSnap' and '&EdSnap'

AND s.blocking_session IS NULL

AND s.event IN (

'enq: TX – row lock contention',

'enq: TM – contention',

'enq: UL – contention',

'enq: TX – allocate ITL entry'

)

AND EXISTS (

SELECT

'exists'

FROM

DBA_HIST_ACTIVE_SESS_HISTORY bs

WHERE

bs.snap_id BETWEEN '&StSnap' and '&EdSnap'

AND bs.blocking_session = s.session_id

AND bs.sample_time = s.sample_time

AND bs.blocking_session IS NOT NULL

AND bs.event IN (

'enq: TX – row lock contention',

'enq: TM – contention',

'enq: UL – contention',

'enq: TX – allocate ITL entry'

)

)

) CONNECT BY NOCYCLE PRIOR session_id = blocking_session

AND PRIOR sample_time = sample_time

ORDER BY

level DESC,

blocked_sid,

sample_time;

/

This query is based on “dba_active_sess_history” and “dba_hist_active_sess_history”. Since these views are static views and holds only 1 of 10 samples, there are chances a contention with very small duration might not be captured properly.

In every production environment, there are some APIs which causes contentions repeatedly. Take on high count of connections coming from such APIs to start working on the issue. There are chances, once one contention is solved, rest will get befits.

=> Case 1:

In the banking, retail, ERP, and other systems, the processes of End Of Day (EOD) and Start Of Day (SOD) are accelerated through the utilization of parallel resources. Tables remain singular, yet there are multiple access points available. In addition to ensuring ACID compliance, it is essential to implement chunk-based processing and event-driven triggers, so that one pipeline does not interfere with another until the previous one has been completed.

=> Case 2:

The same compliance data table is utilized by distinct modules for their specific tasks simultaneously. The teams that utilize these tables are different. Coordination must be established between these application teams and the DBA team to prioritize access.

=> Case 3:

Direct SQL queries executed on a database, when not properly concluded or disrupted over the network, result in residues within the database. Until PMON is activated (every 60 seconds), these connections hinder other sessions accessing the same data.