第8章 數(shù)據(jù)保護(hù)(事務(wù)和鎖)

1、第八章 數(shù)據(jù)保護(hù)之事務(wù)和鎖,8.3 事務(wù)處理 8.4 鎖,8.3 事務(wù)處理,,一、 事務(wù)簡介1.事務(wù)由來2 . 什么是事務(wù)3.事務(wù)屬性及分類二、管理事務(wù)1. 隱性事務(wù) 2 . 自動提交事務(wù) 3 . 顯式事務(wù),一、 事務(wù)簡介 1．事務(wù)由來,使用DELETE 命令或UPDATE 命令對數(shù)據(jù)庫進(jìn)行更新時一次只能操作一個表，這會帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題（即使已經(jīng)設(shè)置級聯(lián)刪除或修改，計算機(jī)內(nèi)部也是分別先后刪除每一

2、個表）。例如：在FACTORY 數(shù)據(jù)庫中，若取消了“市場部”，需要將‘市場部’從depart表中刪除，要修改depart 表，而WORKER表中的部門號與市場部相對應(yīng)的職工也應(yīng)刪除。因此，兩個表都需要修改,這種修改只能通過兩條DELETE 語句進(jìn)行。假設(shè)市場部編號為‘3’,,第一條DELETE 語句修改depart 表delete from departwhere 部門號= ’3’第二條DELETE 語句修改WORKER 表de

3、lete from WORKER where 部門號= ’3’ 在執(zhí)行第一條DELETE 語句后，數(shù)據(jù)庫中的數(shù)據(jù)已處于不一致的狀態(tài)，因為此時已經(jīng)沒有“市場部”了，但WORKER表中仍然保存著屬于“市場部”的員工記錄。只有執(zhí)行了第二條DELETE 語句后數(shù)據(jù)才重新處于一致狀態(tài)。但是，如果執(zhí)行完第一條語句后，計算機(jī)突然出現(xiàn)故障，無法再繼續(xù)執(zhí)行第二條DELETE 語句，則數(shù)據(jù)庫中的數(shù)據(jù)將處于永遠(yuǎn)不一致的狀態(tài)。因此，必須保證這兩條

4、DELETE 語句同時執(zhí)行。為解決類似的問題，數(shù)據(jù)庫系統(tǒng)通常都引入了事務(wù)（Transaction） 的概念。,2.什么是事務(wù),事務(wù)是單獨的邏輯工作單元，也是一個操作序列，該單元中可以包含多個操作以完成一個完整的任務(wù)。如果事務(wù)成功，在事務(wù)中所做的所有的操作都會在提交時完成并且永久地成為數(shù)據(jù)庫的一部分。如果事務(wù)遇到錯誤，則必須取消或回滾，這樣所有的操作都將被消除，就象什么也沒有執(zhí)行過一樣。事務(wù)作為一個整體，只有兩種狀態(tài):要么成功，要么失敗。

5、,事務(wù)具有ACID屬性：原子性(Atomicity)一致性(Consistency)隔離性(Isolation)持續(xù)性(Duration)事務(wù)可以分為顯式事務(wù)和自動提交事務(wù)、隱式事務(wù)。,,3.事務(wù)屬性及分類,1.顯式事務(wù)2 . 自動提交事務(wù) 3 .隱性事務(wù),二、 管理事務(wù),,1. 顯式事務(wù),在顯式事務(wù)中，事務(wù)的語句在BEGIN TRANSACTION和COMMIT TRANSACTION子句間組成一組。

6、使用下列四條語句來管理事務(wù)： (1)BEGIN TRANSACTION (2)COMMIT TRANSACTION (3)ROLLBACK TRANSACTION (4)SAVE TRANSACTION,,1).BEGIN TRANSACTION,標(biāo)記一個顯式本地事務(wù)的起始點，SQL Server可使用該語句來開始一個新的事務(wù)。語法格式如下：BEGIN TRAN [ SACTION ] [ trans

7、action_name | @tran_name_variable    [ WITH MARK [ 'description' ] ] ] 注意：任何有效的用戶都具有默認(rèn)的BEGIN TRANSACTION權(quán)限。,,2).COMMIT TRANSACTION,COMMIT TRANSACTION標(biāo)志一個成功的隱性事務(wù)或顯示事務(wù)的結(jié)束。 語法格式如下：COM

8、MIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ] 注意：事務(wù)的提交使得占用的資源被釋放，所做的修改在數(shù)據(jù)庫中成為永久有效。,,3).ROLLBACK TRANSACTION,ROLLBACK TRANSACTION將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點或事務(wù)內(nèi)的某個保存點。語法格式如下：ROLLBACK [ TRAN [ SACTION ]

9、60;   [ transaction_name | tran_name_variable    | savepoint_name | @savepoint_variable ] ]回滾事務(wù)還可以使用ROLLBACK WORK語句，其語法格式為： ROLLBACK [ WORK ] 注意：不帶savepoint_name和transaction_na

