0

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?

  • 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. – eckes Mar 9 at 18:51
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 ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.