Resolving Enq: TX – Row Lock Contention with Real-life Scenario Analysis

Finding SQL Causing Enq: Tx – Row Lock Contention

INTRO

Whenever Enq: TX - row lock contention wait events occur, most database administrators and consultants typically respond by saying, ‘Oh, that’s an application problem. It’s probably due to a commit or rollback not being executed, and the application design should be revised.‘ I don’t like this answer or that kind of attitude. Additionally, there are dozens of blog posts about the Enq: TX - row lock contention wait event, but they all seem to be just copies of each other.”

I will explain the issue to all DBA kinds using a real-life scenario and equip them with the necessary skills to detect problematic SQL statements. From now on, I hope that anyone facing this wait event will no longer say, ‘Oh, that’s an application problem,’ but instead respond with, ‘The application issued this statement, and it has not been committed or rolled back. During that time, another statement was issued and is now waiting in the queue for the lock to be released.’ With these clear and specific answers, software developers will be able to understand the issue much better.

Short Explanation : This type of contention typically happens in situations where two or more sessions are trying to modify the same row in a table and one session must wait until the lock held by another session is released.

In an Oracle database, whenever a session (Session A) modifies a row (e.g., through an UPDATE, DELETE, or INSERT statement), it acquires a row lock to prevent other sessions from modifying the same row until the transaction is committed or rolled back. Contention occurs when another session (Session B) is blocked because it is trying to access a row that is currently locked by Session A. This results in Session B waiting on the Enq: TX – row lock contention event.

Common Causes:

• Concurrent Updates: Two or more sessions trying to update the same row simultaneously.
• Uncommitted Transactions: A session holding a lock due to an uncommitted transaction, causing other sessions to wait.
• Inadequate Application Design: Situations where multiple sessions frequently access and update the same set of rows, leading to contention.

Resolving Row Lock Contention:

• Identify Blocking Session: Use Oracle views like V$SESSION, V$LOCK and V$TRANSACTION to identify the session holding the lock and what it is doing.
• Review Application Logic: Ensure that the application is designed to minimize row lock contention, such as reducing the frequency of updates on the same rows.
• Commit or Rollback Transactions Promptly: Encourage quick commits or rollbacks to reduce the duration for which locks are held.

Real Life Scenario Analysis

As soon as you encounter the Enq: TX - row lock contention wait event, do not immediately kill the session causing the wait. This approach only addresses the immediate issue (saves that moment) but does not solve the root cause.

Step 1: First detect the sql statement in the waiting state (enq: TX – row lock contention) – You may also use Session Browser Menu in TOAD application.

-- Detect sql statement in the waiting state waiting for enq: TX - row lock contention.
SQL> SELECT sql_id 
  FROM gv$session
 WHERE event='enq: TX - row lock contention'
   AND state='WAITING' ;

SQL_ID
------
d7xdz8m6w9b1z

SQL> select sql_fulltext as sql_statement from gv$sql where sql_id='d7xdz8m6w9b1z';

SQL_STATEMENT
-------------
UPDATE DOC_USERS SET BLOCKING_TIME = :1, LOGIN_FAILURES = :2 WHERE ID = :3


-- Details of the object / block / row that caused the wait can usually be found in the ROW_WAIT_* columns of V$SESSION for the waiting session

SQL> SELECT row_wait_obj#       AS object,
       row_wait_file#      AS datafile,
       row_wait_block#     AS datablock,
       row_wait_row#       AS rowinfo
  FROM gv$session
 WHERE event='enq: TX - row lock contention'
   AND state='WAITING' ;

OBJECT	DATAFILE	DATABLOCK	ROWINFO
164251	8		117898		0

SQL> select object_name from dba_objects where object_id = 164251;

OBJECT_NAME
-----------
DOC_USERS

Step 2: We will determine which session this session is waiting for.

INST_ID	        SESS			STATUS		ID1		ID2	LMODE	        REQUEST	        TYPE
2		Holder: 2566	        INACTIVE	12386336	105027	6		0		TX --> Holding			
1		waiter:1637		ACTIVE		12386336	105027	0		6		TX
1		waiter:1784		ACTIVE		12386336	105027	0		6		TX
1		waiter:2596		ACTIVE		12386336	105027	0		6		TX
1		waiter:2838		ACTIVE		12386336	105027	0		6		TX
2		waiter:2838		INACTIVE	12386336	105027	0		6		TX
2		waiter:640		ACTIVE		12386336	105027	0		6		TX
2		waiter:1271		ACTIVE		12386336	105027	0		6		TX
2		waiter:1939		ACTIVE		12386336	105027	0		6		TX
2		waiter:2596		ACTIVE		12386336	105027	0		6		TX
1		waiter:50		INACTIVE	12386336	105027	0		6		TX
2		waiter:50		ACTIVE		12386336	105027	0		6		TX

Step 3: We have the lock holder session. I have session id and instance number, but I also need session address.

