SQL Server數(shù)據(jù)庫應(yīng)用與開發(fā)：第09章 事務(wù)和鎖

1、SQL Server 2005 數(shù)據(jù)庫應(yīng)用與開發(fā)第09章 事務(wù)和鎖內(nèi)容提要事務(wù)是由一系列的數(shù)據(jù)操作命令組成，是數(shù)據(jù)庫應(yīng)用程序的基本邏輯單元。SQL Server 2005在對(duì)數(shù)據(jù)庫進(jìn)行操作時(shí)，通過事務(wù)來保證數(shù)據(jù)的一致性和完整性。用戶訪問數(shù)據(jù)庫時(shí)，經(jīng)常出現(xiàn)并發(fā)的情況。數(shù)據(jù)庫系統(tǒng)的并發(fā)處理能力是衡量其性能的重要標(biāo)志之一。數(shù)據(jù)庫系統(tǒng)通過適當(dāng)?shù)牟l(fā)控制機(jī)制協(xié)調(diào)并發(fā)操作，保證數(shù)據(jù)的一致性。在SQL Server 2005中，以事務(wù)為基本操作單位，使用鎖來實(shí)現(xiàn)并發(fā)控制。第09章 事務(wù)和鎖9.1 事務(wù)概述9.2 管理事務(wù)9.3 管理并發(fā)數(shù)據(jù)訪問9.4 管理鎖9.5小結(jié)9.1 事務(wù)概述在計(jì)算機(jī)系統(tǒng)設(shè)計(jì)過程中，

2、與一個(gè)商業(yè)事務(wù)相關(guān)的數(shù)據(jù)必須保證可靠性、一致性和完整性，以符合實(shí)際的商業(yè)過程。在SQL Server 2005中，通常由事務(wù)來完成相關(guān)操作，以確保多個(gè)數(shù)據(jù)的修改作為一個(gè)單元來處理。事務(wù)（Transaction）是單個(gè)的工作單元。如果某一事務(wù)執(zhí)行成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù)修改均會(huì)提交，成為數(shù)據(jù)庫中的永久組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所有數(shù)據(jù)修改均被還原。 9.1 事務(wù)概述 9.1.1 事務(wù)的特點(diǎn)定義事務(wù)必須符合如下的典型特征。原子性（Atomicity）：事務(wù)包含的一系列數(shù)據(jù)操作是是一個(gè)整體。執(zhí)行部分操作則數(shù)據(jù)會(huì)回滾到原來的狀態(tài)。一致性(Consistency) ：事務(wù)執(zhí)行

3、完成后，將數(shù)據(jù)庫從一個(gè)一致狀態(tài)轉(zhuǎn)變到另一個(gè)一致狀態(tài)，事務(wù)不能違背定義在數(shù)據(jù)庫中的任何完整性檢查。隔離性(Isolation) ：一一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾持久性(Durability)：要求一旦事務(wù)提交，那么對(duì)數(shù)據(jù)庫所做的修改將是持久的，無論發(fā)生何種機(jī)器和系統(tǒng)故障，都不應(yīng)該對(duì)其有任何影響。9.1 事務(wù)概述9.1.2事務(wù)的分類任何對(duì)數(shù)據(jù)的修改都是在事務(wù)環(huán)境中進(jìn)行的。按照事務(wù)定義的方式可以將事務(wù)分為系統(tǒng)定義事務(wù)和用戶定義事務(wù)。SQL Server 2005支持3種事務(wù)模式分別對(duì)應(yīng)上述兩類事務(wù)：自動(dòng)提交事務(wù)、顯式事務(wù)和隱式事務(wù)。（1）

4、自動(dòng)提交事務(wù)。 SQL Server 2005將一切操作作為事務(wù)處理，它不會(huì)在事務(wù)以外更改數(shù)據(jù)。如果沒有用戶定義事務(wù)，SQL Server會(huì)自己定義事務(wù)，稱為自動(dòng)提交事務(wù)。每條單獨(dú)的語句都是一個(gè)事務(wù)。（2）顯式事務(wù)。顯式事務(wù)是指顯式定義了啟動(dòng)和結(jié)束的事務(wù)。在實(shí)際應(yīng)用中，大多數(shù)的事務(wù)是由用戶來定義的。事務(wù)結(jié)束分為提交（COMMIT）和回滾（ROLLBACK）兩種狀態(tài)。9.1 事務(wù)概述9.1.2事務(wù)的分類（3）隱式事務(wù)。 在隱式事務(wù)中，SQL Server在沒有事務(wù)定義的情況下會(huì)開始一個(gè)事務(wù)，但不會(huì)像在自動(dòng)提交模式中那樣自動(dòng)執(zhí)行COMMIT或ROLLBACK語句，事務(wù)必須顯式結(jié)束。Transact

