1、第十一章 事務(wù)和鎖事務(wù)的概念 事務(wù)是一種機制、是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，并且所有的命令作為一個整體一起向系統(tǒng)提交或撤消操作請求，即這一組數(shù)據(jù)庫命令要么成功、要么失敗。 對于數(shù)據(jù)庫管理系統(tǒng)，如果一次最多允許一個用戶使用，則該系統(tǒng)為單用戶系統(tǒng)；反之為多用戶系統(tǒng)。如：銀行、證券交易所等都是多用戶系統(tǒng)，這些系統(tǒng)都允許多用戶同時提交事務(wù)操作。 如銀行轉(zhuǎn)帳l 事務(wù)和觸發(fā)器事務(wù)和觸發(fā)器他們有時候是功能是等同的，只是實現(xiàn)方式不同而已 l 觸發(fā)器應(yīng)該慎用，相對來說事務(wù)的執(zhí)行效率要高，因為觸發(fā)器是并發(fā)的，多條語句同時執(zhí)行, 而事務(wù)中, 是一句一句執(zhí)行的. 如果事務(wù)結(jié)束前.出現(xiàn)錯誤.那么整個事務(wù)就

2、會回滾. 取消之前的所有操作. 觸發(fā)器對于數(shù)據(jù)來說，也不便于維護。l 用事務(wù)的話. 執(zhí)行第一個正常,第二個出錯.可以回滾.那么第一個執(zhí)行正常的 也不會更新數(shù)據(jù).l 用觸發(fā)器就不一樣了. 第一個或者第二個執(zhí)行正常與否,與另一條無關(guān).l 一般金融機構(gòu)，多用戶并發(fā)系統(tǒng)都采用事務(wù)來出來數(shù)據(jù)，更好的保證了數(shù)據(jù)的完整性和同一性。銀行轉(zhuǎn)帳 資金從帳戶A轉(zhuǎn)到帳戶B，至少需要兩步：帳戶A的資金減少然后帳戶B的資金相應(yīng)增加銀行轉(zhuǎn)帳帳戶帳戶A帳戶帳戶Bl 假定張三的帳戶直接轉(zhuǎn)帳假定張三的帳戶直接轉(zhuǎn)帳1000元到李四的帳戶元到李四的帳戶CREATE TABLE bank( customerName CHAR(10),

3、 -顧客姓名 currentMoney MONEY -當(dāng)前余額)GOALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)GOINSERT INTO bank(customerName,currentMoney) VALUES(張三,1000)INSERT INTO bank(customerName,currentMoney) VALUES(李四,1)創(chuàng)建帳戶表，存放用戶的帳戶信息添加約束：根據(jù)銀行規(guī)定，帳戶余額不能少于1元，否則視為銷戶 張三開戶，開戶金額為1000元 ；李四開戶，開戶金額1元 /*-轉(zhuǎn)帳測

4、試：張三轉(zhuǎn)賬1000元給李四-*/-我們可能會這樣這樣編寫語句-張三的帳戶少1000元，李四的帳戶多1000元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四GO-再次查看轉(zhuǎn)帳后的結(jié)果。 SELECT * FROM bankGO請問：執(zhí)行轉(zhuǎn)帳語句后，張三、李四的帳戶余額為多少？l張三的帳戶沒有減少 l但李四的帳戶卻多了1000元 l100010012001元 總額多出了10

5、00元！l錯誤原因分析：錯誤原因分析：-張三的帳戶減少1000元，李四的帳戶增加1000元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=張三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四GOUPDATE語句違反約束：余額=1元執(zhí)行失敗，所以張三還是執(zhí)行失敗，所以張三還是1000元元繼續(xù)往下執(zhí)行繼續(xù)往下執(zhí)行：執(zhí)行成功，所以李四變?yōu)椋簣?zhí)行成功，所以李四變?yōu)?001元元如何解決呢？使用如何解決呢？使用事務(wù)事務(wù)l 事務(wù)(TRAN

6、SACTION)是作為單個邏輯工作單元執(zhí)行的一系列操作l 這些操作作為一個整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行 l 事務(wù)是一個不可分割的工作邏輯單元 l 轉(zhuǎn)帳過程就是一個事務(wù)。l 它需要兩條UPDATE語句來完成，這兩條語句是一個整體，如果其中任一條出現(xiàn)錯誤，則整個轉(zhuǎn)帳業(yè)務(wù)也應(yīng)取消，兩個帳戶中的余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)帳前和轉(zhuǎn)帳后的余額不變，即都是1001元。 事務(wù)的四個特性（ACID)l 原子性（Atomic） :事務(wù)是一個完整的操作。事務(wù)的各個元素是不可分割的（原子的）。事務(wù)必須作為一個整體提交或回滾，如果事務(wù)中的任何元素失敗，則整個事務(wù)將失敗。事務(wù)中任何一個數(shù)據(jù)庫操作失

