資料庫系統(tǒng)理論與實務(wù)

1、1,,Ch14.交易處理 交易(Transaction)概念介紹多人系統(tǒng)下的交易的並行控制錯誤與例外處理機制Ch15.資料庫的備份與還原資料庫的備份/還原、卸離/附加介紹資料的匯出與匯入Ch16.SQL Server登入與系統(tǒng)安全SQL Server的登入驗證伺服器角色與資料庫角色存取權(quán)限的策略制定,2,交易處理(Transaction Processing),第十四章,3,Chapter 14 Outline,1

2、4-1 交易概念介紹 14-2 錯誤回復(fù) 14-3 交易的並行控制 14-4 SQL Server中交易與記錄檔 14-5 與交易有關(guān)的SQL敘述 14-6 建立一交易 14-7 SQL Server 的3種交易類型 14-8 巢狀式交易 14-9 分散式交易 14-10選擇交易隔離層級 14-11鎖定與死結(jié) 14-12本章總結(jié),4,14-1 交易概念介紹,在資料庫系統(tǒng)中，交易(Transaction)概念提供了

3、一個資料處理的邏輯單元(Logic Unit) 。在該邏輯單元中如果全部執(zhí)行成功，則會確定交易期間所修改的所有資料正式成為資料庫的內(nèi)容；如果有發(fā)生錯誤，則必須取消或回復(fù)該交易期間內(nèi)所有的資料修改。 資料庫系統(tǒng)是一個交易處理系統(tǒng)(Transaction Processing System)，它容許交易同步的被執(zhí)行。,5,14-1 交易概念介紹,多使用者系統(tǒng)（Multiuser System）現(xiàn)今多數(shù)DBMS為多使用者系統(tǒng)，即同時容

4、許一個以上使用者存取資料庫。這是因為DBMS安裝在多元程式（multiprogramming）的作業(yè)系統(tǒng)上，這些作業(yè)系統(tǒng)可以同時存取執(zhí)行一個以上的處理程序以交錯（interleaved）方式分享CPU的使用權(quán)。,6,14-1 交易概念介紹,如下圖所示，有兩個處理程序Pi和Pj以交錯方式被CPU執(zhí)行。在時間t1，Pi被CPU執(zhí)行。當Pi需要資料庫資料，而這些資料不在主記載體時，DBMS必須呼叫作業(yè)系統(tǒng)，處理此I/O要求。,7,14-1 交

5、易概念介紹,這時CPU會中斷Pi的執(zhí)行，再呼叫檔案系統(tǒng)及磁碟管理系統(tǒng)的服務(wù)將資料讀取到主記載體。在此等待的時間中，為了提高CPU之使用率以及系統(tǒng)整體效能（throughput），CPU會執(zhí)行其他處理程序，在本例中Pj會被CPU的排班程式（scheduler）選出而被執(zhí)行，如上頁圖之時間t2所示。假如當Pi之I/O完成後（時間t3），經(jīng)過排班程式的選擇，Pi會重新拿到CPU的使用權(quán)。處理程序Pi和Pj以交錯方式執(zhí)行其他部份，直到結(jié)束為止

6、。當然，一個處理程序的CPU使用時間片斷（time slice）用完也會發(fā)生被置換（swap）的情況。,8,14-1 交易概念介紹,為何交易會與多使用者系統(tǒng)有關(guān)係，因為大多數(shù)的資料庫理論是在交錯同步(Interleaved Concurrency)的假設(shè)下發(fā)展出來的。由於系統(tǒng)在同一時間可能會執(zhí)行多的process，因此如何保證資料讀取、寫入的正確性就是交易探討的重要課題。,9,14-1 交易概念介紹,交易的特性（Properties o

7、f Transaction）單元性（Atomicity）一個交易是一個單元（Atomic）的處理。如果是正常情況，則整個交易應(yīng)完整的被執(zhí)行。否則，發(fā)生任一錯誤情況時，則將交易所做的資料更新復(fù)原到交易開始前狀況（undo）。因此，單元性也稱之為不可部份完成性。 一致性（Consistency ） 一個完整的交易能使資料庫從一個一致性狀態(tài)（consistent state）轉(zhuǎn)換到另一個一致性狀態(tài)。所謂一致性狀態(tài)是指資料庫的資料滿足資

