Oracle技術專題 - Oracle瑣

預備知識：
DDL(DATABASE DEFINITION LANGUAGE)：資料庫定義語言，如create table、drop table.....   
DML(DATABASE MODIFICATION LANGUAGE):資料庫修改語言，如insert、delete、update......
參考資料：Oracle8 Administrator's Guide, Release 8.0
Oracle8 Tuning, Release 8.0

ORACLE鎖具體分為以下幾類：

1.按使用者與系統劃分，可以分為自動鎖與顯示鎖
自動鎖：當進行一項資料庫操作時，預設情況下，系統自動為此資料庫操作獲得所有有必要的鎖。   
顯示鎖：某些情況下，需要使用者顯示的鎖定資料庫操作要用到的資料，才能使資料庫操作執行得更好，顯示鎖是使用者為資料庫物件設定的。

2.按鎖級別劃分，可分為共享鎖與排它鎖
共享鎖：共享鎖使一個事務對特定資料庫資源進行共享訪問——另一事務也可對此資源進行訪問或獲得相同共享鎖。共享鎖為事務提供高併發性，但如拙劣的事務設計+共享鎖容易造成死鎖或資料更新丟失。
排它鎖：事務設定排它鎖後，該事務單獨獲得此資源，另一事務不能在此事務提交之前獲得相同物件的共享鎖或排它鎖。
3.按操作劃分，可分為DML鎖、DDL鎖  
+DML鎖又可以分為，行鎖、表鎖、死鎖  
-行鎖：當事務執行資料庫插入、更新、刪除操作時，該事務自動獲得操作表中操作行的排它鎖。
-表級鎖：當事務獲得行鎖後，此事務也將自動獲得該行的表鎖(共享鎖),以 防止其它事務進行DDL語句影響記錄行的更新。事務也可以在進行 過程中獲得共享鎖或排它鎖，只有當事務顯示使用LOCK TABLE語句顯示的定義一個排它鎖時，事務才會獲得表上的排它鎖,也可使用
LOCK TABLE顯示的定義一個表級的共享鎖(LOCK TABLE具體用法請參 考相關文件)。
-死鎖：當兩個事務需要一組有衝突的鎖，而不能將事務繼續下去的話，就 出現死鎖。
如事務1在表A行記錄#3中有一排它鎖，並等待事務2在表A中記錄#4 中排它鎖的釋放，而事務2在表A記錄行#4中有一排它鎖，並等待事務 1在表A中記錄#3中排它鎖的釋放，事務1與事務2彼此等待，因此就造 成了死鎖。死鎖一般是因拙劣的事務設計而產生。
死鎖只能使用SQL下:alter system kill session 'sid,serial#'；
或者使用相關作業系統kill程式的命令，如UNIX下kill -9 sid,或者 使用其它工具殺掉死鎖程式。
+DDL鎖又可以分為：排它DDL鎖、共享DDL鎖、分析鎖
-排它DDL鎖：建立、修改、刪除一個資料庫物件的DDL語句獲得操作物件的 排它鎖。
如使用alter table語句時，為了維護資料的完成性、一致性、
合法性，該事務獲得一排它DDL鎖。
-共享DDL鎖：需在資料庫物件之間建立相互依賴關係的DDL語句通常需共享
獲得DDL鎖。
如建立一個包，該包中的過程與函式引用了不同的資料庫表，
當編譯此包時，該事務就獲得了引用表的共享DDL鎖。
-分析鎖：ORACLE使用共享池儲存分析與最佳化過的SQL語句及PL/SQL程式，使執行相同語句的應用速度更快。一個在共享池中快取的物件獲得
它所引用資料庫物件的分析鎖。分析鎖是一種獨特的DDL鎖型別，
ORACLE使用它追蹤共享池物件及它所引用資料庫物件之間的依賴 關係。當一個事務修改或刪除了共享池持有分析鎖的資料庫物件
時，ORACLE使共享池中的物件作廢，下次在引用這條SQL/PLSQL語 句時，ORACLE重新分析編譯此語句。
4.內部閂鎖
內部閂鎖：這是ORACLE中的一種特殊鎖，用於順序訪問內部系統結構。
當事務需向緩衝區寫入資訊時，為了使用此塊記憶體區域， ORACLE首先必須取得這塊記憶體區域的閂鎖，才能向此塊記憶體寫入
資訊。