7、敗，已經(jīng)執(zhí)行的任何操作都必須撤銷，讓數(shù)據(jù)庫返回到初始狀態(tài)。l 一致性（Consistency）：在每一個事務(wù)執(zhí)行后，數(shù)據(jù)庫必須保持一致的狀態(tài)。也就是說，在事務(wù)開始之前，數(shù)據(jù)存儲中的數(shù)據(jù)處于一致狀態(tài)。在正在進行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)。事務(wù)操作成功后，數(shù)據(jù)庫所處的狀態(tài)和它的業(yè)務(wù)規(guī)則是一致的，即數(shù)據(jù)不會被破壞。l 隔離性（Isolation）：在事務(wù)完成前，它對數(shù)據(jù)庫的修改對其它事務(wù)必定不可見。這表明事務(wù)是必須獨立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。在并發(fā)數(shù)據(jù)操作時，不同的事務(wù)擁有各自數(shù)據(jù)空間，它們的操作不會對對方產(chǎn)生干擾。數(shù)據(jù)庫規(guī)定了多種事務(wù)隔離級別，不同隔離級別對應(yīng)不同的干擾程

8、度，隔離級別越高，數(shù)據(jù)一致性越好，但并發(fā)性越弱。l 持久性（Durabiliy） ：事務(wù)的持久性意味著只有當(dāng)事務(wù)提交以后，操作才會完成。如果系統(tǒng)相關(guān)事務(wù)剛剛完成后崩潰或重新啟動，則必須保證對數(shù)據(jù)所做的修改不受其的影響。事務(wù)控制事務(wù)控制l BEGIN TRANSACTION：該語句標記一個顯l 式事務(wù)的開始點，即事務(wù)開始。其語法如下：l BEGIN TRAN | TRANSACTION l transaction_name | tran_name_variable l WITH MARK description ; l COMMIT TRANSACTION：該語句標志一個成功的隱性事務(wù)或顯式事務(wù)

9、的結(jié)束，即事務(wù)提交l 其語法如下：l COMMIT TRAN | TRANSACTION l transaction_name | tran_name_variable ; 事務(wù)控制事務(wù)控制l ROLLBACK TRANSACTION ：該語句將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點或事務(wù)內(nèi)的某個保存點。其語法如下：l ROLLBACK TRAN | TRANSACTION l transaction_name | tran_name_variablel | savepoint_name | savepoint_variable ; 事務(wù)的分類1自動提交事務(wù) 自動提交模式是 SQL Server

10、的默認事務(wù)管理模式。每個 SQL 語句在完成時，都被提交或回滾。如果一個語句成功地完成，則提交該語句；如果遇到錯誤，則回滾該語句。只要自動提交模式?jīng)]有被顯式或隱性事務(wù)替代，SQL Server 連接就以該默認模式進行操作。簡言之,每條單獨的語句都是一個事務(wù)。 2顯式事務(wù)：顯式地定義其開始和結(jié)束的事務(wù),這是最常用的事務(wù)類型l 該類事務(wù)是用戶自定義事務(wù)，是以l BEGIN TRANSACTION（事務(wù)開始）開頭l 以 COMMIT TRANSACTION（事務(wù)提交）l 或者 ROLLBACK TRANSACTION（回滾事務(wù)）語句結(jié)束的l 一旦事務(wù)提交或回滾，則事務(wù)結(jié)束。l 判斷某條語句執(zhí)行是否出

11、錯：使用全局變量ERROR和ROWCOUNT；ERROR只能判斷當(dāng)前一條T-SQL語句執(zhí)行是否有錯，為了判斷事務(wù)中所有T-SQL語句是否有錯，我們需要對錯誤進行累計； 如： SET errorSum=errorSum+error 如：if error0 or rowcount1 rollback transaction 3隱式事務(wù) 當(dāng)連接以隱性事務(wù)模式進行操作時，SQL Server將在提交或回滾當(dāng)前事務(wù)后自動啟動新事務(wù)。在前一個事務(wù)完成時新事務(wù)隱式啟動，但每個事務(wù)仍以 COMMIT 或 ROLLBACK 語句顯式完成。隱式事務(wù)的意思就是在提交或回滾當(dāng)前事務(wù)后自動啟動新事務(wù)不需要再次定義事務(wù)的