5、-SQL 腳本使用SET IMPLICIT_TRANSACTIONS ON / OFF語句可以啟動(dòng)/關(guān)閉隱式事務(wù)模式。（4）分布式事務(wù)。一個(gè)比較復(fù)雜的環(huán)境，可能有多臺(tái)服務(wù)器，那么要保證在多服務(wù)器環(huán)境中事務(wù)的完整性和一致性，就必須定義一個(gè)分布式事務(wù)。在分布式事務(wù)中，所有的操作都可以涉及對(duì)多個(gè)服務(wù)器的操作，當(dāng)這些操作都成功時(shí)，那么所有這些操作都提交到相應(yīng)服務(wù)器的數(shù)據(jù)庫中，如果這些操作中有一條操作失敗，那么這個(gè)分布式事務(wù)中的全部操作都被取消。9.1 事務(wù)概述9.1.2事務(wù)的分類跨越兩個(gè)或多個(gè)數(shù)據(jù)庫的單個(gè)數(shù)據(jù)庫引擎實(shí)例中的事務(wù)實(shí)際上也是分布式事務(wù)。該實(shí)例對(duì)分布式事務(wù)進(jìn)行內(nèi)部管理；對(duì)于用戶而言，其操作就

6、像本地事務(wù)一樣。對(duì)于應(yīng)用程序而言，分布式提交必須由事務(wù)管理器管理，以盡量避免出現(xiàn)因網(wǎng)絡(luò)故障而導(dǎo)致事務(wù)由某些資源管理器成功提交，另一些資源管理器回滾的情況。通過準(zhǔn)備階段和提交階段管理提交進(jìn)程可避免這種情況，這稱為兩階段提交。9.2 管理事務(wù) 事務(wù)的基本操作包括啟動(dòng)、保存、提交或回滾等。 9.2.1 啟動(dòng)事務(wù)1顯式事務(wù)的定義顯式事務(wù)需要明確定義事務(wù)的啟動(dòng)。顯式事務(wù)的定義格式如下：BEGIN TRAN | TRANSACTION transaction_name | tran_name_variable WITH MARK description 例9.1 定義一個(gè)事務(wù)，將teaching數(shù)據(jù)庫的s

7、core表中所有選了c05109號(hào)課程的學(xué)生的分?jǐn)?shù)加5分，并提交該事務(wù)。程序代碼如下：USE teachingGODECLARE TranName VARCHAR(20);SELECT TranName = Add_Score;BEGIN TRAN TranName; UPDATE score SET final=final+5 WHERE courseno=c05109;COMMIT TRAN TranName;GO例9.2 定義一個(gè)事務(wù)，將teaching數(shù)據(jù)庫的score表中所有選了c05109號(hào)課程的學(xué)生的分?jǐn)?shù)減5分，并提交該事務(wù)。程序代碼如下：USE teachingGOBEGIN

8、TRAN Subtract_Score WITH MARK NSubtract score of c05109; GO UPDATE score SET final=final-5 WHERE courseno=c05109;COMMIT TRAN Subtract_Score;GO9.2 管理事務(wù)9.2.1 啟動(dòng)事務(wù)2隱式事務(wù)的定義默認(rèn)情況下，隱式事務(wù)是關(guān)閉的。使用隱式事務(wù)需先將事務(wù)模式設(shè)置為隱式事務(wù)模式。不再使用隱式事務(wù)時(shí)，要退出該模式。 SET IMPLICIT_TRANSACTIONS ON | OFF需要注意的是：在使用隱式事務(wù)時(shí)，不要忘記結(jié)束事務(wù)（提交或回滾）。由于不需要顯式的定義

