Oracle ORA-00060：Deadlock detected. More info in file /var/oracle/app/diag/rdbms/...记一次位图索引误用引发卡顿

Oracle ORA-00060：Deadlock detected. More info in file /var/oracle/app/diag/rdbms/…记一次位图索引误用带来的数据库卡顿死锁

数据库死锁带来的性能严重下降

生产环境客户端多发卡顿，排查Oracle日志后发现，数据库中有很多ORA-00060: Deadlock detected. More info in file /var/oracle/app/diag/rdbms/…xxx_ora_80471.trc 记录，初步判断是由资源死锁引起的数据库性能严重下降。

DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-004f001a-0005a437       586     947     X            654    7339           S
TX-0028001d-000b2866       654    7339     X            586     947           S
 
session 947: DID 0001-024A-0000028F	session 7339: DID 0001-028E-00000012 
session 7339: DID 0001-028E-00000012	session 947: DID 0001-024A-0000028F 
 
Rows waited on:
  Session 947: obj - rowid = 0004FE8F - AABP6PAAAAAAAAAAAA
  (dictionary objn - 327311, file - 0, block - 0, slot - 0)
  Session 7339: obj - rowid = 0004FE8F - AABP6PAAAAAAAAAAAA
  (dictionary objn - 327311, file - 0, block - 0, slot - 0)
 
----- Information for the OTHER waiting sessions -----
Session 7339:
  sid: 7339 ser: 32541 audsid: 1484784475 user: 85/XXXXX(Oralce-User)
    flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 654 O/S info: user: oracle, term: UNKNOWN, ospid: 83983
    image: oracle@localhost.localdomain
  client details:
    O/S info: user: ASP.NET v4.0, term: WIN-D74HSI9CPLM, ospid: 75884:77276
    machine: WORKGROUP\WIN-D74HSI9CPLM program: w3wp.exe
    application name: w3wp.exe, hash value=2799981571
  current SQL:
  UPDATE A(Oracle table) SET STATUS = :B5 ,................. WHERE ID = :B1 
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=g2vg0y4nyjkj3) -----
UPDATE A(Oracle table) SET STATUS = 'FINISHED', ................ WHERE ID = :B1 
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x4b5bf89238       377  package body XXX.XXX（Oracle User.package Name）
0x4b5bf89238       399  package body XXX.XXX（Oracle User.package Name）
0x4ad9899c28         8  package body XXX.XXX（Oracle User.package Name）
0x4b7f6a2ab8         1  anonymous block
===================================================

从日志中可以看到是调用的存储过程执行更新状态字段事发生的死锁。

  current SQL:
  UPDATE A(Oracle table) SET STATUS = :B5 ,................. WHERE ID = :B1 

另外可以看到关键的资源等待信息：

Rows waited on:
  Session 947: obj - rowid = 0004FE8F - AABP6PAAAAAAAAAAAA
  (dictionary objn - 327311, file - 0, block - 0, slot - 0)
  Session 7339: obj - rowid = 0004FE8F - AABP6PAAAAAAAAAAAA
  (dictionary objn - 327311, file - 0, block - 0, slot - 0)

从这段代码可以看出，是在rowid为“0004FE8F - AABP6PAAAAAAAAAAAA”的对象资源发生的死锁。
使用

  select sys.dbms_rowid.rowid_object('0004FE8F - AABP6PAAAAAAAAAAAA') from dual;

获取对象id，从而获取对象名称信息

select *from sys.all_objects where  object_id = 'xxxxx'

得知引发死锁的是A表的位图索引I_xxx_status，删除此索引，死锁故障消失。
总结：
1.当发生大范围性能问题时，排查问题的首先应从数据库入手，分析Oracle的alter_xxx.log日志。
2.排除数据库级别的故障后，排查业务系统的性能，把执行耗时的业务逐个分析优化。
3.位图索引不能用于频繁更新值的字段。
4.当字段值域大于7个时，不能使用位图索引。