12、開始，只需要提交或回滾每個事務(wù)就可以了。隱式事務(wù)產(chǎn)生的是一個連續(xù)的事務(wù)鏈。l 語法 SET IMPLICIT_TRANSACTIONS ON | OFF 當(dāng)設(shè)置為 ON 時，SET IMPLICIT_TRANSACTIONS 將連接設(shè)置為隱性事務(wù)模式。當(dāng)設(shè)置為 OFF 時，則使連接返回到自動提交事務(wù)模。 使用事務(wù)解決銀行轉(zhuǎn)帳問題l Print 查看轉(zhuǎn)賬事務(wù)之前的余額l Select * FROM bank l BEGIN TRANSACTION l /*-定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤-*/l DECLARE errorSum INT l SET errorSum=0 -初始化為0，即

13、無錯誤l /*-轉(zhuǎn)帳：張三的帳戶少1000元，李四的帳戶多1000元*/l UPDATE bank SET currentMoney=currentMoney-1000l WHERE customerName=張三l SET errorSum=errorSum+errorl UPDATE bank SET currentMoney=currentMoney+1000l WHERE customerName=李四l SET errorSum=errorSum+error -累計是否有錯誤l Print 查看轉(zhuǎn)賬事務(wù)中的余額l Select * FROM bank 開始事務(wù)（指定事務(wù)從此處開始，后

14、續(xù)的T-SQL語句都是一個整體）累計是否有錯誤l IF errorSum0 -如果有錯誤l BEGINl print 交易失敗，回滾事務(wù)l ROLLBACK TRANSACTION l END l ELSEl BEGINl print 交易成功，提交事務(wù)，寫入硬盤，永久的保存l COMMIT TRANSACTION l ENDl GOl print 查看轉(zhuǎn)帳事務(wù)后的余額l SELECT * FROM bank l GO 根據(jù)是否有錯誤，確定事務(wù)是提交還是撤銷如果有錯，則回滾操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束l演示：轉(zhuǎn)帳1000，轉(zhuǎn)帳失敗的情況轉(zhuǎn)帳事務(wù)前轉(zhuǎn)帳事務(wù)過程中轉(zhuǎn)帳事務(wù)結(jié)束后l轉(zhuǎn)

15、帳800，轉(zhuǎn)帳成功的情況:轉(zhuǎn)帳事務(wù)前轉(zhuǎn)帳事務(wù)過程中轉(zhuǎn)帳事務(wù)結(jié)束后事務(wù)的嵌套事務(wù)的嵌套l 雖然事務(wù)支持嵌套，但是與其他的嵌套方式不同，嵌套事務(wù)并不把嵌套的語句完全執(zhí)行完畢，而是將內(nèi)層事務(wù)的Commit語句忽略，只提交外層的Commit語句。l 由于事務(wù)具有原子性，事務(wù)內(nèi)的所有語句操作都是一個單元，要么全都執(zhí)行，要么全都不執(zhí)行，所以在內(nèi)層事務(wù)里的Commit都是沒有意義的。雖然沒有意義，但是也不能將其省略，因為事務(wù)是以Commit和Rollback語句作為事務(wù)結(jié)束標志的，如果省略系統(tǒng)無法知道內(nèi)層語句是到哪里結(jié)束l 意味著一個事務(wù)還未完成,一個新的事務(wù)也可以開始。l Begin Transactio

16、nl Begin Transactionl Commit Transactionl Commit Transactionl -創(chuàng)建一個嵌套事務(wù)，在外層事務(wù)添加兩條記錄，在內(nèi)層事務(wù)里添加一條記錄l -查看事務(wù)執(zhí)行之前的記錄l select * from 產(chǎn)品l -開始事務(wù)l begin transactionl -插入兩條記錄l insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(西瓜汁,1)l if error0 or rowcount1l goto TranRollBackl insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(獼猴桃汁,1)l if error0 or rowcou