9、事務(wù)的開始，事務(wù)的結(jié)束很容易被忘記，導(dǎo)致失誤長(zhǎng)期運(yùn)行；在連接關(guān)閉時(shí)產(chǎn)生不必要的回滾；或者造成其他連接的阻塞問題。9.2 管理事務(wù)9.2.1 啟動(dòng)事務(wù)2隱式事務(wù)的定義例9.3 分別使用顯式事務(wù)和隱式事務(wù)向表course中插入兩條記錄。程序代碼如下：-first partUSE teachingGOSET NOCOUNT ON;SET IMPLICIT_TRANSACTIONS OFF;GOPRINT NTran count at start = + CAST(TRANCOUNT AS NVARCHAR(10);BEGIN TRANSACTION INSERT INTO course VALUES

10、(c05141,WIN程序設(shè)計(jì),選修,64,4.0); PRINT NTran count at 1st = + CAST(TRANCOUNT AS NVARCHAR(10); INSERT INTO course VALUES(c05142,WEB程序設(shè)計(jì),選修,64,4.0); PRINT NTran count at 2nd = + CAST(TRANCOUNT AS NVARCHAR(10);COMMIT TRANSACTIONGO- second partPRINT NSetting IMPLICIT_TRANSACTIONS ON.;SET IMPLICIT_TRANSACTION

11、S ON;PRINT NUse implicit transactions.;- No BEGIN TRAN needed here.INSERT INTO course VALUES(c05151,管理信息系統(tǒng),選修,48,3.0);PRINT NTran count in 1st implicit transaction = + CAST(TRANCOUNT AS NVARCHAR(10);INSERT INTO course VALUES(c05152,電子商務(wù),選修,48,3.0);PRINT NTran count in 2nd implicit transaction = + CA

12、ST(TRANCOUNT AS NVARCHAR(10);GOCOMMIT TRANSACTION;PRINT NTran count after implicit transaction = + CAST(TRANCOUNT AS NVARCHAR(10);SET IMPLICIT_TRANSACTIONS OFF;GO9.2 管理事務(wù)9.2.2 保存事務(wù)為了提高事務(wù)執(zhí)行的效率，或者進(jìn)行程序的調(diào)試等，可以在事務(wù)的某一點(diǎn)處設(shè)置一個(gè)標(biāo)記（保存點(diǎn)），這樣當(dāng)使用回滾語句時(shí)，可以不用回滾到事務(wù)的起始位置，而是回滾到標(biāo)記所在的位置即保存點(diǎn)。保存點(diǎn)設(shè)置及使用格式：SAVE TRAN | TRANSACTI

13、ON savepoint_name | savepoint_variableROLLBACK TRANSACTION savepoint_name | savepoint_variable例9.4定義一個(gè)事務(wù)，向course表中添加一條記錄，并設(shè)置保存點(diǎn)。然后再刪除該記錄，并回滾到事務(wù)的保存點(diǎn)，提交事務(wù)。程序代碼如下：USE teachingGOBEGIN TRAN INSERT INTO course VALUES(c05139,統(tǒng)一建模語言UML,選修,48,3.0); SAVE TRAN savepoint; DELETE FROM course WHERE courseno=c0513

14、9; ROLLBACK TRAN savepoint;COMMIT TRANGO9.2 管理事務(wù)9.2.3 提交事務(wù)提交事務(wù)標(biāo)志著一個(gè)執(zhí)行成功的隱式事務(wù)或顯式事務(wù)的結(jié)束。事務(wù)提交后，自事務(wù)開始以來所執(zhí)行的所有數(shù)據(jù)修改被持久化，事務(wù)占用的資源被釋放。COMMIT TRAN | TRANSACTIONtransaction_name | tran_name_variable9.2 管理事務(wù)9.2.4 回滾事務(wù)回滾事務(wù)是指清除自事務(wù)的起點(diǎn)或到某個(gè)保存點(diǎn)所做的所有數(shù)據(jù)修改。釋放由事務(wù)控制的資源。ROLLBACK TRAN | TRANSACTIONtransaction_name | tran_nam

15、e_variable | savepoint_name | savepoint_variable 9.2 管理事務(wù)9.2.5 自動(dòng)提交事務(wù)SQL Server 2005在啟動(dòng)顯式事務(wù)，或者隱性事務(wù)模式設(shè)置為打開之前，都將以自動(dòng)提交模式進(jìn)行操作。即：在未啟動(dòng)顯式事務(wù)，或者回滾和關(guān)閉啟動(dòng)顯式事務(wù)時(shí)，SQL Server為自動(dòng)提交模式。在關(guān)閉隱性事務(wù)模式設(shè)置時(shí)，SQL Server 為自動(dòng)提交模式。在自動(dòng)提交模式下，發(fā)生回滾的操作內(nèi)容取決于遇到的錯(cuò)誤的類型。當(dāng)遇到運(yùn)行時(shí)錯(cuò)誤時(shí)，僅回滾發(fā)生錯(cuò)誤的語句；當(dāng)遇到的錯(cuò)誤時(shí)編譯錯(cuò)誤時(shí)，回滾所有的語句。例9.5比較自動(dòng)提交事務(wù)發(fā)生運(yùn)行時(shí)錯(cuò)誤和編譯時(shí)錯(cuò)誤的處理情況

16、。程序代碼如下：-發(fā)生編譯錯(cuò)誤的事務(wù)示例：USE teachingGOINSERT INTO course VALUES(c11111,測(cè)試課程,必修,48,3.0);INSERT INTO course VALUES(c22222,測(cè)試課程,必修,48,3.0);-語法錯(cuò)誤INSERT INTO course VALUSE (c33333,測(cè)試課程,必修,48,3.0); SELECT * FROM course;GO-發(fā)生運(yùn)行時(shí)錯(cuò)誤的事務(wù)示例：USE teachingGOINSERT INTO course VALUES(c11111,測(cè)試課程,必修,48,3.0);INSERT INTO

17、 course VALUES(c22222,測(cè)試課程,必修,48,3.0);-重復(fù)鍵INSERT INTO course VALUES(c11111,測(cè)試課程,必修,48,3.0); SELECT * FROM course;GO9.2 管理事務(wù)9.2.6 事務(wù)嵌套可以在顯式事務(wù)中再定義事務(wù)，稱為嵌套事務(wù)。SQL Server 2005支持嵌套事務(wù)最重要的原因?yàn)榱嗽试S在存儲(chǔ)過程中使用事務(wù)而不必顧及這個(gè)事務(wù)本身是否在另一個(gè)事務(wù)中被調(diào)用的。下面對(duì)于嵌套事務(wù)進(jìn)行如下說明：SQL Server 2005數(shù)據(jù)庫引擎忽略內(nèi)部事務(wù)的提交。根據(jù)最外部事務(wù)結(jié)束時(shí)采取的操作，將提交或者回滾內(nèi)部事務(wù)。如果提交外部事

18、務(wù)，也將提交內(nèi)部嵌套事務(wù)；如果回滾外部事務(wù)，也將回滾所有內(nèi)部事務(wù)。對(duì)COMMIT TRANSACTION的每個(gè)調(diào)用都必須用于事務(wù)最后執(zhí)行的語句。如果嵌套BEGIN TRANSACTION語句，那么COMMIT語句只應(yīng)用于最后一個(gè)嵌套事務(wù)，也就是在內(nèi)部的嵌套事務(wù)。9.2 管理事務(wù)9.2.6 事務(wù)嵌套下面對(duì)于嵌套事務(wù)進(jìn)行如下說明：ROLLBACK TRANSACTION語句的transaction_name transaction_name只能引用外部事務(wù)的事務(wù)名稱。如果在一組嵌套事務(wù)的任意級(jí)別執(zhí)行使用外部事務(wù)名稱的ROLLBACK TRANSACTION transaction_name語句，那

19、么所有嵌套事務(wù)都將回滾。TRANCOUNT函數(shù)可以記錄當(dāng)前事務(wù)的嵌套級(jí)別。每個(gè)BEGIN TRANSACTION語句使TRANCOUNT增加1。每個(gè)COMMIT TRANSACTION語句使TRANCOUNT減去1。如果TRANCOUNT等于0，則表明當(dāng)前操作不在事務(wù)中。默認(rèn)情況下，隱式事務(wù)是不能嵌套的。9.2 管理事務(wù)例9.6 嵌套事務(wù)提交后，外部事務(wù)發(fā)生回滾。程序代碼如下：USE teachingGOBEGIN TRAN PRINT NAfter 1st BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); BEGIN TRAN PRINT NAfte

20、r 2nd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); BEGIN TRAN PRINT NAfter 3rd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); UPDATE course SET cname=SQL Server教程,period=64,credit=4.0 WHERE courseno=c22222; COMMIT TRAN; PRINT After 1st COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); ROLLBACK TRAN; PR

21、INT NAfter ROLLBACK TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); SELECT * FROM course WHERE courseno=c22222;GO9.2 管理事務(wù)例9.7使用TRANCOUNT函數(shù)查看事務(wù)的嵌套級(jí)別。程序代碼如下：PRINT NTrancount before transaction: + CAST(TRANCOUNT As NVARCHAR(10);BEGIN TRAN PRINT NAfter 1st BEGIN TRAN: + CAST(TRANCOUNT As NVARCHAR(10); BEGIN TRA

22、N PRINT NAfter 2nd BEGIN TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); COMMIT TRAN PRINT NAfter 1st COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10);COMMIT TRANPRINT NAfter 2nd COMMIT TRAN: + CAST(TRANCOUNT AS NVARCHAR(10); GO9.3 管理并發(fā)數(shù)據(jù)訪問并發(fā)數(shù)據(jù)訪問是指多個(gè)用戶能夠同時(shí)時(shí)刻訪問某些數(shù)據(jù)。當(dāng)數(shù)據(jù)庫引擎所支持的并發(fā)操作數(shù)較大時(shí)，數(shù)據(jù)庫并發(fā)程序就會(huì)增多?？刂贫鄠€(gè)用戶如何同時(shí)訪問和更改

23、共享數(shù)據(jù)而不會(huì)彼此沖突稱為并發(fā)控制。在SQL Server 2005中，并發(fā)控制是通過用鎖來實(shí)現(xiàn)的。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.1 并發(fā)的影響多個(gè)用戶訪問同一個(gè)數(shù)據(jù)資源時(shí)，如果數(shù)據(jù)存儲(chǔ)系統(tǒng)沒有并發(fā)控制，就會(huì)出現(xiàn)并發(fā)問題，比如修改數(shù)據(jù)的用戶會(huì)影響同時(shí)讀取或修改相同數(shù)據(jù)的其他用戶。下面列出了使用SQL Server時(shí)可能出現(xiàn)的一些并發(fā)問題：（1）更新丟失。當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后根據(jù)最初選定的值更新該行時(shí)，就會(huì)出現(xiàn)更新丟失的問題。每個(gè)事務(wù)都不知道其他事務(wù)的存在。最后的更新將覆蓋其他事務(wù)所做的更新，從而導(dǎo)致數(shù)據(jù)丟失。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.1 并發(fā)的影響（2）不可重復(fù)讀當(dāng)一個(gè)事務(wù)

24、多次訪問同一行且每次讀取不同數(shù)據(jù)時(shí)，會(huì)出現(xiàn)不可重復(fù)讀問題。因?yàn)槠渌聞?wù)可能正在更新該事務(wù)正在讀取的數(shù)據(jù)。（3）幻讀。當(dāng)對(duì)某行執(zhí)行插入或刪除操作，而該行屬于某事務(wù)正在讀取的行的范圍時(shí)，就會(huì)出現(xiàn)幻讀問題。由于其他事務(wù)的刪除操作，使事務(wù)第一次讀取行范圍時(shí)存在的行在后續(xù)讀取時(shí)已不存在。與此類似，由于其他事務(wù)的插入操作，后續(xù)讀取顯示原來讀取時(shí)并不存在的行。（4）臟讀，即讀出的是不正確的臨時(shí)數(shù)據(jù)。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.2 并發(fā)控制的類型計(jì)算機(jī)系統(tǒng)對(duì)并發(fā)事務(wù)遵循可串行化（serializable）的調(diào)度策略，即幾個(gè)并行事務(wù)執(zhí)行是正確的，當(dāng)且僅當(dāng)其結(jié)果與按某一次序串行地執(zhí)行它們的結(jié)果相同時(shí)?？纱行?/p>

