第13章事務與鎖

1、SQL Server 2012數(shù)據(jù)庫管理教程第第13章章第1313章 事務與鎖事務的概述創(chuàng)建事務鎖13.1 13.1 事務的概述13.1.1 13.1.1 事務的概念事務是構成單一邏輯工作單元的操作集合。一個事務中可能只包含一個操作，也可能包含一系列的操作。這些操作要么全部執(zhí)行，要么全部不執(zhí)行。13.1.2 13.1.2 事務的狀態(tài)通常，一個事務會有兩種輸出結果。如果一個事務成功執(zhí)行，我們就說這個事務被提交，數(shù)據(jù)庫會進入一個新的一致狀態(tài)。如果事務沒有成功執(zhí)行，那么事務會被中止，數(shù)據(jù)庫將回到事務開始以前的那個一致狀態(tài)，這時稱事務被回滾。事務執(zhí)行過程中所處的各種可能的狀態(tài)如下圖所示。1 1）活動狀

2、態(tài)：事務執(zhí)行時處于該狀態(tài)。2 2）部分提交狀態(tài)：事務的最后一條語句執(zhí)行之后進入該狀態(tài)。3 3）失敗狀態(tài)：當事務中的操作不能繼續(xù)后進入該狀態(tài)。4 4）中止狀態(tài)：事務回滾，并且數(shù)據(jù)庫已恢復到事務開始執(zhí)行以前的狀態(tài)后，事務處于該狀態(tài)。5 5）提交狀態(tài)：事務成功執(zhí)行之后進入該狀態(tài)。事務從活動狀態(tài)開始，當事務完成它的最后一條語句后就進入了部分提交狀態(tài)。此刻，事務的全部操作已經(jīng)執(zhí)行完，但由于實際輸出可能仍臨時駐留在主存中，而在其成功完成前可能出現(xiàn)硬件故障。因此，事務仍有可能失敗，所以稱事務進入部分提交狀態(tài)。接著數(shù)據(jù)庫系統(tǒng)將事務所做的更新寫入磁盤，確保即使出現(xiàn)故障，事務所做的更新也會保留在磁盤上。系統(tǒng)判定事

3、務不能繼續(xù)正常執(zhí)行后（如由于硬件或邏輯錯誤），事務進入失敗狀態(tài)。這種事務必須回滾，此時，事務就進入了中止狀態(tài)。事務進入中止狀態(tài)后，系統(tǒng)有兩種選擇。1 1）重啟事務：當引起事務中止的錯誤是軟件或硬件錯誤，而不是事務內(nèi)部邏輯錯誤時，可以重新啟動事務。重新啟動的事務是一個新事務。2 2）殺死事務：這樣做通常是由于事務內(nèi)部邏輯造成的錯誤，只有重寫應用程序才能改正。事務的提交狀態(tài)和中止狀態(tài)都是事務的結束狀態(tài)。13.1.3 13.1.3 事務的特性為了保證數(shù)據(jù)庫中的數(shù)據(jù)總是正確的，要求事務必須滿足四個特性。我們將這四個特性簡稱為ACIDACID，這個縮寫來自于這四個特性的英文單詞的首字母。1 1）原子性（

4、AtomicityAtomicity）：每一個事務是一個工作單元，它不能被分割成更小的部分。2 2）一致性（ConsistencyConsistency）：一個事務對數(shù)據(jù)庫的更改必須保證數(shù)據(jù)庫從一個一致的狀態(tài)轉換到另一個一致的狀態(tài)3 3）隔離性（IsolationIsolation）：即使多個事務可以并發(fā)執(zhí)行，事務之間也應該彼此獨立。4 4）持久性（DurabilityDurability）：一個事務成功提交之后，無論發(fā)生任何系統(tǒng)錯誤，這個事務更改的數(shù)據(jù)將永久存在。當用戶設計SQL ServerSQL Server事務時，必須注意以下幾點：1 1）定義事務的外延。開發(fā)者需要定義事務從哪里開始，

