Oracle数据库故障排除enq: TX -行锁争用

Question

EE数据库的AWR报告显示enq: TX行锁争用高达80%的DB时间。该应用程序庞大，拥有超过500个表和100.000注册用户，平均约800个并发用户会话。用户抱怨等待时间很长。根据1476298.1的说法，这肯定是一个应用程序问题。

不过，我们仍在努力支持应用程序开发。我认为有两种类型的enq: TX -行锁争用模式4和模式6。我认为这个问题与模式6有关。锁定的表没有外键约束或位图索引。

锁类型：

• 1空空
• 2股SS分股
• 3 SX次排他性
• 4 S共享-> TX行锁争用模式4
  • a.独特索引
  • b.外键
  • c.位图索引

• 5股/分股排他性
• 6X独占-> TX行锁争用模式6
  • TX锁是在事务启动其第一次更改时获取的，并一直保持到事务执行提交或回滚为止。它主要用作排队机制，以便其他会话可以等待事务完成。TX锁的锁名(ID1和ID2)反映了活动事务的事务ID。

我在一次事故报告后收集了以下信息：

会议375、969、975等正在等待第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

他们等了多久？

   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

涉及哪些用户？

SQL> select sid, serial#, username from v$session where sid in (375, 1162);  

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       375      31530 user_1
      1162      46115 user1

数据库中对象的锁：

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

哪个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

等待TX锁的显示会话：

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

持有TX锁的显示会话：

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

按总等待时间排列的前10个前台事件

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           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 
…

从应用程序的角度看，什么可以解决锁问题？作为DBA，我们还需要收集其他信息来解决这个问题吗？

Answer 1

根据我的经验，行锁争用是最难解决的问题之一。例如，这可能是由于应用程序中缺少提交或外键缺少索引所致。以下是一些有助于排除故障的问题：

• 你如何解决这个问题？你会杀死阻塞会话吗？
• 你能重现这个问题吗？你知道是哪种行动引起了这种局面吗？
• 可以在会话级别、数据库级别或应用程序级别添加跟踪吗？