8、料庫綱要所定義的所有限制，例如整合限制條件。,10,14-1 交易概念介紹,隔離性（Isolation）一個執(zhí)行中的交易不應(yīng)被其他同步執(zhí)行中的交易所影響，即交易間應(yīng)具有隔離機制。 耐久性（Durability）一個交易成功的執(zhí)行「認可」（commit）命令後，它對資料庫所作的任何資料更新處理，均反應(yīng)在資料庫中。這些更新動作不會受「認可」後的任何錯誤所影響。,11,14-1 交易概念介紹,交易管理在DBMS是由錯誤回復(fù)(Failur

9、e Recovery)和並行控制(Concurrency Control)兩個主要機制來達成 。而交易管理的主要目的是要維持資料庫中資料的一致性(Consistency)和正確性(Correctness)。 錯誤回復(fù)機制是維持交易處理的「單元性」和「耐久性」 並行控制機制是在維持「隔離性」和「一致性」,12,14-2 錯誤回復(fù),當一個交易提交到DBMS要被執(zhí)行時，DBMS的責(zé)任是要管理此交易順利的執(zhí)行，包括下列的情況： 當交易成功

10、的執(zhí)行「認可」(Commit)後，此交易對資料庫所作的資料更新需反應(yīng)到資料庫中。 在交易執(zhí)行當中不會被其他交易影響。 當錯誤發(fā)生時，交易的操作不會對資料庫產(chǎn)生影響。,13,14-2 錯誤回復(fù),錯誤種類（Types of Failures）：一般可分為3大類，交易錯誤、系統(tǒng)錯誤以及儲存媒體錯誤交易錯誤（Transaction Failures）邏輯錯誤（Logical Error） 資料錯誤（Data Error） 同步控制強

11、制錯誤（Concurrency Control Enforcement Error） 系統(tǒng)錯誤（System Failure）電腦錯誤（Computer Failures） 天災(zāi)（Catastrophes）,14,14-2 錯誤回復(fù),儲存媒體錯誤（Storage Media Failures）主要是指磁碟錯誤。當交易正在讀寫資料庫資料時，發(fā)生磁碟錯誤，以致磁碟中資料可能遺失。針對這種錯誤可使用RAID（Redundant Arr

12、ay of Inexpensive Disk）硬體提高資料安全性。,15,14-2 錯誤回復(fù),錯誤回復(fù)程序（Procedure of Failure Recovery）這個程序DBMS能自動完成而不需任何人工作業(yè)。在說明回復(fù)程序前，我們必須先介紹「檢查點」（checkpoint）事件。「檢查點」是一個系統(tǒng)設(shè)定的時間（例如：5分鐘、10分鐘），DBMS每隔「檢查點」所設(shè)定的時間會處理以下的動作：將主記憶體內(nèi)所有日誌(Log)紀錄存回磁

13、碟中。 將緩衝區(qū)內(nèi)已認可(Commit)交易之資料回存到磁碟。 在日誌中存入檢查點紀錄。,16,14-2 錯誤回復(fù),檢查點記錄的主要功能是幫助回復(fù)程序作Redo和Undo工作，所以包含了系統(tǒng)重啟之後必須處理的資訊。 Redo：當系統(tǒng)發(fā)生錯誤前，交易已作「認可」(Commit)動作，但資料尚未寫到磁碟上，根據(jù)「一致性原則」，從最後一個「檢查點」到「認可」的操作，需要Redo，或稱之為向前恢復(fù)(Roll Forward)。 Undo

14、：當錯誤發(fā)生前，交易還未做「認可」動作，此時需要強制中斷該交易而作Undo的動作，也稱之為Rollback。,17,14-3 交易的並行控制,在一個多元程式(Multiprogramming)或多工(Multi-tasking)的資料庫系統(tǒng)中，必須提供「並行控制」(Concurrency Control)機制，否則會發(fā)生下列問題： 遺失更新問題(Lost Update Program) 暫時更新問題(Temporary Update

15、 Problem) 不可重複讀取(Nonrepeatable Read) 幽靈讀取(Phantom Read),18,14-3 交易的並行控制,遺失更新問題(Lost Update Program)：兩個交易交錯地讀取了共用資料，並且作更新的操作，使得前一個交易的資料更新作業(yè)遺失了 。,交易T1先讀取資料項目X，然後對X作更新處理（X ＝ X ＋ 100）。接著交易T2也讀取X且作X ＝ X － 500的處理。交易T1將X寫入資