5、到哪里結束。事務越短越好，但是同時要做到盡量符合實際業(yè)務過程的需要。2 2）定義事務的錯誤管理。在事務中，并不是所有的錯誤都會使事務自動回滾。開發(fā)者應該對事務中的錯誤進行管理，以保證錯誤出現(xiàn)時，使用專門的命令來回滾事務。3 3）正確使用隔離級別。如果將每個事務都與其他事務隔離開來執(zhí)行，那么就會降低系統(tǒng)并行執(zhí)行的效率。在使用事務時，如果能夠設定恰當?shù)母綦x級別，那么既可以保證事務正確執(zhí)行，也可以發(fā)揮系統(tǒng)并行執(zhí)行的高效性。13.2 13.2 創(chuàng)建事務自動提交事務：SQL ServerSQL Server數(shù)據(jù)庫引擎的默認模式。每個單獨的Transact-SQLTransact-SQL語句都在其完成后提

6、交，不必指定任何語句來控制事務。顯式事務：每個事務均以BEGIN TRANSACTIONBEGIN TRANSACTION語句顯式開始，以COMMITCOMMIT或ROLLBACKROLLBACK語句顯式結束。隱式事務：在前一個事務完成時新事務即隱式啟動，但每個事務仍以COMMITCOMMIT或ROLLBACKROLLBACK語句顯式結束。在SQL Server 2012SQL Server 2012中，默認情況下，事務按連接級別進行管理，一個連接的事務模式發(fā)生變化對其他連接的事務模式?jīng)]有影響。每一個連接可以選擇使用以下三種事務模式中的一種。13.2.1 13.2.1 自動提交事務SQL Se

7、rver 2012SQL Server 2012數(shù)據(jù)庫的所有數(shù)據(jù)處理都是以事務的形式完成的。也就是說，在SQL Server 2012SQL Server 2012中，如果用戶沒有顯式地定義事務，系統(tǒng)會自動為該處理定義事務。由SQL ServerSQL Server定義的事務稱為自動提交事務。自動提交事務模式是SQL ServerSQL Server的默認模式。在該模式下，每個Transact-SQLTransact-SQL語句都是一個事務。如果這個語句成功地完成，則提交該語句；如果遇到錯誤，則回滾該語句。USE jwUSE jwGOGOCREATE TABLE TeacherCREATE T

8、ABLE Teacher ( teacherid char(3) NOT NULL PRIMARY KEY, ( teacherid char(3) NOT NULL PRIMARY KEY, name varchar(50) name varchar(50) NOT NULL, NOT NULL, sex char(2) ) sex char(2) )GOGO【例13-113-1】在jwjw數(shù)據(jù)庫中創(chuàng)建一個新表，使用自動提交事務模式向表中插入違反約束的記錄，觀察數(shù)據(jù)插入操作的結果，并分析自動提交事務模式的特點。1 1）在查詢編輯器中，使用CREATE TABLECREATE TABLE語句來

9、創(chuàng)建一個數(shù)據(jù)表TeacherTeacher，通過在該數(shù)據(jù)表中插入數(shù)據(jù)來檢驗事務的行為。2 2）在查詢編輯器窗口中向TeacherTeacher表插入三條記錄。INSERT INTO TeacherINSERT INTO Teacher VALUES(001, VALUES(001,李麗李麗, , 女女) ) INSERT INTO TeacherINSERT INTO Teacher VALUES(002,NULL, VALUES(002,NULL, 女女) ) INSERT INTO TeacherINSERT INTO Teacher VALUES(003, VALUES(003,楊育清楊

10、育清, , 男男) )GOGO3 3）執(zhí)行以上三個INSERTINSERT語句，因為第二個INSERTINSERT語句向namename列中插入NULLNULL，而該列被定義為不允許空，所以提示錯誤消息，如下圖所示。4 4）輸入并執(zhí)行以下SELECTSELECT語句，檢查記錄是否已成功插入到TeacherTeacher表中。SELECT SELECT * * FROM Teacher FROM TeacherGOGO運行結果如下圖所示。5 5）從圖中所示結果可以看出，第二條記錄沒有被插入，但是第一條和第三條記錄已被成功插入。13.2.2 13.2.2 顯式事務如果需要將多條語句放在一個事務中執(zhí)