25、（serializability）是并行事務(wù)正確性的唯一準(zhǔn)則。從理論上講，在某一事務(wù)執(zhí)行時(shí)禁止其他事務(wù)執(zhí)行的調(diào)度策略一定是可串行化的調(diào)度，這也是最簡(jiǎn)單的調(diào)度策略。但這種方法實(shí)際上是不可行的因?yàn)樗褂脩舨荒艹浞止蚕頂?shù)據(jù)庫資源。目前常用的可串行化調(diào)度策略有悲觀并發(fā)控樂觀并發(fā)控制。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.2 并發(fā)控制的類型（1）悲觀并發(fā)控制。悲觀并發(fā)控制將在事務(wù)執(zhí)行過程中根據(jù)需要鎖定資源，阻止用戶以影響其他用戶的方式修改數(shù)據(jù)。比如用戶執(zhí)行的操作導(dǎo)致應(yīng)用了某個(gè)鎖，則直到這個(gè)鎖的所有者釋放該鎖，其他用戶才能執(zhí)行與該鎖沖突的操作。該方法主要用在數(shù)據(jù)爭(zhēng)奪激烈的環(huán)境中，以及出現(xiàn)并發(fā)沖突時(shí)用鎖保護(hù)數(shù)據(jù)的

26、成本比回滾事務(wù)的成本低的環(huán)境中，因此該方法稱為悲觀并發(fā)控制。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.2 并發(fā)控制的類型（2）樂觀并發(fā)控制。樂觀并發(fā)控制中，用戶讀取數(shù)據(jù)時(shí)不鎖定數(shù)據(jù)。當(dāng)一個(gè)用戶更新數(shù)據(jù)時(shí)，系統(tǒng)將進(jìn)行檢查，查看該用戶讀取數(shù)據(jù)后對(duì)其他用戶是否又更改了該數(shù)據(jù)。如果其他用戶更新了數(shù)據(jù)，將產(chǎn)生一個(gè)錯(cuò)誤。一般情況下，受到錯(cuò)誤信息的用戶將回滾事務(wù)并重新開始。該方法主要用在數(shù)據(jù)爭(zhēng)用不大，且偶爾回滾事務(wù)的成本低于讀取數(shù)據(jù)時(shí)鎖定數(shù)據(jù)的成本的環(huán)境內(nèi)。目前DBMS普遍采用鎖（悲觀并發(fā)控制）來保證調(diào)度的正確性。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.3 事務(wù)的隔離級(jí)別鎖在用作事務(wù)控制機(jī)制時(shí)，可以解決并發(fā)問題。雖然在同一時(shí)