16、料庫中。交易T2也將X更新至資料庫。最後交易T1和T2分別執(zhí)行「認可」（Commit）處理。在此情況下，交易T1對資料項目X的更新被交易T2所影響而遺失了。,19,14-3 交易的並行控制,暫時更新問題（Temporary Update Problem） 當交易T1更新了某資料項目X，但在某原因之下這個交易被迫放棄（Abort）。如果資料項目X也被另一個交易T2讀取，這時候交易T2也必須連帶的將X回存成舊有的資料值（即T2也被Ab

17、ort）。,20,14-3 交易的並行控制,暫時更新問題如圖所示交易T1更新了資料X但在正常結(jié)束前發(fā)生了錯誤，使得X必須回復(fù)成舊的資料值。所以交易T2讀取了資料X的「暫時值」（Temporary value）。這個被T2讀取的資料也被稱為「髒資料」（Dirty data），因此該問題也被稱為「髒讀取問題」（Dirty Read Problem）。,21,14-3 交易的並行控制,不可重複讀取(Nonrepeatable Read)：

18、也稱之為不一致分析 (Inconsistent Analysis)，該情況發(fā)生在一交易讀取同一資料數(shù)次以上。,例如：交易1在不同時間點讀取同一筆資料一次以上，而在其中任兩次讀取之間，有另一個交易2改變了該筆資料。此時則因為該資料錄的內(nèi)容被改變了，所以會造成交易1讀取了不同內(nèi)容的同一筆資料,22,14-3 交易的並行控制,幽靈讀取(Phantom Read )：發(fā)生在兩個交易中都對資料表作INSERT或DELETE動作時所發(fā)生。 例如：

19、當交易1讀取一資料表，而交易2在交易1結(jié)束之前新增了一筆資料到該資料表中，則該筆新增資料對交易1而言有如幽靈(Phantom)，因為與第一次所讀到的資料不相同。反之，當交易1讀取一資料表時，而交易2卻刪除一些資料錄，結(jié)果就造成交易1中存在一些不屬於該資料表的幽靈資料錄。,23,14-3 交易的並行控制,並行控制的方法可分為二類：悲觀並行控制(Pessimistic)與樂觀並行控制(Optimistic) 悲觀並行控制：使用者在執(zhí)行某個

20、動作而鎖定某些資源之後，其他使用者就不能執(zhí)行會與該鎖定衝突的動作，直到擁有者解除鎖定為止。主要是用在高度爭用資源的環(huán)境中，以鎖定方式來保護資源的成本，會低於發(fā)生並行衝突時回復(fù)交易的成本。 樂觀並行控制：在該類型並行控制中，使用者在讀取資料時，並不會將資料鎖定。主要是用在低度爭用資源的環(huán)境中，偶爾回復(fù)交易的成本會優(yōu)於讀取時鎖定資料的成本。,24,14-4 SQL Server中交易與記錄檔,交易是一組具有邏輯關(guān)聯(lián)性動作的集合，所有的動

21、作必須全部完成，不然就是必須一件都未發(fā)生，不能夠處於部分完成的狀態(tài)，一交易是不可分割的。例如：將遊戲點數(shù)由A帳號轉(zhuǎn)移至B帳號即是一個交易應(yīng)用，該交易包含了下面兩個動作：因轉(zhuǎn)出動作而修改A帳號資料 因轉(zhuǎn)入動作而修改B帳號資料,25,14-4 SQL Server中交易與記錄檔,如果系統(tǒng)已經(jīng)完成了由A帳號轉(zhuǎn)出的動作，而此時發(fā)生了系統(tǒng)當機，當系統(tǒng)重新開機後，A帳號客戶就會白白的損失一筆遊戲點數(shù)，因為尚未轉(zhuǎn)到B帳號，這樣的系統(tǒng)就不太會有人敢

22、使用了。因此，對於一個交易內(nèi)所有動作的處理，必須是所有動作全部完成，否則就必須是所有動作全都沒有發(fā)生，這就是交易最基本的精神。,26,14-4 SQL Server中交易與記錄檔,交易的三個相關(guān)名詞-- ROLLBACK、SAVEPOINT以及COMMIT ROLLBACK：當系統(tǒng)發(fā)生錯誤的時候，將部分完成交易解除作用，ROLLBACK可以將一交易復(fù)原至一SAVEPOINT或是交易開始時的狀態(tài)。 SAVEPOINT：一個位置定義器，

