Oracle Database troubleshooting enq: TX - row lock contention

Question

AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average around 800 concurrent user sessions. Users complain about long waits. This is definitely an application problem also according to Oracle support Doc ID 1476298.1.

Still we are trying to support the application development. I think there are two types of enq: TX - row lock contention mode 4 and mode 6. In my opinion the problem is related to mode 6. The tables locked don’t have foreign key constrains nor bitmap indexes.

Lock Types:

• 1 Null Null
• 2 SS Sub share
• 3 SX Sub exclusive
• 4 S Share -> TX - row lock contention Mode 4
  • a. Unique Index
  • b. Foreign key
  • c. Bitmap indexes
• 5 SSX Share/sub exclusive

• 6 X Exclusive -> TX - row lock contention Mode 6

  • TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.

I collected the following information after an incident report:

The sessions 375, 969, 975 etc. are waiting for session 1162.

@utllockt.sql 
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
1162 None
 375 Transaction Exclusive Exclusive 196610 122968
 969 Transaction Exclusive Exclusive 196610 122968
 975 Transaction Exclusive Exclusive 196610 122968
 1238 Transaction Exclusive Exclusive 196610 122968
 1739 Transaction Exclusive Exclusive 196610 122968

How long are they waiting?

 SQL> select session_id, LAST_CONVERT Sekunden, LAST_CONVERT/60 Minuten from
 dba_locks where Session_id in (375, 1162) ; 
SESSION_ID SEKUNDEN MINUTEN
---------- ---------- ----------
 375 8072 134,533333
 1162 5267 87,7833333
 1162 549 9,15
 1162 576 9,6
 375 576 9,6
 1162 574 9,56666667
 1162 574 9,56666667
 1162 574 9,56666667
 375 2923 48,7166667
 1162 4611 76,85
 1162 576 9,6
 1162 574 9,56666667
 1162 549 9,15
 1162 550 9,16666667
 1162 550 9,16666667
 1162 576 9,6
 1162 576 9,6

What users are involved?

SQL> select sid, serial#, username from v$session where sid in (375, 1162); 
 SID SERIAL# USERNAME
---------- ---------- ------------------------------
 375 31530 user_1
 1162 46115 user1

Locks on objects in the database:

SQL> SELECT a.session_id, a.oracle_username, a.os_user_name, b.object_name
FROM v$locked_object a, sys.all_objects b
WHERE b.object_id = a.object_id
ORDER BY 2, 3; 2 3 4
SESSION_ID ORACLE_USERNAME OS_USER_NAME OBJECT_NAME
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
 975 USER_1 osuser_333 ANXXXXX
 969 USER_1 osuser_355 TREXXX
 1162 USER_1 osuser_555 TGXXXX
 1162 USER_1 osuser_555 REISXXXX
 1162 USER_1 osuser_555 TGXXXXX
 1162 USER_1 osuser_555 DOKXXXXXX
 1162 USER_1 osuser_555 ANXXXXX
 1162 USER_1 osuser_555 ANTRXXXX
 1162 USER_1 osuser_555 ANTRAXXXN
 1162 USER_1 osuser_555 DOKXXX
 1162 USER_1 osuser_555 EAKTEPXXXX
 1162 USER_1 osuser_555 FAHRXX
 1162 USER_1 osuser_555 TRENNXXX
 1162 USER_1 osuser_555 TRENXXXX
 375 USER_1 osuser_321 ANXXXXX

Which SQL?

SQL> select SQL_ID from v$session where sid in (1162,375);
SQL_ID
-------------
413m9wtbz3w4b
SQL> select SQL_TEXT from v$SQL where sql_id='413m9wtbz3w4b';
Update AnXXXX set AnXXXXXX.BELXXX=:xaa Where AnwXXX.F14XX=:xab

Show sessions 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 CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000FFAB126C0 0000000FFAB12738 2031 TX 196610 122968 0 6 1080 0 0
0000000FFAB0CBA0 0000000FFAB0CC18 1739 TX 196610 122968 0 6 1470 0 0
0000000FFAAFDFE8 0000000FFAAFE060 1238 TX 196610 122968 0 6 1426 0 0
0000000FFAB20148 0000000FFAB201C0 975 TX 196610 122968 0 6 1585 0 0
0000000FFAB20548 0000000FFAB205C0 969 TX 196610 122968 0 6 1404 0 0
0000000FFAB1AD40 0000000FFAB1ADB8 375 TX 196610 122968 0 6 1585 0 0

Show sessions holding a TX lock:

SQL> SELECT * FROM v$lock WHERE type='TX' AND lmode > 0;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000FE5372538 0000000FE53725B8 1162 TX 196610 122968 6 0 1631 1 0

Top 10 Foreground Events by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Total Wait Wait % DB Wait
Event Waits Time (sec) Avg(ms) time Class
------------------------------ ----------- ---------- ---------- ------ --------
enq: TX - row lock contention 7 6924,9 989275.83 63.1 Applicat 
...

What could be a solution to the lock problem from an application perspective? Any other information we should collect as a DBA to solve that issue?

• eckes: What you can see is that the number of wait Events is small with a large wait time. This suggests it is not a congestion but a small number of blocking transactions which could be caused by application error, user interaction or maybe a missing dead client detection. If you run across the open transaction you can check if the connection is still visible in netstat on the client machine.

Answer 1

From my experience, row lock contention is one the most difficult problem to troubleshoot. It could be due to a missing commit in the application or missing index on foreign key, for instance. Here are some questions to help to troubleshoot :

• How do you solve this issue ? Do you kill blocking session ?
• Can you reproduce the problem ? Do you know which action raise this situation ?
• Can you add a trace either at session level, database level or application level ?