10、me的ROLLBACK TRANSACTION回滾到事務(wù)的起點。,,4).SAVE TRANSACTION,SAVE TRANSACTION是在事務(wù)內(nèi)設(shè)置保存點 。用戶可以在事務(wù)內(nèi)設(shè)置保存點或標(biāo)記。保存點定義如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置。語法格式如下：SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable },5).事務(wù)日志,WITH MARK使

11、得事務(wù)名被記錄到事務(wù)日志中，以便維護(hù)數(shù)據(jù)庫的一致性并為恢復(fù)提供援助。日志是一片存儲區(qū)，并自動追蹤數(shù)據(jù)庫的所有變化，但非日志運算不記錄到日志中。在進(jìn)行數(shù)據(jù)更新執(zhí)行過程中，修改行數(shù)據(jù)在未寫入數(shù)據(jù)庫前，先被記錄到日志中。 SQL Server采用下列方式應(yīng)用（前滾）或收回（回滾）每個事務(wù)： （1）在應(yīng)用事務(wù)日志時，事務(wù)將前滾。 （2）當(dāng)收回未完成的事務(wù)時，事務(wù)將回滾。,,,注意：部分語句不能應(yīng)用于事務(wù)處理中（即這些操作對數(shù)據(jù)庫的影響是

12、不能恢復(fù)的），其中包括CREATE DATABASE、 ALTER DATABASE、 DROP DATABASE、 RESTORE DATABASE、 LOAD DATABASE、 BACKUP LOG、RESTORE LOG、 UPDATE STATISTICS、GRANT、DUMP TRANSACTION、DISK INIT、RECONFIGURE。,例1：,DECLARE @TranName VARCHAR(20) --定義一

13、個局部變量SELECT @TranName = 'MyTransaction' --給局部變量賦值BEGIN TRANSACTION @TranName --事務(wù)開始 GO USE pubs GO UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' GO--提交事務(wù)COMMIT TRANSACT

14、ION MyTransactionGO,例2：設(shè)置事務(wù)保存點,USE XJGLGOSELECT COUNT(*) FROM STUDENT -- （假如STUDENT中原有22條記錄）BEGIN TRAN MYTRAN1 INSERT INTO STUDENT(SNO,SNAME) VALUES('1000','AAA') SELECT COUNT(*) FROM STUDENT --

15、幾條記錄？SAVE TRAN MYTRAN1 INSERT INTO STUDENT(SNO,SNAME) VALUES('2000','BBB') SELECT COUNT(*) FROM STUDENT --幾條記錄？ROLLBACK TRAN MYTRAN1COMMIT TRAN SELECT COUNT(*) FROM STUDENT -- 幾條記錄？,例3：設(shè)計一個事務(wù)并執(zhí)行：

16、將課程“數(shù)據(jù)庫原理”和“操作系統(tǒng)的”的課程號互換,BEGIN TRANUSE XJGLSELECT * FROM COURSEGODECLARE @CNO1 CHAR(2),@CNO2 CHAR(2)SELECT @CNO1=CNO FROM COURSE WHERE CNAME='數(shù)據(jù)庫原理'SELECT @CNO2=CNO FROM COURSE WHERE CNAME='操作系統(tǒng)'UP

17、DATE COURSE SET CNO=@CNO1 WHERE CNAME='操作系統(tǒng)'UPDATE COURSE SET CNO=@CNO2 WHERE CNAME='數(shù)據(jù)庫原理'COMMIT TRAN SELECT * FROM COURSE,USE FACTORYSELECT COUNT(*) FROM WORKERBEGIN TRAN INSERT INTO WORKER(職工

18、號,姓名) VALUES('80','AAA') SELECT COUNT(*) FROM WORKERROLLBACKSELECT COUNT(*) FROM WORKER,,2 自動提交事務(wù),它是SQL Server 的默認(rèn)事務(wù)管理模式，每個Transact-SQL語句在完成時，都被提交或回滾。如果一個語句成功地完成，則提交該語句；如果遇到錯誤，則回滾該語句。只要自動提交模式?jīng)]有被顯式或

19、隱性事務(wù)替代，SQL Server 連接就以自動提交事務(wù)為默認(rèn)模式進(jìn)行操作。 即SQL Server連接在BEGIN TRANSACTION語句啟動顯式事務(wù)模式；在隱性事務(wù)模式設(shè)置為打開后，啟動隱性事務(wù)模式；當(dāng)提交或回滾顯式事務(wù)，或者關(guān)閉隱性事務(wù)模式時，SQL Server將返回到自動提交模式。,3. 隱性事務(wù),隱性事務(wù)將在提交或回滾當(dāng)前事務(wù)后自動啟動新事務(wù)。無須描述事務(wù)的開始，只需提交或回滾每個事務(wù)。隱性事務(wù)模