23、可以於一交易內(nèi)定義SAVEPOINT，當系統(tǒng)發(fā)生錯誤時將交易復(fù)原至一SAVEPOINT。 COMMIT：是存檔的動作，它會將一交易內(nèi)所有對資料庫的修訂儲存至磁碟上。COMMIT可以保証所有的改變將成為資料庫的永久一部分，而且會釋放交易所使用的資源。,27,14-5 與交易有關(guān)的SQL敘述,BEGIN TRANSACTION COMMIT TRANSACTION COMMIT WORK SAVE TRANSACTION ROLL

24、BACK TRANSACTION ROLLBACK WORK,28,14-5 與交易有關(guān)的SQL敘述,BEGIN TRANSACTION敘述語法結(jié)構(gòu)： BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable] 功能：標示一使用者定義交易的起始點引數(shù)： transaction_name：為交易的指定名稱，transaction_name必須遵循識別字的命名

25、規(guī)則，有效位數(shù)為32個文字。@tran_name_variable：為一地區(qū)性變數(shù)，包含一交易的名稱，必須為文字資料型態(tài)。,29,14-5 與交易有關(guān)的SQL敘述,COMMIT TRANSACTION敘述語法結(jié)構(gòu)： COMMIT TRAN[SACTION] [transaction_name | @tran_name_variable]功能：標示一交易的終止點，表示一交易已成功的被執(zhí)行了。引數(shù)：transaction_na

26、me：為前面BEGIN TRANSACTION敘述所指定的交易名稱，但SQL Server會忽略該transaction_name。tran_name_variable：為地區(qū)性一變數(shù)，包含一交易的名稱，必須為文字資料型態(tài)。,30,14-5 與交易有關(guān)的SQL敘述,COMMIT WORK敘述語法結(jié)構(gòu)：COMMIT [WORK]功能：與COMMIT TRANSACTION相同，但是不能接受一使用者定義的交易名稱。COMMIT

27、 [WORK],31,14-5 與交易有關(guān)的SQL敘述,SAVE TRANSACTION敘述語法結(jié)構(gòu)：SAVE TRAN[SACTION] [savepoint_name | @savepoint_variable]功能：在一交易內(nèi)設(shè)定一SAVEPOINT引數(shù)：savepoint_name：為一SAVEPOINT的指定名稱@savepoint_variable：為一變數(shù)，包含一SAVEPOINT的名稱，必須為文字資料型

28、態(tài)。,32,14-5 與交易有關(guān)的SQL敘述,ROLLBACK TRANSACTION敘述語法結(jié)構(gòu)：ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable]功能：將一交易ROLLBACK至交易的起始點或交易內(nèi)的SAVEPOINT引數(shù)：transaction_name：為一於BEGIN

29、 TRANSACTION敘述指定的交易名稱@tran_name_variable：為一地區(qū)性變數(shù)，包含一交易的名稱，必須為文字資料型態(tài)。savepoint_name：為一SAVEPOINT的指定名稱@savepoint_variable：為一地區(qū)性變數(shù)，包含一SAVEPOINT的名稱，必須為文字資料型態(tài)。,33,14-6 建立一交易,如何使用BEGIN TRANSACTION、COMMIT、SAVEPOINT以及ROLLBACK敘

30、述，撰寫一個交易呢？一個交易的結(jié)構(gòu)大致如下：BEGIN TRANSACTIONstatement1statement2. . . . .statementNIF （success） COMMIT TRANSACTIONELSE ROLLBACK TRANSACTIONENDIFBEGIN TRANSACTION標示著一個交易的開始，然後開始一系列的SQL操作敘述，最後檢查

