oracle 包 被锁,Oracle包被鎖定的原因分析及解決方案

在數據庫的開發過程中，經常碰到包、存儲過程、函數無法編譯或編譯時會導致PL/SQL 無法響應的問題。碰到這種問題，基本上都要重啟數據庫解決，嚴重浪費開發時間。本文將就產生這種現象的原因和解決方案做基本的介紹。

問題分析

從事數據庫開發的都知道鎖的概念，如：執行 Update Table xxx Where xxx 的時候就會產生鎖。這種常見的鎖在Oracle里面被稱為DML鎖。在Oracle中還有一種DDL鎖，主要用來保證存儲過程、表結構、視圖、包等數據庫對象的完整性，這種鎖的信息可以在DBA_DDL_LOCKS中查到。注意：V$LOCKED_OBJECT記錄的是DML鎖信息，DDL鎖的信息不在里面。

對應DDL鎖的是DDL語句，DDL語句全稱數據定義語句(Data Define Language)。用於定義數據的結構或Schema，如：CREATE、ALTER、DROP、TRUNCATE、COMMENT、RENAME。當我們在執行某個存儲過程、或者編譯它的時候Oracle會自動給這個對象加上DDL鎖，同時也會對這個存儲過程所引用的對象加鎖。

了解了以上知識以后，我們可以得出結論：編譯包長時間無響應說明產生了死鎖。我們可以輕易的讓這種死鎖發生，舉例：

1、     打開一個PL/SQL，開始調試某個函數(假設為：FUN_CORE_SERVICECALL)，並保持在調試狀態

2、     打開一個SQL Window，輸入Select*Fromdba_ddl_locks aWherea.name ='FUN_CORE_SERVICECALL'會發現一行記錄：

3、     打開一個新的PL/SQL，重新編譯這個函數。我們會發現此時已經無法響應了

4、     回到第一個PL/SQL ,重新執行Select*Fromdba_ddl_locks aWherea.name ='FUN_CORE_SERVICECALL'我們將會看到如下記錄：

5、     上述的情況表明發生了鎖等待的情況。

在Oracle中DDL鎖分為：Exclusive DDL Locks(排他的DDL)、Share DDL Locks(共享DDL鎖)、Breakable Parse Locks(可被打破的解析鎖)幾類。篇幅所限，這里就不再詳細介紹了。根據這個例子推理一下，當我們試圖編譯、修改存儲過程、函數、包等對數據對象的時候，如果別人也正在編譯或修改他們時就會產生鎖等待；或者我們在編譯某個存儲過程的時候，如果它所引用的數據庫對象正在被修改應該也會產生鎖等待。這種假設有興趣的兄弟可以測試下，不過比較困難。

解決方案

碰到這種問題，如果知道是被誰鎖定了(可以查出來的)，可以讓對方盡快把鎖釋放掉；實在查不出來只能手工將這個鎖殺掉了。步驟如下：

1、     首先查出哪些進程鎖住了這個對象，語句如下：

Selectb.SID,b.SERIAL#

Fromdba_ddl_locks a, v$session b

Where a.session_id= b.SID

And a.name ='FUN_CORE_SERVICECALL';

2、     執行如下語句殺進程：alter system kill session 'sid,serial#'

3、     執行了以上的語句后，有的時候不一定能夠將進程殺掉。這個時候就需要連到數據庫服務器上殺掉服務器端的進程了，查詢語句：

Selectspid, osuser, s.program

Fromv$session s, v$process p

Where s.paddr= p.addr

And s.sid =(上面查出來的SID)

4、     在服務器上執行如下語句：

#kill -9 spid(UNIX平台)

orakill sid thread(Windows平台SID是Oracle的實例名，thread是上面查出來的SID)

5、     執行完4步以后基本上就可以殺掉這些鎖死的進程了，不放心的話可以再執行第一步確認下。

本文只能是說對這個問題做了初步分析，產生此問題的原因及解決方案涉及到很多Oracle的基本知識。如：DML語句，DDL語句；Oracle鎖的機制；v$session 與 v$process之間關系等。有興趣的兄弟可以更加深入的研究，歡迎跟我交流！

死鎖是數據庫經常發生的問題，數據庫一般不會無緣無故產生死鎖，死鎖通常都是由於我們應用程序的設計本身造成的。產生死鎖時，如何解決呢，下面是常規的解決辦法：

1)執行下面SQL，先查看哪些表被鎖住了:

select b.owner,b.object_name,a.session_id,a.locked_mode

from v$locked_object a,dba_objects b

where b.object_id = a.object_id;

2)查處引起死鎖的會話

select b.username,b.sid,b.serial#,logon_time

from v$locked_object a,v$session b

where a.session_id = b.sid order by b.logon_time;

這里會列出SID

3) 查出SID和SERIAL#：

查V$SESSION視圖:

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='剛才查到的SID';

這一步將得到PADDR

4)查V$PROCESS視圖:

SELECT SPID FROM V$PROCESS WHERE ADDR='剛才查到的PADDR';

這一步得到SPID

5)殺死進程

(1)在數據庫中，殺掉ORACLE進程:

ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

執行存儲過程更新一個表中的數據的時候產生如下的錯誤：

SQL> exec update_jc_kxx_yxrq;

begin update_jc_kxx_yxrq; end;

ORA-20998: Err=-2049,Msg=0-ORA-02049: 超時: 分布式事務處理等待鎖定

ORA-06512: 在"ICUSER.UPDATE_JC_KXX_YXRQ", line 36

ORA-06512: 在line 2

以sys用戶登陸數據庫查詢死鎖

SQL> select username,lockwait,status,machine,program from v$session where sid in

(select session_id from v$locked_object);

USERNAME LOCKWAIT STATUS

------------------------------ ---------------- --------

MACHINE

----------------------------------------------------------------

PROGRAM

------------------------------------------------

icdb

JDBC Thin Client

ICUSER 000000038A37C0C8 ACTIVE

icdb

JDBC Thin Client

說明數據庫有死鎖

然后使用一下語句查找被死鎖的語句

SQL> select sql_text from v$sql where hash_value in

(select sql_hash_value from v$session where sid in

(select session_id from v$locked_object));

SQL_TEXT

--------------------------------------------------------------------------------

update JC_KXX SET LJXF =NVL ( LJXF , 0 ) + :1 , YE =:2 WHERE KH =:3

update jc_kxx set zt='07' where kh='1000530330'

再使用以下語句查找被死鎖的進程

SQL> SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,

l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS

FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

USERNAME OBJECT_ID SESSION_ID SERIAL#

------------------------------ ---------- ---------- ----------

ORACLE_USERNAME OS_USER_NAME PROCESS

------------------------------ ------------------------------ ------------

ICUSER 30523 32 42463

ICUSER oracle

ICUSER 30523 28 25508

ICUSER oracle

ICUSER 30523 76 14781

ICUSER oracle

USERNAME OBJECT_ID SESSION_ID SERIAL#

------------------------------ ---------- ---------- ----------

ORACLE_USERNAME OS_USER_NAME PROCESS

------------------------------ ------------------------------ ------------

ICUSER 30523 24 37522

ICUSER oracle

使用一下語句把死鎖的進程kill

alter system kill session ‘sid,serial#’; (其中sid=l.session_id)

如：SQL> alter system kill session '24,37522';

再次執行存儲過程，錯誤沒有了。語句執行成功！