11、行，那么用戶可以使用顯式事務模式。在該模式下，用戶需要定義事務從哪里開始。如果事務能夠正確執(zhí)行，則提交事務；如果事務不能正確執(zhí)行，則回滾事務。1 1定義顯式事務的語句BEGIN TRANSACTIONBEGIN TRANSACTION：用于定義一個事務的開始點。COMMIT TRANSACTIONCOMMIT TRANSACTION：當一個事務正確執(zhí)行完以后，使用該語句提交事務，使事務對數(shù)據(jù)庫的更改能夠長久保留。ROLLBACK TRANSACTIONROLLBACK TRANSACTION：如果事務執(zhí)行過程中發(fā)生了錯誤，使用該語句回滾該事務，使數(shù)據(jù)庫回到事務開始以前的狀態(tài)。2 2捕獲錯誤的語

12、句用戶設計的事務應該具有捕獲錯誤的功能。如果捕獲到錯誤，則顯式地回滾事務，否則顯式地提交事務?？梢允褂肨RYTRYCATCHCATCH語句捕獲Transact-SQLTransact-SQL代碼中的錯誤。該語句包括兩部分：一個TRYTRY塊和一個CATCHCATCH塊。如果在TRYTRY塊所包含的Transact-SQLTransact-SQL語句中檢測到錯誤，控制將被傳遞到CATCHCATCH塊，所以，可以在CATCHCATCH塊中處理該錯誤。TRYTRY塊以BEGIN TRYBEGIN TRY語句開頭，以END TRYEND TRY語句結尾。在BEGIN BEGIN TRYTRY和END

13、TRYEND TRY語句之間可以指定一個或多個Transact-SQLTransact-SQL語句。CATCHCATCH塊必須緊跟TRYTRY塊。CATCHCATCH塊以BEGIN CATCHBEGIN CATCH語句開頭，以END CATCHEND CATCH語句結尾。在Transact-SQLTransact-SQL中，每個TRYTRY塊僅與一個CATCHCATCH塊相關聯(lián)?？梢栽赥RYTRYCATCHCATCH語句中使用以下錯誤函數(shù)來捕獲錯誤信息。ERROR_NUMBER( )ERROR_NUMBER( )：返回錯誤號。ERROR_MESSAGE( )ERROR_MESSAGE( )：返

14、回錯誤消息的完整文本。ERROR_SEVERITY( )ERROR_SEVERITY( )：返回錯誤嚴重性。ERROR_STATE( )ERROR_STATE( )：返回錯誤狀態(tài)號。ERROR_LINE( )ERROR_LINE( )：返回導致錯誤的例程中的行號。ERROR_PROCEDURE( )ERROR_PROCEDURE( )：返回出現(xiàn)錯誤的存儲過程或觸發(fā)器的名稱?！纠?3-213-2】 將例13-113-1的向TeacherTeacher表中插入三行數(shù)據(jù)的操作放在一個顯式事務中執(zhí)行，觀察執(zhí)行的結果。1 1）在查詢編輯器中輸入并執(zhí)行以下Transact-SQLTransact-SQL語

15、句。USE jwUSE jwGOGOTRUNCATE TABLE Teacher;TRUNCATE TABLE Teacher;GOGOBEGIN TRYBEGIN TRYBEGIN TRANSACTIONBEGIN TRANSACTIONINSERT INTO TeacherINSERT INTO TeacherVALUES(001, VALUES(001, 李麗李麗, , 女女) )INSERT INTO TeacherINSERT INTO TeacherVALUES(002,NULL, VALUES(002,NULL, 女女) )INSERT INTO TeacherINSERT IN

16、TO TeacherVALUES(003,VALUES(003,楊育清楊育清, , 男男) ) COMMIT TRANSACTION; COMMIT TRANSACTION;END TRYEND TRYBEGIN CATCHBEGIN CATCHSELECT ERROR_NUMBER( ) AS ErrorNumber,SELECT ERROR_NUMBER( ) AS ErrorNumber, ERROR_SEVERITY( ) AS ErrorSeverity, ERROR_SEVERITY( ) AS ErrorSeverity, ERROR_STATE( ) AS ErrorState