31、是否所有的資料修訂都已成功的被完成，是則執(zhí)行COMMIT敘述將資料寫入磁碟，否則執(zhí)行ROLLBACK敘述將資料回復(fù)至交易發(fā)生前的狀態(tài)。,34,14-6 建立一交易,範例14-6.1：請新增一筆訂單，訂單號碼為200，訂貨日期為2007-04-01，經(jīng)銷商號碼為1019。該訂單包含兩個單項，第一單項產(chǎn)品號碼為1，供應(yīng)商代碼為HSC，數(shù)量為2；第二單項產(chǎn)品號碼為2，供應(yīng)商代碼為HFT，數(shù)量為6。很明顯的必須將3個insert(一筆訂單資料

32、，兩筆訂單細項資料)包裝在一個交易中，因為新增了訂單但是沒有新增細項資料就不正確。但是在新增資料的過程中要一一檢查是否正確才能COMMIT 由於必須依依檢查，因此決定採用try …catch機制,35,14-6 建立一交易,SET IDENTITY_INSERT orders ON;BEGIN TRAN --開始交易begin try --啟動例外處理機制 insert into orders(order_num,order

33、_date,cust_num) values(200,'2007-04-01',1019) --訂單 insert into items(item_num,order_num,prod_num,supp_code,quantity) values(1, 200, 2, 'HSC', 2) --訂單細項一 insert into items(item_num,order_num,prod_num,su

34、pp_code,quantity) values(2, 200, 6, 'HSC', 2) --訂單細項二 COMMIT TRAN --如果沒有錯誤就認可該交易end trybegin catch --發(fā)生錯誤的處理機制 ROLLBACK TRAN --回復(fù)該次交易end catchSET IDENTITY_INSERT orders ON;,36,14-7 SQL Server 的3種交易類型,交易是

35、以每個連線主，所以每個連線都有自己的交易，但是不同的交易卻可能鎖定相同的物件而造成死結(jié)(Deadlock)的狀況 在SQL Server 2005中交易的模式有3種，分別為：外顯交易 (Explicit Transaction)、自動認可交易(Autocommit Transaction)隱含交易 (Implicit Transaction),37,14-7 SQL Server 的3種交易類型,外顯交易：是明確定義交易的啟動與

36、結(jié)束的一種交易，所以亦稱之為明確交易，也就是使用SQL交易敘述所指定的交易。自動認可交易：這是SQL Server 2005預(yù)設(shè)的交易管理模式。每一個T-SQL敘述句都會在完成時認可或回復(fù)。敘述句如果成功地完成則便會自動被認可；如果是遇到任何錯誤則被自動復(fù)原。 隱含交易：隱含交易的意思就是說不用下BEGIN TRANSACTION也會被認為在交易的狀態(tài)下。換句話說，從系統(tǒng)進入隱含交易之後所有執(zhí)行的SQL敘述都會視為同一個交易，直到使

37、用COMMIT或是ROLLBACK時才算結(jié)束一個交易。,38,14-8 巢狀式交易,巢狀式交易(Nested Transaction)是指數(shù)個交易彼此以階層的方式互相連結(jié)，而成為巢狀式的結(jié)構(gòu)。@@TRANCOUNT為一整數(shù)之全域性變數(shù)，它指出目前交易的巢狀階層(Nested Level) BEGIN TRANSACTION敘述會將@@TRANCOUNT加1 ROLLBACK TRANSACTION敘述則會將@@TRANCOUNT

38、設(shè)定為0 COMMIT TRANSACTION或COMMIT WORK敘述會將@@TRANCOUNT減1,39,14-9 分散式交易,分散式交易(Distributed Transaction)是指一交易使用到多個伺服器的資源。SQL Server 2005亦支援分散式交易，允許使用者在一交易中修訂多個伺服器資料庫的資料，一分散式交易會使用到下列系統(tǒng)軟體： 資源管理員(Resource Manager) 交易管理員(Transac

39、tion Manager) 二階段認可(Two-phase Commit/2PC),40,14-9 分散式交易,資源管理員（Resource Manager）資源管理員為管控用於分散式交易資源的系統(tǒng)軟體，一分散式交易是由各別資源管理員的地區(qū)性交易（Local Transaction）所組成的。在分散式交易中，每一資源管理員都必須負責(zé)本身地區(qū)性交易的COMMIT或是ROLLBACK工作，而且要與其他的資源管理員相互協(xié)調(diào)。SQL Ser

40、ver本身即扮演著資源管理員的角色。,41,14-9 分散式交易,交易管理員（Transaction Manager）交易管理員為管控分散式交易COMMIT或ROLLBACK的系統(tǒng)軟體，它會協(xié)調(diào)各個資源管理員，以保證所有包含於一分散式交易的地區(qū)性交易能夠一起被COMMIT或是一起被ROLLBACK，Microsoft的分散式交易管理員（Distributed Transaction Coordinator/DTC）扮演著交易管理員的角