27、間可以運(yùn)行多個(gè)事務(wù)，但鎖只允許事務(wù)獨(dú)立運(yùn)行，運(yùn)行時(shí)互相完全隔離。事務(wù)可以設(shè)置隔離級(jí)別，隔離級(jí)別描述了一個(gè)事務(wù)必須與其他事務(wù)所進(jìn)行的資源或數(shù)據(jù)更改相隔離的程度。隔離級(jí)別從允許并發(fā)負(fù)面影響（如臟讀、幻讀等）的角度進(jìn)行描述。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.3 事務(wù)的隔離級(jí)別SQL Server 2005支持的隔離級(jí)別（從最低到最高）如下：未提交讀(READ UNCOMMITTED)。已提交讀(READ COMMITTED)?？芍貜?fù)讀(REPEATEABLE READ)?？尚蛄谢纱谢?SERIALIZABLE)。隨著隔離級(jí)別的提高，可以更有效地防止數(shù)據(jù)的不一致性。但是，這將降低事務(wù)的并發(fā)處理能

28、力，會(huì)影響多用戶訪問。9.3 管理并發(fā)數(shù)據(jù)訪問9.3.3 事務(wù)的隔離級(jí)別SQL Server 支持的隔離級(jí)別（從低到高）如下：未提交讀(READ UNCOMMITTED)。已提交讀(READ COMMITTED)。可重復(fù)讀(REPEATEABLE READ)。可序列化（可串行化）(SERIALIZABLE)。隨著隔離級(jí)別的提高，可以更有效地防止數(shù)據(jù)的不一致性。但是，這將降低事務(wù)的并發(fā)處理能力，會(huì)影響多用戶訪問。表中列出了隔離級(jí)別以及各個(gè)級(jí)別可以解決的并發(fā)問題。隔離級(jí)別臟讀不可重復(fù)讀幻讀未提交讀是是是已提交讀否是是可重復(fù)讀否否是可串行讀否否否9.3 管理并發(fā)數(shù)據(jù)訪問9.3.3 事務(wù)的隔離級(jí)別隔離