SQL> select SADDR from gv$session where sid=2566 and inst_id=2;

SADDR
-----
0000000290D35400

Step 4: We will find out which sql statements executed from this session and not committed or rollbacked. We will basically focus on finding the sql statement (UPDATE,DELETE, MERGE etc.) on DOC_USERS table. It can come from a package / function or executed through a synonym.

SQL> select inst_id,sid,sql_id,sql_text,LAST_SQL_ACTIVE_TIME, CURSOR_TYPE from gv$open_cursor  where saddr='0000000290D35400' and inst_id=2 ;

INST_ID	SADDR            SID	SQL_ID		SQL_TEXT						       LAST_SQL_ACTIVE_TIME CURSOR_TYPE	
------- ---------------- ----   -------------   -------------------------------------------------------------  ------------------- ------------	
2	0000000290D35400 2566	b202bzm06wn66	select besiktas.ID as ID1_176_0_, besiktas.DESCRIPTION as A	7/04/2024 23:36:36 OPEN			
2	0000000290D35400 2566	7bk1fjgug4qnj	select karakartal_.ID as ID1_18_, karakartal_.DESCRIPTION as 	7/04/2024 22:59:22 OPEN			
2	0000000290D35400 2566	9zg9qd9bm4spu	update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0	7/04/2024 12:40:44 OPEN-RECURSIVE	
2	0000000290D35400 2566	9w19mqnst93vx	select squad.ID as ID1_187_, squad.AD as AD2_1			7/04/2024 23:00:00 OPEN			
2	0000000290D35400 2566	6hh4yyh0s1a05	select squad.ID as ID1_187_, squad.AD as AD2_1			7/04/2024 23:00:01 OPEN			
2	0000000290D35400 2566	6jckn90b3pkjz	select kullanici0_.ID as ID1_117_, kullanici0_.DESCRIPTION as A	7/04/2024 23:36:38 OPEN			
2	0000000290D35400 2566	av080ywjp1m1z	select max(tmm.ID) as col_0_0_ from team_squad tmm		7/04/2024 23:00:00 OPEN			
2	0000000290D35400 2566	ca720cv8n1qx0	select karakartal_.ID as ID1_18_, karakartal_.DESCRIPTION as 	7/04/2024 12:41:14 OPEN			
2	0000000290D35400 2566	ab95abfz2a736	select besiktas.ID as ID1_176_, besiktas.DESCRIPTION as ACI	7/04/2024 23:36:38 OPEN			
2	0000000290D35400 2566	czyw4auh9z073	update DOC_USERS  set DESCRIPTION=:1 , ACTIVE=:2 , ACTIVE_UNIT 	7/04/2024 23:36:38 OPEN	
2	0000000290D35400 2566	gqyavk4h9vnsf	BEGIN prepare_all_preqs; END;					7/04/2024 23:36:38 OPEN	

Step 5: We will find sql_fulltext.

SQL> select sql_fulltext as sql_statement from gv$sql where sql_id='czyw4auh9z073';

SQL_STATEMENT
-------------
UPDATE DOC_USERS
   SET DESCRIPTION = :1,
       ACTIVE = :2,
       ACTIVE_UNIT = :3,
       ARCHIVE_ID = :4,
       BARCODE_TYPE = :5,
       BARCODE_SIZE = :6,
       BARCODE_MODEL = :7,
       BLOCKING_TIME  = :8,
       GENDER = :9,
       NOT_CHANGABLE = :10,
       EXT_USER = :11,
       MENU_NUM = :12,
       ESIGN_TYPE = :13
       THEME_CODE = :14
 WHERE ID = :15;

Step 6: Now We have specific information, we may share with the development team.

The application issued UPDATE DOC_USERS SET DESCRIPTION = :1, ACTIVE = :2, ACTIVE_UNIT = :3, ARCHIVE_ID = :4, BARCODE_TYPE = :5, BARCODE_SIZE = :6, BARCODE_MODEL = :7, BLOCKING_TIME = :8, GENDER = :9, NOT_CHANGABLE = :10, EXT_USER = :11, MENU_NUM = :12, ESIGN_TYPE = :13, THEME_CODE = :14 WHERE ID = :15; on 7/04/2024 23:36:38 and it has not been committed or rolled back. During that time, another statement was issued UPDATE DOC_USERS SET BLOCKING_TIME = :1, LOGIN_FAILURES = :2 WHERE ID = :3 and this one is now waiting in the queue for the lock to be released.

That’s it. Now, it will be easier for them to solve.

For more information, Refer to How to Diagnose Issues Where ‘enq: TX – row lock contention’ Waits are Occurring (Doc ID 1986832.1) , Resolving Issues Where ‘enq: TX – row lock contention’ Waits are Occurring (Doc ID 1476298.1) and WAITEVENT: “enq: TX – row lock contention” Reference Note (Doc ID 1966048.1)

Hope it helps.