17、nt1l goto TranRollBackl -查看在事務(wù)嵌套執(zhí)行之前的記錄情況l select * from 產(chǎn)品l -嵌套開始l begin tranl insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(可樂,1)l if error0 or rowcount1l rollback tranl elsel commit tranl -嵌套結(jié)束l -查看事務(wù)嵌套后的記錄情況l select * from 產(chǎn)品l if error0l beginl TranRollBack:l rollback tranl endl elsel commit tranl -查看所有事務(wù)完成后的情況l

18、select * from 產(chǎn)品l事務(wù)執(zhí)行之前的記錄l事務(wù)嵌套執(zhí)行之前的記錄情況l嵌套后的記錄情況l所有事務(wù)完成后的情況l 修改嵌套事務(wù)代碼，讓內(nèi)嵌套事務(wù)插入一條已存在的產(chǎn)品名稱，由于產(chǎn)品名具有唯一性約束，插入的時候會出現(xiàn)錯誤，然后再內(nèi)嵌事務(wù)后再插入一條記錄，看看結(jié)果？l “牛奶存在”l -嵌套開始l begin tranl insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(牛奶,1)l if error0 or rowcount1l rollback tranl elsel commit tranl -嵌套結(jié)束l -在外層事務(wù)里再插入一條記錄l insert 產(chǎn)品(產(chǎn)品名稱,類別ID)

19、 values(可樂,1)l if error0 or rowcount1l rollback tranl elsel commit tranl -查看插入記錄后的結(jié)果l select * from 產(chǎn)品l 從運行結(jié)果所示的截圖我們可以看出，在事務(wù)執(zhí)行之前，數(shù)據(jù)表里只有三條記錄，在插入兩條記錄后，數(shù)據(jù)表里有了五條記錄，此時進入到了事務(wù)嵌套，在嵌套事務(wù)里插入了一條記錄，該記錄違反了唯一性約束，插入失敗，此時事務(wù)回滾到外層事務(wù)開始之前的狀態(tài)，也就是只有三條記錄的狀態(tài)，在內(nèi)層嵌套結(jié)束之后，繼續(xù)執(zhí)行外層事務(wù)的代碼，在插入一條記錄，此時插入的記錄的是正確的，所以外層事務(wù)沒有回滾，最后的結(jié)果就是只插入了一

20、條記錄l 嵌套事務(wù)通常針對存儲過程或觸發(fā)器設(shè)計的，如果兩個存儲過程都用了事務(wù)，而且這兩個存儲過程是嵌套關(guān)系，那么事務(wù)也變成了嵌套事務(wù)了事務(wù)的保存點事務(wù)的保存點l 再之前的嵌套事務(wù)中，內(nèi)層嵌套事務(wù)回滾時，并沒有回滾到內(nèi)層嵌套事務(wù)的開始狀態(tài)，而是回滾到外層事務(wù)的開始狀態(tài)，然而在現(xiàn)實運用中，往往只需要回滾其中一部分操作即回滾到內(nèi)層嵌套事務(wù)開始狀態(tài)，用什么方法呢？l 在 SQL SERVER 2005中，為事務(wù)提供了一個“事務(wù)保存點“，用來保存事務(wù)當(dāng)前所在的位置，設(shè)置好事務(wù)保存點之后，可以讓事務(wù)回滾到該保存點時的狀態(tài)。l 設(shè)置事務(wù)保存點的語法：l SAVE tran|transaciton savep

21、oint_name|savepoint_variablel 其中： savepoint_name為保存點名稱l savepoint_variable為保存點名稱變量l 在設(shè)置完事務(wù)保存點之后，可以事務(wù)回滾到某一個保存點，回滾方式：l Rollback tran|transaction savepoint_name| savepoint_variablel 實例：在嵌套事務(wù)開始之前做事務(wù)保存點，然后再嵌套事務(wù)插入一個存在的值，然后違反唯一性約束數(shù)據(jù)插入報錯，然后事務(wù)回滾，看是否回滾到外層事務(wù)初始狀態(tài)還是內(nèi)層嵌套事務(wù)初始狀態(tài)?。縧 事務(wù)執(zhí)行之前的記錄情況：l 事務(wù)嵌套之前插入兩條記錄后的情況：l

