Oracle資料完整性和鎖機制簡析

本課內容屬於Oracle進階課程範疇，內容略微偏向理論性，但是與資料庫程式開發和管理、最佳化密切相關；另外本課的部分內容在前面章節已經涉及，請注意理論聯絡實際。

事務

事務(Transaction)從 通訊的角度看：是使用者定義的資料庫操作序列，這些操作要麼全做、要麼全不做，是不可分割的一個工作單元。事務控制語句稱為TCL，一般包括Commit和Rollback。

事務不是程式，事務和程式分屬兩個概念。在RDBMS中，一個事務可以有一條SQL語句、一組SQL語句或者整個程式；一個應用程式又通常包含多個事務。

事務是恢複和並發控制的基本單元。

明確交易和隱含交易

begin

insert into classes_2(bjbh,bjmc,bjms,bzr,ssxb,bjrs,bz)

values ('888','測試班級','測試班級','肖豐斌','003','38','');

commit/rollback;

end ;

insert into classes_2(bjbh,bjmc,bjms,bzr,ssxb,bjrs,bz)

values ('888','測試班級','測試班級','肖豐斌','003','38','');

commit/rollback;

事務的ACID特性和結束方式

事務的ACID特性和結束方式

破壞事務ACID特性的因素包括：

1.多個事務並行運行時，不同事務的操作交叉執行

2.事務在運行過程中被強行終止

事務的結束方式包括：

並行性和一致性

並行性和一致性是針對多使用者、多事務，而非單使用者、單交易資料庫環境的，其含義是在多使用者、多事務環境下，針對同一張資料庫表的資料存在同時更新（含Update和Insert、Delete）的情況。

並行性意味著多使用者能夠同時訪問資料；

一致性意味著每個使用者看到的資料是一致的。

為保證資料的一致性，一般採用了事務隔離機制（事務隔離模型），又稱為事務序列化，用來保證事務盡量按照串列的方式執行。

執行並行事務要防止三種情況：

1.髒讀：事務讀取了另外一個沒有提交的事務的資料(髒資料)；

2.非重複讀：事務重新讀取了以前讀取的資料，結果發現另外一個已經提交的事務已經修改了那些資料；

3. 幻影讀：一個事務重新執行，返回滿足條件的行集資料，結果發現另外一個已經提交的事務插入了滿足條件的其他行的資料。

隔離層未提交的讀模式提交的讀模式重複讀模式序列化模式髒讀可能不可能不可能不可能非重複讀可能可能不可能不可能幻影讀可能可能可能不可能

並行性適用的情況

前提條件是必須是多CPU的伺服器上執行，此時並行性的好處才能顯示出來，單CPU伺服器上實驗並行性反而會降低效能。

•處理對大表(至少100萬行記錄以上)的大資料量查詢

•處理串連非常大的表查詢

•處理建立大索引、大容量資料裝載、匯總計算

•處理Oracle對象間大量資料拷貝等作業

•處理在SMP(對稱式多處理器)或MPP(大規模平行處理)群和彙總(多機器同時訪問同一組磁碟和主要資料庫)的機器上的查詢

•處理存放在分佈於不同磁碟的多個資料檔案中的資料查詢

•處理需要大量輔助記憶體的查詢，如Group by、Order By等

語句級讀一致性和事務級讀一致性

Oracle鎖

什麼是資料庫鎖

鎖是用於防止在訪問相同的資源(包括使用者物件、系統對象、記憶體、Oralce資料字典中的共用資料結構，最常見的是資料庫表Table對象)時 ，事務之間的有害性 互動（存、取）的一種機制。

不同類型的鎖，代表了目前使用者是允許還是阻止其它使用者對相同資源的同時存取，從而確保不破壞系統資料的完整性、一致性和並行性。

加鎖是實現資料庫並發控制的一個非常重要的技術。當事務在對某個資料對象進行操作前，先向系統發出請求，對其加鎖。加鎖後事務就對該資料對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此資料對象進行更新操作。

兩種鎖機制

共用鎖定（Share Lock）：即S鎖，是通過對資料存取的高並行性來實現的。加了共用鎖定的資料庫物件可以被其它事務讀取，但是不能被其它事務修改。

獨佔鎖（Exclusive Lock）：即X鎖，又稱排它鎖，是用來防止同時共用相同資源的鎖。加了獨佔鎖的資料庫物件不能被其它事務讀取和修改。

•鎖在交易保留週期間是被保持的，用來防止包括髒讀、丟失更新和破壞性DLL等互動行為。對一個事務中SQL語句所做的修改只有在該事務提交或復原後才能被其它事務所使用。

•Commit或Rollback執行後，事務所使用的鎖被釋放。

死結

鎖的類型

1.資料鎖(DML鎖)。

用來保證並行訪問資料的完整性。能夠防止同步衝突的DML和DDL操作的破壞性 互動。是Oracle中主要的鎖，又包括表級鎖（TM鎖）和行級鎖（TX鎖、也稱為事務鎖）。

(1).TM鎖

1.資料鎖(DML鎖) 。

(2).TX鎖及DML鎖工作機制