41、色。,42,14-9 分散式交易,二階段認可（Two-phase Commit/2PC）準備階段交易管理員送出一「準備COMMIT」要求給每一資源管理員，每一資源管理員會去執(zhí)行地區(qū)性交易，完成後會只保有最低的必要系統(tǒng)資源（以保證資料的完整性），並傳回交易成功的訊息給交易管理員。確認階段如果每一資源管理員於準備階段都COMMIT成功，則交易管理員會送出「COMMIT命令」給一資源管理員。此時每一資源管理員會立刻將交易記錄為「已完成

42、」，並且釋出佔用的系統(tǒng)資源。如果任一資源管理員於準備階段傳回錯誤訊息，則交易管理員會送出「ROLLBACK命令」給每一資源管理員。,43,14-10 選擇交易隔離層級,交易隔離層級(Isolation Level)是一種機制，讓我們可以動態(tài)的調(diào)整SQL Server於一連線內(nèi)的鎖定狀況。 交易隔離層級將存取共同資料的多個交易加以隔離，以決定一交易接受「不一致資料」的程度。低交易隔離層級可以增加系統(tǒng)並行處理的程度，但是會降低資料存取的

43、正確性。高交易隔離層級能夠讀取正確的資料，但是會降低系統(tǒng)並行處理的程度,44,14-10 選擇交易隔離層級,交易隔離層級決定了SQL Server使用鎖定機制的方式與程度以解決並行性控制上發(fā)生的問題。不淨式讀取(Dirty Read)：或稱中途讀取，意思就是說在交易讀取到未經(jīng)認可的中途資料。 不可重複式讀取(Nonrepeatable Read)：意思就是說在交易中讀取到的資料被其他交易改變了。 幽靈讀取(Phantom Re

44、ad)：意思就是說交易中的資料表被其他交易改變了。,45,14-10 選擇交易隔離層級,SQL Server 2005支援以下ANSI SQL所定義的4種交易隔離層級： 讀取未認可 (Read Uncommitted，隔離交易的最低等級，僅能確保不會讀取實體上已損毀的資料) 讀取認可(Read Committed，SQL Server的預(yù)設(shè)值) 可重覆讀取(Repeatable Read) 可序列化(Serializable，隔

45、離交易的最高等級，使交易完全與其他交易隔離),46,14-10 選擇交易隔離層級,表14-10.1 交易隔離層級的現(xiàn)象,Read Uncommitted與Read Committed屬於樂觀的並行性控制 Repeatable Read與Serializable則屬於悲觀的並行性控制,47,14-10 選擇交易隔離層級,根據(jù)選擇的交易隔離層級，當存取資源時，系統(tǒng)會使用鎖機制(Lock)鎖住該資源，以便免其他交易來存取而發(fā)生一些錯誤的現(xiàn)

46、象?？墒褂肧ET TRANSACTION ISOLATION LEVEL敘述來設(shè)定交易隔離層級 語法結(jié)構(gòu)：SET TRANSACTION ISOLATION LEVEL ｛READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE｝,48,14-10 選擇交易隔離層級,SET LOCK_TIMEOUT敘述語法結(jié)構(gòu)：SET LOCK_TIMEOUT timeo

47、ut_period功能：宣告一SQL敘述等待一鎖機制被釋放的時間(TIME-OUT)，以千分之一秒為單位，-1表示永久的等待，但永久等待很容易造成死結(jié)，此為系統(tǒng)預(yù)設(shè)值 。 範例14-10.1：請設(shè)定TIME-OUT為1,800毫秒，然後查詢該設(shè)定值。set lock_timeout 1800select @@lock_timeout,49,14-11鎖定與死結(jié),SQL Server擁有多個資料粒度(Granulari

48、ty)的鎖定(Lock)，允許交易鎖定不同類型的資源。為了把鎖定的成本降至最低，SQL Server會自動依照交易的適當層級來鎖定資源。鎖定於較小的資料粒度如資料列則可以提高並行性，但如果鎖定太多的資料列則會因為持有更多的鎖定而造成系統(tǒng)的負擔，可能反而不如直接鎖定資料表。鎖定於較大的資料粒度如資料表，從並行性的角度來看會因為鎖定整個資料表而限制其他交易對於該資料表其他部份的存取。但由於必須維持的鎖定較少，因此系統(tǒng)的負擔較低。,50