29、級(jí)別可以通過編程方式進(jìn)行設(shè)置，也可以通過使用SQL語法 SET TRANSACTION ISOLATION LEVEL進(jìn)行設(shè)置。下面是使用SET TRANSACTIOIN ISOLATION LEVEL設(shè)置隔離級(jí)別的語法：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED| READ COMMITTED | REPEATABLE READ| SNAPSHOT | SERIALIZABLE 例9.8 將隔離級(jí)別設(shè)置為REPEATABLE READ時(shí)，對(duì)于后續(xù)每個(gè)Transact-SQL語句，SQL Server將所有共享鎖保持到事務(wù)結(jié)束。程序代碼如下

30、：USE teachingGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRAN; SELECT * FROM course; SELECT * FROM score;COMMIT TRAN;GO9.4 管理鎖當(dāng)多個(gè)用戶或應(yīng)用程序同時(shí)訪問同一數(shù)據(jù)時(shí)，鎖可以防止這些用戶或應(yīng)用程序同時(shí)對(duì)數(shù)據(jù)進(jìn)行更改。鎖由SQL Server 2005數(shù)據(jù)庫引擎在內(nèi)部進(jìn)行管理。根據(jù)用戶采取的操作，會(huì)自動(dòng)獲取和釋放鎖。如果在沒有使用鎖時(shí)多個(gè)用戶同時(shí)更新同一數(shù)據(jù)，則數(shù)據(jù)庫內(nèi)的數(shù)據(jù)會(huì)出現(xiàn)邏輯錯(cuò)誤。如果出現(xiàn)這種情況，則對(duì)這些數(shù)據(jù)執(zhí)行的查詢可能會(huì)產(chǎn)生意外的