更正一點:
真正的死鎖，包括相互死鎖及迴圈死鎖，oracle會自動監測到，並使得最後申請鎖的請求失敗。
只有非正常終止的session所申請的鎖(我們平時經常講的死鎖實際上值的就是這種型別），才需要手工kill.

第二部分

非正常終止的session，ORACLE會自動回滾其事物（前提是該事物已終止）。包括相互死鎖及迴圈死鎖ORACLE雖會處理，但好象這方面ORACLE處理不是很好，常需自己動手解決掉造成死鎖的程式。




是否可以介紹一下如何定位死鎖的SID，在應用過程中，同樣操作的client很多，往往是一臺機器造成死鎖後，從v$session中看到的terminal都是一樣的（比如：Windows PC），無法分清，那麼這時候怎樣確定呢？


tonnyluo:

我作過試驗, 在網路斷掉（透過拔掉網線）或非正常終止程式（透過task manager強行關閉sql*plus)時，oracle在有限的時間內（我只觀查了5-10分）內，oracle未能對該程式作任何處理。

但是， 如果是相互死鎖或迴圈死鎖，oracle會即時檢測到，並立即使該程式失敗。

以下為相互死鎖時的情形：

[SQL*PLUS (1)]
SQL> SELECT * FROM TEST FOR UPDATE;

C1 C2
--------- ------------------------------
1 中文

[SQL*PLUS (2)]
SQL> SELECT * FROM TEMP FOR UPDATE;

KIND S EMAIL
--------- - ------------------------------
1 a asd


[SQL*PLUS (1)]
SQL> SELECT * FROM TEMP FOR UPDATE;

[... 處於等待狀態]

[SQL*PLUS (2)]
SQL> SELECT * FROM TEST FOR UPDATE;

[... 處於等待狀態]

[此時SQL*PLUS (1)退出等待狀態，且產生以下錯誤]
SQL> SELECT * FROM TEMP FOR UPDATE;
SELECT * FROM TEMP FOR UPDATE
*
ERROR 位於第1行：
ORA-00060: 等待資源時檢測到死鎖


我也一直對ORACLE自動處理所謂死鎖的時間間隔感到疑惑：一是這個時間間隔是可以設定的麼？二是如何確定這樣的SESSION呢？

能否給我介紹一下我上面一個帖子涉及的問題，象你這樣兩個client端使用SQLPLUS發生互鎖或死鎖時，如何確定kill哪個session呢？


正如上面的實驗，互鎖或死鎖時，ORACLE已經解除了這種鎖，所以只要等待某一佔有資源的程式開啟鎖（透過ROLLBACK/COMMIT）即可。


鎖等待的診斷及排除
=====================================

在ORACLE中，為了保證資料的一致性，在對資料庫中的資料進行操作時，系統會進行對資料相應的鎖定。
當程式對所做的修改進行提交(commit)或回滾後(rollback)後，鎖住的資源便會得到釋放，從而允許其它使用者進行操作。
但是，有時，由於程式中的原因，鎖住資源後長時間未對其工作進行提交；或是由於使用者的原因，如調出需要修改的資料後，未及時修改並提交，而是放置於一旁；或是由於客戶伺服器方式中客戶端出現"當機"，而伺服器端卻並未檢測到，從而造成鎖定的資源未被及時釋放，影響到其它使用者的操作。
這時，我們需要迅速地診斷出鎖住資源的使用者並解決其鎖定。

1. 診斷系統中的鎖
為了找出系統中那些使用者鎖住資源以及那些使用者在等待相應的資源，可使用以下語句(其中的/*+ NO_MERGE(..) */千萬不可省略, 否則會很慢)：

-- looklock.sql
-- use the NO_MERGE hints can speed up the query
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"  from   v$session a, v$lock b, v$sqltext c  
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;

執行後的結果如下所示：
Stat USERNAME MACHINE SID SERIAL# Seconds ID1
---- ------------------------------ ---------------- --------- --------- --------- ---------
SQL
----------------------------------------------------------------
Lock CIQUSR CIQDULMACER 12 966 245 131089
select * from c_trade_mode for update

Wait CIQUSR CIQDULMACER 10 735 111 131089
update c_trade_mode set x_name = 'zzz' where x_code='5'

Wait CIQUSR CIQDULMACER 15 106 1094 131089
select * from c_trade_mode for update


其中：
Status有兩種狀態，LOCK表明該程式鎖住了某個資源，WAIT表示該程式正在等待某個資源。
Username, Machine分別為ORACLE使用者名稱及機器名
SID，SERIAL#可用於隨後的解鎖操作
Seconds表示該程式最後一次進行操作至當前的時間(秒)
ID1, 鎖標識。某個LOCK狀態的ID1與某個WAIT狀態的ID1相同，可說明鎖的正是另一個程式等待的。
SQL: 鎖住資源的SQL語句

2. 解除鎖
診斷出鎖的狀態後，若發現該阻塞其它使用者程式的程式是正常操作中，則可通知該使用者對其進行提交，從而達到釋放鎖資源的目的；若為非正常操作，即，其狀態為"inactive"，且其Seconds已為較多長時間，則可執行以下語句將該程式進行清除，系統會自動對其進行回滾，從而釋放鎖住的資源。

alter system kill session 'sid, serial#';

例如:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84274/，如需轉載，請註明出處，否則將追究法律責任。