49、,14-11鎖定與死結(jié),除了資料類型鎖定之外，還提供不同的資料鎖定模式：共用鎖定(S/Shared Lock)：用於不作更新資料的讀取作業(yè)，例如 SELECT敘述句。 獨佔鎖定(X/eXclusive Lock)：用於資料修改動作，例如INSERT、UPDATE以及DELETE。更新鎖定(U/Update Lock)：更新(U)鎖定可防止常見的死結(jié)。 意圖鎖定(I/Intent Lock)：意圖鎖定主要是用來防止其他交易修改較

50、高層級的資源，而導(dǎo)致較低層級的鎖定失效與改進SQL Server偵測資料粒度較高層級鎖定衝突的效率。,51,14-11鎖定與死結(jié),有些鎖定可以同時發(fā)生在資源(換句話說就是不同的交易同時對一個資源發(fā)出鎖定)上，但有些就不行，稱之為鎖定模式的相容性，如下表所列。,52,14-11鎖定與死結(jié),死結(jié)：當二或多個工作各自具有某個資源的鎖定，但其他工作嘗試要鎖定此資源，而造成工作永久封鎖彼此時就發(fā)生死結(jié) ，例如：交易 A 取得資料列 1 的共用(

51、S)鎖定。交易 B 取得資料列 2 的共用(S)鎖定。交易 A 現(xiàn)在要求資料列 2 的獨佔(X)鎖定，但會被封鎖直到交易 B 完成並釋出對資料列 2 的共用(S)鎖定為止 交易 B 現(xiàn)在要求資料列 1 的獨佔(X)鎖定，但會被封鎖直到交易 A 完成並釋出對資料列 1 的共用(S)鎖定為止。,53,14-11鎖定與死結(jié),在這種情況下，等到交易B完成後，交易A才能完成；但交易B被交易A封鎖了。這個狀況也稱為「循環(huán)相依性」(Cyclic

52、 Dependency),54,14-11鎖定與死結(jié),降低死結(jié)發(fā)生率的建議方式： 以相同的順序來存取物件 將交易維持在單一批次中且愈短愈好 在交易中避免使用者互動 使用較低的隔離等級,55,14-12 本章總結(jié),資料庫的異動必須以交易的方式執(zhí)行，這樣才能確保資料的完整性與一致性 交易是一個單一工作單元該工作邏輯單元必須呈現(xiàn)出四種屬性，才能有資格成為一筆交易，分別為：單元性(Atomicity)、一致性(Consistency)

53、、隔離性(Isolation) 以及耐久性(Durability) 屬性，簡稱為ACID 交易管理在DBMS是由錯誤回復(fù)(Failure Recovery)和並行控制(Concurrency Control)兩個主要機制來達成。 「錯誤回復(fù)」機制是維持交易處理的「單元性」和「耐久性」，而「並行控制」機制是在維持「隔離性」和「一致性」 。,56,14-12 本章總結(jié),在一個多元程式(Multiprogramming)或多工(Multi

54、-tasking)的資料庫系統(tǒng)中，必須提供「並行控制」(Concurrency Control)機制，否則將會發(fā)生遺失更新問題(Lost Update Program)、暫時更新問題(Temporary Update Problem，或稱之為中途讀取問題Dirty Read Problem)、不可重複讀取(Nonrepeatable Read)以及幽靈讀取(Phantom Read)等問題。 SQL Server支援4種交易隔離層級

55、-- Read Uncommitted、Read Committed、Repeatable Read以及Serializable,57,14-12 本章總結(jié),Read Uncommitted的層級最低，使用的系統(tǒng)資源最少，但是系統(tǒng)並行處理性最高，Serializable的層級最高，使用的系統(tǒng)資源最多，但是系統(tǒng)並行處理性最低。 SQL Server會每隔一段時間，將那些已被修改過的資料寫入資料庫，這種動作我們稱之為查核點。 所謂的分散

56、式交易(Distributed Transaction)是指一交易使用到多個伺服器的資源，一分散式交易會使用資源管理員(Resource Manager)、交易管理員(Transaction Manager)以及二階段認可(Two-phase Commit/2PC),58,14-12 本章總結(jié),在SQL Server 2005資料鎖定的模式可分為共用鎖定(S/Shared Lock)、獨佔鎖定(X/eXclusive Lock)、更新鎖