關於等待多長時間會引發ORA-04021: timeout occurred while waiting to lock object錯誤的猜測

關於等待多長時間會引發ORA-04021: timeout occurred while waiting to lock object錯誤的猜測

本文的實驗環境為：

[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 的時間計數會清零。