关于等待多长时间会引发ORA-04021: timeout occurred while waiting to lock object错误的猜测

最新推荐文章于 2024-12-16 17:07:30 发布

msdnchina

最新推荐文章于 2024-12-16 17:07:30 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/msdnchina/article/details/46491125

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文详细探讨了Oracle数据库中遇到的等待超时问题，包括超时原因、实验环境设置、具体操作步骤以及如何通过SQL查询分析锁定对象的持有者和等待者，最后推测超时计数是否在阻塞关系变化时清零。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文的实验环境为：

[oracle@rhel63single ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 07:18:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--- session 71

SQL> set timing on
SQL>  alter procedure lc0019999.dummy11gR2 compile;
 alter procedure lc0019999.dummy11gR2 compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


Elapsed: 00:15:00.09
SQL> show parameter lock

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     128
ddl_lock_timeout                     integer     0
distributed_lock_timeout             integer     60
dml_locks                            integer     1088
lock_name_space                      string
lock_sga                             boolean     FALSE
SQL> select * from v$mystat where rownum<2;  

       SID STATISTIC#      VALUE
---------- ---------- ----------
        71          0          0

Elapsed: 00:00:00.02
SQL>

从上面可以看出，等待15分钟后，由于timeout而失败，不过这个15分钟我没有找到对应的数据库初始化参数。

而在另外的一个实验中，在22分钟之后，才报出的ORA-04021: timeout occurred while waiting to lock object。见如下的实验。

--如下脚本来自：How to Analyze Library Cache Timeout with Associated: ORA-04021 'timeout occurred while waiting to lock object %s%s%s%s%s.' Errors (文档 ID 1486712.1)
--若是lock or pin的 session比较多，如下脚本不太容易看出来哪个是持有者（即:阻塞者）

SQL> select /*+ ordered */ w1.sid  waiting_session,
  2           h1.sid  holding_session,
  3           w.kgllktype lock_or_pin,
  4           w.kgllkhdl address,
  5           decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  6              'Unknown') mode_held,
  7           decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  8            'Unknown') mode_requested
  9     from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 10    where
 11     (((h.kgllkmod != 0) and (h.kgllkmod != 1)
 12        and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
 13      and
 14        (((w.kgllkmod = 0) or (w.kgllkmod= 1))
 15        and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 16     and  w.kgllktype      =  h.kgllktype
 17     and  w.kgllkhdl =  h.kgllkhdl
 18     and  w.kgllkuse     =   w1.saddr
 19     and  h.kgllkuse     =   h1.saddr
 20   /

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
            198              71 Lock 00000000976AE938 Exclusive Exclusive
             14              71 Lock 00000000976AE938 Exclusive Exclusive
             71             136 Pin  00000000976AE938 Share     Exclusive

SQL> /---->此时sid为71的（见本文的最上边）session由于ORA-04021而失败，然后重新执行查询后，构成如下的阻塞者和持有者关系：198变成了持有者。

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
            198             136 Pin  00000000976AE938 Share     Exclusive
             14             198 Lock 00000000976AE938 Exclusive Exclusive

---另外一个session 198

SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       198          0          0

Elapsed: 00:00:00.00
SQL> alter procedure lc0019999.dummy11gR2 compile;
alter procedure lc0019999.dummy11gR2 compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


Elapsed: 00:22:28.64 ----->请注意，消耗了22分钟之后，才报出ORA-04021
SQL>

因此，我猜测，session 198由等待者变为持有者的时候，Library Cache Timeout 的时间计数会清零。