了解ORA-00060和trace跟踪文件

怼烎@ 2021-12-19 01:51 480阅读 0赞

朋友前两天问到ORA-00060错误的解决，首先，这种错误都是因为应用设计导致的，当不同的会话处理同一张表的不同行，或者不同表，或者不同事务的时候(这是比较复杂的)，如果出现处理次序的交叉，Oracle就会检测到，进而对其中一个会话抛出ORA-00060，强制回滚，释放锁资源，并将相关信息，写入跟踪文件，Oracle的这种设计，既进行了自恢复，而且记录了相关的信息，便于问题跟踪，值得我们借鉴。

默认设置中，ORA-00060错误对应的跟踪文件包含缓存游标、死锁 图、处理状态、相关会话的当前SQL语句，以及会话等待历史(Oracle 10g及以上的版本)，除了当前的SQL语句和死锁图，其他所有信息都属于接收到ORA-00060错误的会话。Oracle提供了个10027 event，10027事件能让DBA控制ORA-00060错误对应的诊断信息的数量和类型，他可以实现：

*  减小和ORA-00060错误对应的跟踪信息的占用空间，例如，当该问题无法解决的时候，执行该操作，降低业务影响。  
    
 *  在跟踪信息中加入系统状态转储数据或者调用栈，用来寻找死锁的根源。

10027有三个级别，1，2和4，其中第1级只包含一个死锁图和相关会话的当前SQL语句。第2级包含了系统状态转储信息(包含缓冲SQL和所有会话的等待历史)，不仅仅是死锁相关会话的当前SQL语句。第4级包含的调用栈信息用途不大，通过这些信息，能知道检测到死锁的时候，Oracle服务器进程正在执行哪个C函数。

一般使用2级，就可以满足要求。另外，锁会在ORA-00060跟踪文件写好才被释放，所以第1级的10027能确保会话更快地响应。

接下来我们用测试数据，验证下ORA-00060，以及跟踪文件。

创建测试表，增加测试数据，

SQL> create table a(id number, a varchar2(1));Table created.SQL> insert into a values(1, 'a');1 row created.SQL> insert into a values(2, 'b');1 row created.SQL> commit;Commit complete.SQL> select * from a;        ID A---------- -         1 a         2 b
    Table created.
    
    SQL> insert into a values(1, 'a');
    1 row created.
    
    SQL> insert into a values(2, 'b');
    1 row created.
    
    SQL> commit;
    Commit complete.
    
    SQL> select * from a;
            ID A
    ---------- -
             1 a
             2 b

按照如下顺序，进行的操作，就会出现ORA-00060，

t1时间点，会话1：更新id=1的行

t2时间点，会话2：更新id=2的行

此时两个会话，均能正常执行。

t3时间点，会话1：更新id=2的行，该行锁正被会话2占用，因此会话1处于hung，等待中。

t4时间点，会话2：更新id=1的行，该行锁正被会话1占用，此时出现了会话1和会话2锁资源的交叉等待，1等待2，2等待1，Oracle检测到后，就会让会话1抛出ORA-00060的错误，此时会话1更新id=1的行锁未释放，所以会话2更新id=1的操作，当前状态是hung，等待会话1释放锁资源，

![640?wx\_fmt=png][640_wx_fmt_png]

我们设置level=2级的10027 event，

alter system set events '10027 trace name context forever, level 2';system set events '10027 trace name context forever, level 2';

默认设置，和10027设置得到的trace，除了状态转储信息外，其他内容，基本一致，

![640?wx\_fmt=png][640_wx_fmt_png 1]

trace开始就提示了，“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:”，这个错误不是Oracle的，而是因为应用设计导致的。

从死锁图，能看出会话15和会话67之间存在相互依赖的锁循环链，持有和等待X锁。

“Rows waited on”根据rowid信息，能知道两个会话，当前互相等待的行是什么。

“Information for the OTHER waiting sessions”表示未抛出ORA-00060会话正在执行的操作，包括用户名、SID、PID、终端、应用名称、SQL等，

![640?wx\_fmt=png][640_wx_fmt_png 2]

“Information for THIS session”表示抛出ORA-00060的会话正在执行的操作，

![640?wx\_fmt=png][640_wx_fmt_png 3]