31、結(jié)果。9.4 管理鎖當(dāng)事務(wù)開始并在事務(wù)內(nèi)以查詢語言、數(shù)據(jù)操作語言（DML）或數(shù)據(jù)定義語言（DDL）執(zhí)行命令時(shí)，SQL Server 2005會(huì)鎖定任何所需的資源以幫助保護(hù)所需隔離級(jí)別的資源。默認(rèn)情況下，行級(jí)鎖定用于數(shù)據(jù)頁，頁級(jí)鎖定用于索引頁。為保留系統(tǒng)資源，當(dāng)超過行鎖數(shù)的可配置閾值時(shí)，鎖管理器將自動(dòng)執(zhí)行鎖升級(jí)。在鎖管理器中可以為每個(gè)會(huì)話分配的最大鎖數(shù)是262143。9.4 管理鎖9.4.1鎖的類型鎖的類型確定并發(fā)事務(wù)可以訪問數(shù)據(jù)的方式。SQL Server根據(jù)必須鎖定的資源和必須執(zhí)行的操作來確定使用那種鎖。表9.3介紹了SQL Server支持的鎖類型。鎖類型說明共享（S）保護(hù)資源，以便只能對(duì)

32、其進(jìn)行讀取訪問。當(dāng)資源上存在共享（S）鎖時(shí)，其他事務(wù)均不能修改數(shù)據(jù)。排他（X）指示數(shù)據(jù)修改，例如插入、更新或刪除。確保不能同時(shí)對(duì)同一資源進(jìn)行多個(gè)更新。更新（U）防止常見形式的死鎖。每次只有一個(gè)事務(wù)可以獲得資源上的U鎖。如果事務(wù)修改資源，則U鎖將轉(zhuǎn)換為X鎖。架構(gòu)在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型時(shí)架構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定性（Sch-S）。意向建立鎖層次結(jié)構(gòu)。最常見的意向鎖類型是IS、IU和IX。這些鎖指示事務(wù)正在處理層次結(jié)構(gòu)中較低級(jí)別的某些資源，而不是所有資源。較低級(jí)別的資源將具有S、U或X鎖。9.4 管理鎖9.4.2可以鎖定的資源可以鎖定的資源指鎖定的粒度或發(fā)生鎖定的級(jí)別。默

33、認(rèn)情況下，行級(jí)鎖用于數(shù)據(jù)頁，頁級(jí)鎖用于索引頁。為保留系統(tǒng)資源，當(dāng)超過行鎖數(shù)的可配置閾值時(shí)，鎖管理器將自動(dòng)執(zhí)行鎖升級(jí)。在較小粒度（如行級(jí)）上鎖定會(huì)提高并發(fā)性，但開銷更多，因?yàn)槿绻i定許多行，則必須持有更多的鎖。在較大粒度（如表級(jí)）上鎖定會(huì)降低并發(fā)性，因?yàn)殒i定整個(gè)表會(huì)限制其他事務(wù)對(duì)該表任何部分的訪問。但是，此級(jí)別上的鎖定開銷較少，因?yàn)榫S護(hù)的鎖較少。可以鎖定的資源主要包括行、數(shù)據(jù)頁、架構(gòu)、表和數(shù)據(jù)庫等。 鎖說明RID行標(biāo)識(shí)符，用于鎖定表內(nèi)的單個(gè)行。PAG一個(gè)8K的數(shù)據(jù)頁或索引頁。TAB整個(gè)表，包括所有數(shù)據(jù)和索引。MD表的元數(shù)據(jù)。用于保護(hù)表架構(gòu)。DB數(shù)據(jù)庫。9.4 管理鎖9.4.3鎖的兼容性如果某事務(wù)