22、嵌套后的記錄情況，由于違反約束，記錄回滾到事務(wù)的保存點，而不是外層事務(wù)的初始情況，所以應(yīng)該是五條記錄：l 在外層事務(wù)里再插入一條記錄，然后看記錄情況，應(yīng)該是六條記錄，嵌套事務(wù)回滾后五條記錄加上外層事務(wù)又插入的一條共計六條記錄：l 最后在所有事務(wù)都執(zhí)行完畢之后數(shù)據(jù)的記錄情況應(yīng)該為六條記錄：數(shù)據(jù)并發(fā)數(shù)據(jù)并發(fā)l 數(shù)據(jù)并發(fā)就是多個事務(wù)同時操作一個數(shù)據(jù)，這樣會引發(fā)一系列問題：l 1. 臟讀（臟讀（Dirty Read）：）：l 臟讀是指某個事務(wù)（A）讀取另外事務(wù)（B）尚未提交的更改數(shù)據(jù)，并在這個數(shù)據(jù)的基礎(chǔ)上操作。如果恰巧 B事務(wù)回滾，那么 A事務(wù)讀到的數(shù)據(jù)根本是不被承認的。l 2. 不可重復(fù)讀（不可重復(fù)

23、讀（Unrepeatable Read）：）：l 不可重復(fù)讀是指 A事務(wù)讀取了 B事務(wù)已經(jīng)提交的更改數(shù)據(jù)。 l3. 幻象讀幻象讀（Phantom Read）：lA事務(wù)讀取B事務(wù)提交的新增數(shù)據(jù)，這時 A事務(wù)將出現(xiàn)幻象讀的問題。l4. 第一類丟失更新：第一類丟失更新：lA事務(wù)撤銷時，把已經(jīng)提交的B事務(wù)的更新數(shù)據(jù)覆蓋了。l5. 第二類丟失更新第二類丟失更新 ：l A事務(wù)覆蓋B事務(wù)已經(jīng)提交的數(shù)據(jù)，造成 B事務(wù)所做操作丟失。 事務(wù)的隔離級別l 事務(wù)具有隔離性，因為事務(wù)中所有使用的數(shù)據(jù)和其他的事務(wù)進行隔離，在同一時間可以有很多事務(wù)正在處理數(shù)據(jù)，但是每個數(shù)據(jù)在同一時刻只能有一個事務(wù)來進行操作，將數(shù)據(jù)鎖定可以

24、防止其他事務(wù)的影響本事務(wù)的操作，但是這樣只能讓使用該數(shù)據(jù)的事務(wù)進行排隊等待，這樣影響了數(shù)據(jù)庫使用效率，甚至可能幾個數(shù)據(jù)同時鎖定了自己，又同時在等待其他事務(wù)在釋放，造成了死鎖l 事務(wù)的隔離級別就是用來設(shè)置事務(wù)在讀取數(shù)據(jù)時的隔離狀態(tài)，這是提高數(shù)據(jù)并發(fā)使用效率的一種有效手段。事務(wù)隔離級別由低到高分為五個級別l READ UNCOMMITTED：不隔離數(shù)據(jù)，指定語句可以讀取已由其他事務(wù)修改但尚未提交的行。l READ COMMITTED：指定語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)。這樣可以避免臟讀。l REPEATABLE READ：指定語句不能讀取已由其他事務(wù)修改但尚未提交的行，并且指定，其他

25、任何事務(wù)都不能在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)。l 高一層次，不會發(fā)生臟讀和不可重復(fù)讀。這表明查詢中使用的所有數(shù)據(jù)都會被鎖定，其它事務(wù)不能更新這些數(shù)據(jù)。此選項會影響系統(tǒng)的效能l SNAPSHOT：快照隔離，指定事務(wù)中任何語句讀取的數(shù)據(jù)都將是在事務(wù)開始時便存在的數(shù)據(jù)的事務(wù)上一致的版本。l SERIALIZABLE：指定：l （1）語句不能讀取已由其他事務(wù)修改但尚未提l 交的數(shù)據(jù)；l （2）任何其他事務(wù)都不能在當(dāng)前事務(wù)完成之前l(fā) 修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)；l （3）在當(dāng)前事務(wù)完成之前，其他事務(wù)不能使用l 當(dāng)前事務(wù)中任何語句讀取的鍵值插入新行。使用該級別的事務(wù)并發(fā)性最低，要讀取同一數(shù)據(jù)的事務(wù)必須排隊進行等待。l 最高級別的,這時事務(wù)被完全隔離。此選項會嚴重影響系統(tǒng)的效能設(shè)置事務(wù)隔離級別語法：l SET TRANSACTION ISOLATION LEVELl READ UNCOMMITTEDl | RE