20、式生成連續(xù)的事務(wù)鏈。在SQL Server中， 通過SET IMPLICIT_TRANSACTIONS ON語句將連接設(shè)置為隱性事務(wù)模式； 通過SET IMPLICIT_TRANSACTIONS OFF語句將連接設(shè)置為返回到自動提交事務(wù)模式。,,隱性事務(wù),在將隱性事務(wù)模式設(shè)置為打開之后，當(dāng)SQL Server首次執(zhí)行下列任何語句時，都會自動啟動一個事務(wù):,啟動事務(wù)的SQL語句,,8.4.1

21、 并發(fā)問題8.4.2 鎖、鎖的分類、死鎖,8.4 數(shù)據(jù)的鎖定,什么是并發(fā)問題？并發(fā)問題帶來什么異常？1.更新丟失 2.臟讀 3.不可重復(fù)讀 4.幻像讀,8.4.1 并發(fā)問題,,8.4.1 并發(fā)問題,1．更新丟失 當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，會發(fā)生丟失更新問題。每個事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。,,(a) 丟失修改,8

22、.4.1 并發(fā)問題,2．臟讀 當(dāng)?shù)诙€事務(wù)選擇其它事務(wù)正在更新的行時，會發(fā)生未確認(rèn)的相關(guān)性問題。第二個事務(wù)正在讀取的數(shù)據(jù)還沒有確認(rèn)并且可能由更新此行的事務(wù)所更改。,,(c) 讀“臟”數(shù)據(jù),8.4.1 并發(fā)問題,3．不可重復(fù)讀 當(dāng)?shù)诙€事務(wù)多次訪問同一行而且每次讀取不同的數(shù)據(jù)時，會發(fā)生不一致的分析問題。不一致的分析與未確認(rèn)的相關(guān)性類似，因為其它事務(wù)也是正在更改第二個事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致

23、的分析中，第二個事務(wù)讀取的數(shù)據(jù)是由已進(jìn)行了更改的事務(wù)提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次信息都由其它事務(wù)更改；因而該行被非重復(fù)讀取。,,不可重復(fù)讀,8.4.1 并發(fā)問題,4．幻像讀 當(dāng)對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時，會發(fā)生幻像讀問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因為該行已被其它事務(wù)刪除。同樣，由于其它事務(wù)的插入

24、操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中 。,,8.4.2 并發(fā)控制,1．悲觀并發(fā)控制 2．樂觀并發(fā)控制3．隔離級別,,8.4.2 并發(fā)控制,1．悲觀并發(fā)控制 （SQL SERVER 使用） 鎖定系統(tǒng)阻止用戶以影響其它用戶的方式修改數(shù)據(jù)。如果用戶執(zhí)行的操作導(dǎo)致應(yīng)用了某個鎖，則直到這個鎖的所有者釋放該鎖，其它用戶才能執(zhí)行與該鎖沖突的操作。該方法主要用在數(shù)據(jù)爭奪激烈的環(huán)境中，以及出現(xiàn)并發(fā)沖突時用鎖保護(hù)

25、數(shù)據(jù)的成本比回滾事務(wù)的成本低的環(huán)境中，因此稱該方法為悲觀并發(fā)控制。,,8.4.2 并發(fā)控制,2．樂觀并發(fā)控制 在樂觀并發(fā)控制中，用戶讀數(shù)據(jù)時不鎖定數(shù)據(jù)。在執(zhí)行更新時，系統(tǒng)進(jìn)行檢查，查看另一個用戶讀過數(shù)據(jù)后是否更改了數(shù)據(jù)。如果另一個用戶更新了數(shù)據(jù)，將產(chǎn)生一個錯誤。一般情況下，接收錯誤信息的用戶將回滾事務(wù)并重新開始。該方法主要用在數(shù)據(jù)爭奪少的環(huán)境內(nèi)，以及偶爾回滾事務(wù)的成本超過讀數(shù)據(jù)時鎖定數(shù)據(jù)的成本的環(huán)境內(nèi)，因此稱該方法

26、為樂觀并發(fā)控制。,,8.4.2 并發(fā)控制,3．隔離級別 事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級別稱為隔離級別。隔離級別是一個事務(wù)必須與其它事務(wù)進(jìn)行隔離的程度。較低的隔離級別可以增加并發(fā)，但代價是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級別確定了SQL Server使用的鎖定行為。 SQL Server支持的隔離級別： 未提交讀（事務(wù)隔離的最低級別，僅可保證不讀