17、, ERROR_STATE( ) AS ErrorState, ERROR_LINE( ) AS ErrorLine, ERROR_LINE( ) AS ErrorLine, ERROR_MESSAGE( ) AS ErrorMessage; ERROR_MESSAGE( ) AS ErrorMessage; ROLLBACK TRANSACTION; ROLLBACK TRANSACTION;END CATCH;END CATCH;GOGO運行結果如下圖所示。2 2）輸入并執(zhí)行以下SELECTSELECT語句檢查事務是否被回滾。SELECT SELECT * * FROM Teacher;

18、FROM Teacher;GOGO運行結果如下圖所示。從圖中可以看出，從圖中可以看出，TeacherTeacher表中表中沒有任何記錄。因為第二條沒有任何記錄。因為第二條INSERTINSERT語句出現(xiàn)錯誤，所以流程語句出現(xiàn)錯誤，所以流程跳到跳到CATCHCATCH塊。在塊。在CATCHCATCH塊中，塊中，首先通過使用錯誤函數(shù)顯示錯誤信首先通過使用錯誤函數(shù)顯示錯誤信息，然后顯式地將整個事務回滾，息，然后顯式地將整個事務回滾，所以系統(tǒng)回到事務開始以前的狀態(tài)。所以系統(tǒng)回到事務開始以前的狀態(tài)。13.2.3 13.2.3 隱式事務當連接以隱式事務模式進行操作時，SQL ServerSQL Serve

19、r將在提交或回滾當前事務后自動啟動新事務，無需描述事務的開始。但是，用戶必須使用COMMITCOMMIT或ROLLBACKROLLBACK語句顯式地結束事務。將隱式事務模式設置為打開之后，在首次執(zhí)行下表中的任何語句時，都會自動啟動一個事務。隱式事務模式下能夠自動啟動一個事務的Transact-SQLTransact-SQL語句在發(fā)出COMMITCOMMIT或ROLLBACK ROLLBACK 語句之前，該事務將一直保持有效。在第一個事務被提交或回滾之后，下次當連接執(zhí)行以上任何語句時，都將自動啟動一個新事務?！纠?3-313-3】使用隱式事務模式，創(chuàng)建一個表Teacher2Teacher2，向表