接下来“PROCESS STATE”进程状态信息了，能看明白的，就很少了，

![640?wx\_fmt=png][640_wx_fmt_png 4]

通过trace，可以知道发生死锁的两个会话，当前各自执行的操作是什么，因为是应用的设计问题，所以就可以据此，在应用端过代码，找到可能出现操作次序交叉的逻辑，这是问题的关键，因此跟踪文件，对定位死锁问题，还是很重要的。

实测，使用level=2级的10027事件，打印出来的trace大小1.8M，使用默认设置，打印出来的trace大小352K，主要多了系统状态转储信息。

默认设置和10027得到的跟踪文件内容比较，

<table>       
 <tbody> 
  <tr> 
   <td>内容/级别</td> 
   <td>默认值</td> 
   <td>第1级</td> 
   <td>第2级</td> 
   <td>第4级</td> 
  </tr> 
  <tr> 
   <td>缓存游标</td> 
   <td>yes</td> 
   <td>no</td> 
   <td>yes</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>调用栈跟踪</td> 
   <td>no</td> 
   <td>no</td> 
   <td>no</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>死锁图</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>处理状态</td> 
   <td>yes</td> 
   <td>no</td> 
   <td>yes</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>SQL语句</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>yes，对所有会话</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>会话等待历史</td> 
   <td>yes</td> 
   <td>no</td> 
   <td>yes，对所有会话</td> 
   <td>yes</td> 
  </tr> 
  <tr> 
   <td>系统状态</td> 
   <td>no</td> 
   <td>no</td> 
   <td>yes</td> 
   <td>no</td> 
  </tr> 
 </tbody> 
</table>

虽然Oracle抛出ORA-00060，不代表应用不用做什么了，图中，t4时刻，会话1抛出ORA-00060，此时，只强制回滚t3时刻会话1的这条SQL，换句话说，会话2在t4时刻的这条SQL，会处于hung，因为id=1的行锁是会话1在t1时刻得到的，此时并未释放，因此在应用程序的设计中，针对抛出的ORA错误，应该try-catch到，并且显式ROLLBACK，才会让其他会话继续执行，否则这种操作，还是有问题的，

![640?wx\_fmt=png][640_wx_fmt_png]

参考：

1. 关于10027事件，参考《未公开的Oracle数据库秘密》这本书。

2. 《[困扰许久的一个ORA-00060错误解决][ORA-00060]》介绍了个事务锁导致的ORA-00060，这个复杂场景，当时配合开发，鼓捣了很久，才梳理清楚。导致ORA-00060的锁，不仅仅是常见的行或者表，他可以是anything-“任何”，“The resources in question could be anything. In the server, they could be rows, tables, ITL slots, or library cache or row cache locks.”，这话还要仔细品味才行。

[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Paln2Z5GedW8lB7h9Ecia78hWAerdzia35lLGuzqhf2zUaTOBmR1dgyrNicXTCuRf8bpibIyjXXk0bHu1A/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Paln2Z5GedW8lB7h9Ecia78hWBQPa7lGPdxDYdnTIgqhkoAYpibySD6tYFvCNia6lBy3RoVT7UDsRJVDw/640?wx_fmt=png
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Paln2Z5GedW8lB7h9Ecia78hWz9H0Z9Smnusmb7wa5g6nGFrEozpl4o1iakicL0YCVfxeAtn7TNdIKLeA/640?wx_fmt=png
[640_wx_fmt_png 3]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Paln2Z5GedW8lB7h9Ecia78hWSr7Lt25QTgYbiaEGkiawjLsVYhng4hAWjHJkr01zpDpcMvAzuRYOlusg/640?wx_fmt=png
[640_wx_fmt_png 4]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Paln2Z5GedW8lB7h9Ecia78hWRypWYhgSLaIPfhG5MV7SvbrwAptmIer5JFBGj09diaiaXIMxt6IoJPhQ/640?wx_fmt=png
[ORA-00060]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485713&idx=1&sn=03cd988854708d80e5548ae237bfb4d7&chksm=ea814ce2ddf6c5f47b6a3602e837e12ac0963d466b02c2ae8fab9c238b5cdb27db7ada4a3e46&scene=21#wechat_redirect