34、已鎖定一個(gè)資源，而另一個(gè)事務(wù)又需要訪問該資源，那么SQL Server會(huì)根據(jù)第一個(gè)事務(wù)所用鎖定模式的兼容性確定是否授予第二個(gè)鎖。對(duì)于已鎖定的資源，只能施加兼容類型的鎖。資源的鎖定模式有一個(gè)兼容性矩陣，可以顯示哪些鎖與在同一資源上獲取的其他鎖兼容，并按照鎖強(qiáng)度遞增的順序列出這些鎖。表中顯示了請(qǐng)求的鎖定模式及其與現(xiàn)有鎖定模式的兼容性。請(qǐng)求的模式ISSUIXSIXX意向共享（IS）是是是是是否共享（S）是是是否否否更新（U）是是否否否否意向排他（IX）是否否是否否意向排他共享（SIX）是否否否否否排他（X）否否否否否否9.4 管理鎖9.4.4 死鎖SQL Server 2005對(duì)并發(fā)事務(wù)的處理，使用

35、任何方案都會(huì)導(dǎo)致死鎖（deadlock）問題。在下面兩種情況下，可以發(fā)生死鎖。第1種情況是，兩個(gè)事務(wù)分別鎖定了兩個(gè)單獨(dú)的對(duì)象，這時(shí)每一個(gè)事務(wù)都要求在另外一個(gè)事務(wù)鎖定的對(duì)象上獲得一個(gè)鎖，結(jié)果是每一個(gè)事務(wù)都必須等待另外一個(gè)事務(wù)釋放占有的鎖，此時(shí)就發(fā)生了死鎖。這種死鎖是最典型的死鎖形式。第2種情況是，在一個(gè)數(shù)據(jù)庫中，有若干長(zhǎng)時(shí)間運(yùn)行的事務(wù)并行的執(zhí)行操作，查詢分析器處理非常復(fù)雜的查詢時(shí)，例如連接查詢，由于不能控制處理的順序，有可能發(fā)生死鎖。9.4 管理鎖9.4.4 死鎖死鎖是指事務(wù)永遠(yuǎn)不會(huì)釋放它們所占用的鎖，死鎖中的兩個(gè)事務(wù)都將無限期等待下去。SQL Server 2005的SQL Server Da

36、tabase Engine自動(dòng)檢測(cè)死鎖循環(huán)，并選擇一個(gè)會(huì)話作為死鎖中放棄的一方，通過終止該事務(wù)來打斷死鎖。被終止的事務(wù)發(fā)生回滾，并返回給連接一個(gè)錯(cuò)誤消息。如果在交互式的Transact-SQL語句中發(fā)生死鎖錯(cuò)誤，用戶只要簡(jiǎn)單地重新輸入Transact-SQL語句即可。在程序中的Transact-SQL中，應(yīng)用程序必須提供對(duì)死鎖錯(cuò)誤碼的處理，例如通過提示信息通知用戶或者自動(dòng)再次執(zhí)行該事務(wù)。9.4 管理鎖9.4.4 死鎖例9.9本例制造了一個(gè)簡(jiǎn)單的死鎖場(chǎng)景，并由SQL Server檢測(cè)和處理死鎖。具體步驟和代碼如下。（1）啟動(dòng)SQL Server Management Studio并打開一個(gè)查詢?cè)O(shè)

37、計(jì)器窗口。（2）輸入并執(zhí)行以下代碼來創(chuàng)建一個(gè)表t1，并在不關(guān)閉事務(wù)的情況下插入數(shù)據(jù)。USE teaching;GOCREATE TABLE t1(i int);BEGIN TRAN;INSERT INTO t1 VALUES(1);（3）打開第2個(gè)查詢窗口并執(zhí)行以下語句創(chuàng)建另一個(gè)表t2，并在其中插入數(shù)據(jù)，然后嘗試在表t1中更新數(shù)據(jù)。USE teaching;GOCREATE TABLE t2(i int);BEGIN TRAN;INSERT INTO t2 VALUES(1);UPDATE t1 SET i=2;由于在查詢1中的事務(wù)沒有提交，因此該事務(wù)將被阻塞。（4）切換回查詢窗口1，執(zhí)行以下UPDATE語句更新表t2。此時(shí)會(huì)發(fā)生什么結(jié)果呢？UPDATE t2 SET i=3;在幾秒鐘后，其中一個(gè)事務(wù)被取消了，并且返回了一個(gè)錯(cuò)誤消息