TX鎖是Transaction eXclusive Lock行級排它鎖，對一條記錄加上TX鎖後，其他使用者不能修改、刪除該記錄。

•當Oracle 執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得後，系統再自動申請TX類型的鎖，並將實際鎖定的資料行的鎖標誌位進行置位。 這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X 等多種模式，在資料庫中用0－6來表示。不同的SQL操作產生不同類型的TM鎖。

1.資料鎖(DML鎖)

(2).TX鎖及DML鎖工作機制

•在資料行上只有X鎖（獨佔鎖定）。在 Oracle資料庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或復原。當兩個或多個會話在表的同一條記錄上執行 DML語句時，第一個會話在該條記錄上加鎖，其他的會話處於等待狀態。當第一個會話提交後，TX鎖被釋放，其他會話才可以加鎖。

•當Oracle資料庫發生TX鎖等待時，如果不及時處理常常會引起Oracle資料庫掛起，或導致死結的發生，產生ORA-60的錯誤。這些現象都會對實際應用產生極大的危害，如長時間未響應、大量事務失敗等。

2.字典鎖(DDL鎖)

當 DDL命令發出時，Oracle會自動在被處理的對象上添加DDL鎖定，從而防止對象被其他使用者所修改。當DDL命令結束以後，則釋放DDL鎖定。DDL鎖定不能顯式的被請求，只有當對象結構被修改或者被引用時，才會在對象上添加DDL鎖定。比如建立或者編譯 預存程序時會對引用的對象添加DDL鎖定。在建立視圖時，也會對引用的表添加DDL鎖定等。

在執行DDL命令之前，Oracle會自動添加一個隱式提交命令，然後執行具體的DDL命令，在DDL命令執行結束之後，還會自動添加一個隱式提交命令。實際上，Oracle在執行DDL命令時，都會將其轉換為對資料字典表的DML操作。比如我們發出建立表的DDL命令時，Oracle會

2.字典鎖(DDL鎖)

將表的名稱插入資料字典表tab$裡，同 時將表裡的列名以及列的類型插入col$表裡等。因此，在DDL命令中需要添加隱式的提交命令，從而提交那些對資料字典表的DML操作。即使DDL命令失 敗，它也會發出提交命令。DDL鎖包括三種類型：

•排他的DDL鎖定(Exclusive DDL Lock)
大部分的DDL操作都會在被操作的對象上添加排他的DDL鎖定，從而防止在DDL命令執行期間，對象被其他使用者所修改。當對象上添加了排他的DDL鎖定以後，該對象上不能再添加任何其他的DDL鎖定。如果是對錶進行DDL命令，則其他進程也不能修改表裡的資料。

2.字典鎖(DDL鎖)

•共用的DDL鎖定(Shared DDL Lock )
用來保護被DDL的對象不被其他使用者進程所更新，但是允許其他進程在對象上添加共用的DDL鎖定。如果是對錶進行DDL命令，則其他進程可以同時修改表裡 的資料。比如我們發出create view命令建立視圖時，在視圖的所引用的表(這種表也叫基表)上添加的就是共用的DDL命令。也就是說，在建立視圖時，其他使用者不能修改 基表的結構，但 是可以更新基表裡的資料。

3.內部鎖

內部鎖保護內部資料庫結構，如資料檔案，對使用者是不可見的。

2.字典鎖(DDL鎖)

•可打破的解析鎖定(Breakable Parsed Lock)

在shared pool裡緩衝的SQL遊標或者PL/SQL程式碼都會獲得引用對象上的解析鎖定。如果我們發出DDL命令修改了某個對象的結構時，該對象相關的、位於 shared pool裡的解析鎖定就被打破，從而導致引用了該對象的SQL遊標或者PL/SQL程式碼全都失效。下次再次執行相同的SQL語句時，需要重新解析，這 也就是所謂的SQL語句的reload了。可打破的解析鎖定不會阻止其他的DDL鎖定，如果發生與解析鎖定相衝突的DDL鎖定，則解析鎖定也會被打破

死結的解決

1.尋找鎖

3.Kill 作業系統進程

Orakill 執行個體名 作業系統進程ID

Orakill oralearn 2444

其中oralearn是資料庫sid,244是第二步查出spid

資料完整性

常用的資料完整性約束規則包括：

1.NOT NULL

2.唯一關鍵字

3.主關鍵字

4.外鍵

5.檢查項Check

由於本部分內容再前面的章節中已經穿插講解，本處不再贅述

要點及習題

習題

1.什麼是事務，請解釋什麼是明確交易和隱含交易。

2.事務具有哪四個特性？並行性 事務主要使用的情況是什麼，請舉出四種情況。

3.事務級讀一致性包括那三種類型，並列表說明其相同點和不同點。

4.什麼是資料庫鎖，包括那兩種大的類型？TM鎖又包括那些類型？

5.將表級鎖和行級鎖結合起來，舉例解釋資料鎖的工作機制。

6.什麼是死結？死結解決的步驟是什嗎？

7.為什麼資料庫設計不推薦大量使用外鍵來確保資料完整性？