20、中插入記錄，顯示插入操作的結果。1 1）在查詢編輯器中輸入并執(zhí)行以下Transact-SQLTransact-SQL語句，在當前連接上設置隱式事務模式。SET IMPLICIT_TRANSACTIONS ON;SET IMPLICIT_TRANSACTIONS ON;GOGO2 2）在查詢編輯器中輸入并執(zhí)行以下Transact-SQLTransact-SQL語句創(chuàng)建表Teacher2Teacher2。USE jwUSE jwGOGOCREATE TABLE Teacher2(CREATE TABLE Teacher2( teacherid teacherid char(3) NOT NULL

21、PRIMARY KEY,char(3) NOT NULL PRIMARY KEY, name varchar(50); name varchar(50);GOGO3 3）使用系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT來測試是否已經(jīng)打開了一個事務。SELECT TRANCOUNT AS Transaction Count;SELECT TRANCOUNT AS Transaction Count;GOGO運行結果如圖所示。從圖中所示的結果可以看出，當前連接已經(jīng)打開了一個事務。4 4）提交事務并再次使用系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT測試當前連接上打開的事務。COMMIT TRANS

22、ACTION;COMMIT TRANSACTION;GOGOSELECT TRANCOUNT AS Transaction Count;SELECT TRANCOUNT AS Transaction Count;GOGO運行結果如圖所示。由圖中所示結果可知，顯由圖中所示結果可知，顯示的結果為示的結果為0，也就是說，使用，也就是說，使用COMMIT TRANSACTION命令命令提交事務后，事務結束。提交事務后，事務結束。5 5）執(zhí)行以下語句關閉隱式事務模式。SET IMPLICIT_TRANSACTIONS OFF;SET IMPLICIT_TRANSACTIONS OFF;GOGO13.2.

23、4 13.2.4 嵌套事務顯式事務可以嵌套，即在一個顯式事務中可以嵌套另一個顯式事務。【例13-413-4】使用事務的嵌套。在外層事務中創(chuàng)建一個表格，在內(nèi)層嵌套的事務中向該表插入數(shù)據(jù)。內(nèi)層事務和外層事務分別被提交。1 1）輸入以下語句。USE jwUSE jwGOGOBEGIN TRANSACTIONBEGIN TRANSACTIONPRINT After the first begin-transaction:+CAST(TRANCOUNT AS char(1)PRINT After the first begin-transaction:+CAST(TRANCOUNT AS char(1)

24、CREATE TABLE TempTable (CREATE TABLE TempTable ( col1 int PRIMARY KEY, col1 int PRIMARY KEY, clo2 char(6) clo2 char(6)BEGIN TRANSACTIONBEGIN TRANSACTIONPRINT After the second begin-transaction:+CAST(TRANCOUNT AS char(1)PRINT After the second begin-transaction:+CAST(TRANCOUNT AS char(1)INSERT INTO Te

25、mpTableINSERT INTO TempTable VALUES(1,AAA) VALUES(1,AAA)COMMIT TRANSACTIONCOMMIT TRANSACTIONPRINT After the first commit-transaction:+CAST(TRANCOUNT AS char(1)PRINT After the first commit-transaction:+CAST(TRANCOUNT AS char(1)COMMIT TRANSACTIONCOMMIT TRANSACTIONPRINT After the second commit-transact

26、ion:+PRINT After the second commit-transaction:+ CAST(TRANCOUNT AS char(1) CAST(TRANCOUNT AS char(1)GOGO在上面的例子中，先在外層事務中定義了TempTableTempTable表，再開始內(nèi)層事務，并在內(nèi)層事務中使用INSERTINSERT語句向表中插入了一條記錄；然后依次提交內(nèi)層事務和外層事務。其中系統(tǒng)函數(shù)CASTCAST的作用是進行類型轉換，將系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT返回的整型數(shù)值轉變?yōu)轭愋蜑閏har(1)char(1)的字符型數(shù)據(jù)輸出。2 2）執(zhí)行以上的語句，運行結果

27、如下圖所示。從圖中的執(zhí)行結果中可以看出，每執(zhí)行一次BEGIN TRANSACTIONBEGIN TRANSACTION語句，便開始一個新事務，而且系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT的值也會增加1 1。每執(zhí)行一次COMMIT TRANSACTIONCOMMIT TRANSACTION語句，可以關閉一層事務，使系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT的值減小1 1。當系統(tǒng)函數(shù)TRANCOUNTTRANCOUNT的值減為0 0時，最外層的事務也被關閉了。3 3）使用以下SELECTSELECT語句查看TempTableTempTable表中的記錄。SELECT SELECT * * F

28、ROM TempTable FROM TempTableGOGO運行結果如下圖所示?！纠?3-513-5】將例13-413-4中的事務嵌套示例修改為：提交內(nèi)層事務，但撤銷外層事務。1 1）刪除TempTableTempTable表。DROP TABLE TempTableDROP TABLE TempTableGOGO2 2）創(chuàng)建一個包含嵌套事務的事務，提交內(nèi)層事務，但撤銷外層事務。BEGIN TRANSACTIONBEGIN TRANSACTIONCREATE TABLE TempTable(CREATE TABLE TempTable( col1 int PRIMARY KEY, col1

29、 int PRIMARY KEY, clo2 char(6) clo2 char(6)BEGIN TRANSACTIONBEGIN TRANSACTIONINSERT INTO TempTableINSERT INTO TempTable VALUES(1,AAA) VALUES(1,AAA)COMMIT TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRANSACTIONGOGO執(zhí)行以上語句，系統(tǒng)顯示正確執(zhí)行的消息，運行結果如下圖所示。3 3）使用以下SELECTSELECT語句查看TempTableTempTable表中

30、的記錄。SELECT SELECT * * FROM TempTable FROM TempTableGOGO運行結果顯示如下圖所示的錯誤信息。從圖中所示結果可以看出，即使內(nèi)層事務被提交，如果外層事務被回滾，那么內(nèi)層事務也會回滾。也就是說，回滾外層事務時，將創(chuàng)建表以及向表中插入記錄的操作全部回滾，表TempTableTempTable不存在。【例13-613-6】將例13-413-4中的事務嵌套示例修改為回滾內(nèi)層事務，但提交外層事務。BEGIN TRANSACTIONBEGIN TRANSACTIONCREATE TABLE TempTable3(CREATE TABLE TempTable3

31、( col1 int PRIMARY KEY, col1 int PRIMARY KEY, clo2 char(6) clo2 char(6)BEGIN TRANSACTIONBEGIN TRANSACTIONINSERT INTO TempTable3INSERT INTO TempTable3 VALUES(1,AAA) VALUES(1,AAA)ROLLBACK TRANSACTIONROLLBACK TRANSACTIONCOMMIT TRANSACTIONCOMMIT TRANSACTIONGOGO執(zhí)行以上語句，系統(tǒng)顯示錯誤提示，運行結果如下圖所示。13.3 13.3 鎖提高吞吐量和

32、資源利用率。減少等待時間。13.3.1 13.3.1 鎖的概述1 1并發(fā)操作產(chǎn)生的問題事務處理系統(tǒng)通常允許多個事務并發(fā)執(zhí)行。事務并發(fā)執(zhí)行有以下優(yōu)點：但是，即使每個事務單獨執(zhí)行時都能正確執(zhí)行，當它們并發(fā)執(zhí)行時，數(shù)據(jù)庫的一致性也可能被破壞。下面來看幾個并發(fā)操作可能會引發(fā)的問題。（1 1）丟失更新當兩個或多個事務讀取同一數(shù)據(jù)項，然后基于最初讀取的值更新該數(shù)據(jù)項時，會發(fā)生丟失更新問題。每個事務都不知道其他事務的存在，最后的更新將覆蓋其他事務所做的更新，這將導致數(shù)據(jù)更新丟失。（2 2）讀“臟”數(shù)據(jù)當某個事務讀取其他事務正在更新的數(shù)據(jù)項時，可能會發(fā)生讀“臟”數(shù)據(jù)的問題。（3 3）不可重復讀當某個事務多次讀

33、取同一數(shù)據(jù)項，但讀取的結果卻不相同時，稱為不可重復讀。（4 4）幻讀當對某行執(zhí)行插入或刪除操作，而該行屬于某個事務正在讀取的行的范圍時，會發(fā)生幻讀問題。2 2封鎖技術數(shù)據(jù)庫管理系統(tǒng)引入鎖的機制來解決并發(fā)訪問帶來的問題。如果事務請求的鎖模式與已授予同一數(shù)據(jù)項的鎖模式發(fā)生沖突，則數(shù)據(jù)庫引擎實例將暫停事務請求直到第一個鎖釋放。基本的鎖模式包括共享鎖（S S鎖）和排他鎖（X X鎖）。共享鎖（S S鎖）：當事務獲得了數(shù)據(jù)項上的共享鎖后，該事務只能讀不能寫該數(shù)據(jù)項。例如，SELECTSELECT語句只讀取數(shù)據(jù)，所以事務中使用SELECTSELECT時會獲得相應數(shù)據(jù)上的共享鎖。排他鎖（X X鎖）：當事務獲得

34、了數(shù)據(jù)項上的排他鎖后，該事務既可以讀也可以寫該數(shù)據(jù)項。例如，在事務中使用INSERTINSERT，UPDATEUPDATE及DELETEDELETE語句時，會獲得該相應數(shù)據(jù)上的排他鎖。SQL Server 2012SQL Server 2012還提供了更新鎖（U U鎖）。當SQL ServerSQL Server定位表中必須修改的行時，這種鎖首先定義為共享鎖，但在執(zhí)行實際數(shù)據(jù)修改之前，這種鎖會升級為排他鎖。鎖定可分為三個不同的級別，可以在行、頁或表三個層次進行鎖定。SQL ServerSQL Server以動態(tài)增加機制為基礎，自動將資源鎖定在適合任務的級別，并盡量減小鎖定的開銷。SQL ServerSQL Server總會嘗試精細地鎖住資源。在大多數(shù)情況下，它會首先基于行級加鎖，如果鎖住的行太多，會提升鎖至表級。這個過程是自動完成的?！纠?3-7