27、取物理損壞的數(shù)據(jù)）。提交讀（SQL Server默認(rèn)級別）?？芍貜?fù)讀?？纱凶x（事務(wù)隔離的最高級別，事務(wù)之間完全隔離）。,,8.4.2 并發(fā)控制,事務(wù)隔離級別與并發(fā)問題的可能性,,8.4.3 什么是鎖？,數(shù)據(jù)庫的鎖代表的是對數(shù)據(jù)庫資源的一種權(quán)限。SQL SERVER 2000有一種共享鎖，就是當(dāng)DBA執(zhí)行SELECT語句時，對數(shù)據(jù)需要加一種共享鎖，就代表我申請訪問數(shù)據(jù)。SQL SERVER 2000有一種更新鎖，就是當(dāng)D

28、BA執(zhí)行UPDATE語句時，對數(shù)據(jù)需要加一種更新鎖，就代表我申請更新數(shù)據(jù)。誰來加鎖呢？SQL SERVER 2000系統(tǒng)本身會根據(jù)用戶對數(shù)據(jù)的操作來自動完成，DBA一般不需要手工進(jìn)行干預(yù)。（但有時為了設(shè)計更有效的應(yīng)用程序，可以自定義鎖）,鎖的分類,SQL Server可以鎖定的資源,SQL Server鎖定模式,,,8.4.3 鎖的分類 （自己看）,1．共享鎖 共享(S)鎖允許并發(fā)事務(wù)讀取(SELECT)一個資源

29、。資源上存在共享(S)鎖時，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享(S)鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享(S)鎖。,,2．更新鎖 更新(U)鎖可以防止通常形式的死鎖。一般更新模式由一個事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享(S)鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它(X)鎖。如果兩個事務(wù)獲得了資源上的共享模式鎖，然后試圖同時

30、更新數(shù)據(jù)，則一個事務(wù)嘗試將鎖轉(zhuǎn)換為排它(X)鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時間，因為一個事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務(wù)試圖獲取排它(X)鎖以進(jìn)行更新。由于兩個事務(wù)都要轉(zhuǎn)換為排它(X)鎖，并且每個事務(wù)都等待另一個事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。若要避免這種潛在的死鎖問題，請使用更新(U)鎖。一次只有一個事務(wù)可以獲得資源的更新(U)鎖。如果事務(wù)修改資源，則更新(U)鎖轉(zhuǎn)換為排它(X)鎖。否則，鎖

31、轉(zhuǎn)換為共享鎖。,,3．排它鎖 排它(X)鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。其它事務(wù)不能讀取或修改排它(X)鎖鎖定的數(shù)據(jù)。,,,4．意向鎖 意向鎖表示SQL Server需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享(S)鎖或排它(X)鎖。例如，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享(S)鎖。在表級設(shè)置意向鎖可防止另一個事務(wù)隨后在包含那一頁的表上獲取排它(X)鎖。意向鎖可以提高性能，因為S

32、QL Server僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個表。,,,5．架構(gòu)鎖 執(zhí)行表的數(shù)據(jù)定義語言(DDL)操作（例如添加列或除去表）時使用架構(gòu)修改(Sch-M)鎖。 當(dāng)編譯查詢時，使用架構(gòu)穩(wěn)定性(Sch-S)鎖。架構(gòu)穩(wěn)定性(Sch-S)鎖不阻塞任何事務(wù)鎖，包括排它(X)鎖。因此在編譯查詢時，其它事務(wù)（包括在表上有排它(X

33、)鎖的事務(wù)）都能繼續(xù)運行。但不能在表上執(zhí)行DDL操作。,,,6．大容量更新鎖 當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了TABLOCK提示或者使用sp_tableoption設(shè)置了table lock on bulk表選項時，將使用大容量更新(BU)鎖。大容量更新(BU)鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時防止其它不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問該表。,,,死鎖,當(dāng)某組資源的兩個或多個線程之間有循環(huán)相關(guān)性時，將發(fā)生死鎖。在多用戶環(huán)境中

34、，當(dāng)多個用戶(或會話)擁有對不同對象的鎖，并且每個用戶都試圖獲得對方所鎖定的對象的鎖時，將發(fā)生死鎖，它們因為正等待對方擁有的資源而不能提交或回滾事務(wù)。,死鎖,,本章小結(jié),事務(wù)是單獨的工作單元，該單元中可以包含多個操作以完成一個完整的任務(wù)。鎖是在多用戶環(huán)境中對數(shù)據(jù)訪問的限制。事務(wù)和鎖確保了數(shù)據(jù)的完整性。事務(wù)確保了對數(shù)據(jù)的多個修改能夠一起處理。加鎖防止了更新沖突，使得事務(wù)是可串行化，允許數(shù)據(jù)的并發(fā)使用，加鎖是自動實現(xiàn)的。當(dāng)管理事務(wù)和加