第8章 SQL Server _事務(wù)及鎖

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

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

3、rentMoney 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)帳測試：張三轉(zhuǎn)賬1000

4、元給李四-*/-我們可能會這樣這樣編寫語句-張三的帳戶少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元 總額多出了1000元！l錯誤原因分

5、析：錯誤原因分析：-張三的帳戶減少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ù)(TRANSACTION)是作為

6、單個邏輯工作單元執(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ù)庫操作失敗，已經(jīng)執(zhí)行的任何操作都必須撤銷

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

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

9、IT TRAN | TRANSACTION l transaction_name | tran_name_variable ; 事務(wù)控制事務(wù)控制lROLLBACK TRANSACTION ：該語句將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)內(nèi)的某個保存點(diǎn)。其語法如下：l ROLLBACK TRAN | TRANSACTION l transaction_name | tran_name_variablel | savepoint_name | savepoint_variable ; 事務(wù)的分類1自動提交事務(wù) 自動提交模式是 SQL Server 的默認(rèn)事務(wù)管理模式。每個 SQL 語句在完成時(shí)，

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

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

12、的事務(wù)鏈。l語法 SET IMPLICIT_TRANSACTIONS ON | OFF 當(dāng)設(shè)置為 ON 時(shí)，SET IMPLICIT_TRANSACTIONS 將連接設(shè)置為隱性事務(wù)模式。當(dāng)設(shè)置為 OFF 時(shí)，則使連接返回到自動提交事務(wù)模。 使用事務(wù)解決銀行轉(zhuǎn)帳問題lPrint 查看轉(zhuǎn)賬事務(wù)之前的余額lSelect * FROM bank lBEGIN TRANSACTION l/*-定義變量，用于累計(jì)事務(wù)執(zhí)行過程中的錯誤-*/lDECLARE errorSum INT lSET errorSum=0 -初始化為0，即無錯誤l/*-轉(zhuǎn)帳：張三的帳戶少1000元，李四的帳戶多1000元*/lUPD

13、ATE bank SET currentMoney=currentMoney-1000l WHERE customerName=張三lSET errorSum=errorSum+errorlUPDATE bank SET currentMoney=currentMoney+1000l WHERE customerName=李四lSET errorSum=errorSum+error -累計(jì)是否有錯誤lPrint 查看轉(zhuǎn)賬事務(wù)中的余額lSelect * FROM bank 開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個整體）累計(jì)是否有錯誤lIF errorSum0 -如果有錯誤l B

14、EGINl print 交易失敗，回滾事務(wù)l ROLLBACK TRANSACTION l END lELSEl BEGINl print 交易成功，提交事務(wù)，寫入硬盤，永久的保存l COMMIT TRANSACTION l ENDlGOlprint 查看轉(zhuǎn)帳事務(wù)后的余額lSELECT * FROM bank lGO 根據(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)帳800，轉(zhuǎn)帳成功的情況:轉(zhuǎn)帳事務(wù)前轉(zhuǎn)帳事務(wù)過程中轉(zhuǎn)帳事務(wù)結(jié)束后事務(wù)的嵌套事務(wù)的嵌套l雖然事務(wù)支持嵌套，

15、但是與其他的嵌套方式不同，嵌套事務(wù)并不把嵌套的語句完全執(zhí)行完畢，而是將內(nèi)層事務(wù)的Commit語句忽略，只提交外層的Commit語句。l由于事務(wù)具有原子性，事務(wù)內(nèi)的所有語句操作都是一個單元，要么全都執(zhí)行，要么全都不執(zhí)行，所以在內(nèi)層事務(wù)里的Commit都是沒有意義的。雖然沒有意義，但是也不能將其省略，因?yàn)槭聞?wù)是以Commit和Rollback語句作為事務(wù)結(jié)束標(biāo)志的，如果省略系統(tǒng)無法知道內(nèi)層語句是到哪里結(jié)束l意味著一個事務(wù)還未完成,一個新的事務(wù)也可以開始。lBegin Transactionl Begin Transactionl Commit TransactionlCommit Transact

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

17、in tranl insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(可樂,1)l if error0 or rowcount1l rollback tranl elsel commit tranl-嵌套結(jié)束l-查看事務(wù)嵌套后的記錄情況lselect * from 產(chǎn)品lif error0l beginl TranRollBack:l rollback tranl endlelsel commit tranl-查看所有事務(wù)完成后的情況lselect * from 產(chǎn)品l事務(wù)執(zhí)行之前的記錄l事務(wù)嵌套執(zhí)行之前的記錄情況l嵌套后的記錄情況l所有事務(wù)完成后的情況l修改嵌套事務(wù)代碼，讓內(nèi)嵌套事務(wù)插入

18、一條已存在的產(chǎn)品名稱，由于產(chǎn)品名具有唯一性約束，插入的時(shí)候會出現(xiàn)錯誤，然后再內(nèi)嵌事務(wù)后再插入一條記錄，看看結(jié)果？l“牛奶存在”l-嵌套開始lbegin tranl insert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(牛奶,1)l if error0 or rowcount1l rollback tranl elsel commit tranl-嵌套結(jié)束l-在外層事務(wù)里再插入一條記錄linsert 產(chǎn)品(產(chǎn)品名稱,類別ID) values(可樂,1)l if error0 or rowcount1l rollback tranl elsel commit tranl-查看插入記錄后的結(jié)果ls

19、elect * from 產(chǎn)品l從運(yùn)行結(jié)果所示的截圖我們可以看出，在事務(wù)執(zhí)行之前，數(shù)據(jù)表里只有三條記錄，在插入兩條記錄后，數(shù)據(jù)表里有了五條記錄，此時(shí)進(jìn)入到了事務(wù)嵌套，在嵌套事務(wù)里插入了一條記錄，該記錄違反了唯一性約束，插入失敗，此時(shí)事務(wù)回滾到外層事務(wù)開始之前的狀態(tài)，也就是只有三條記錄的狀態(tài)，在內(nèi)層嵌套結(jié)束之后，繼續(xù)執(zhí)行外層事務(wù)的代碼，在插入一條記錄，此時(shí)插入的記錄的是正確的，所以外層事務(wù)沒有回滾，最后的結(jié)果就是只插入了一條記錄l嵌套事務(wù)通常針對存儲過程或觸發(fā)器設(shè)計(jì)的，如果兩個存儲過程都用了事務(wù)，而且這兩個存儲過程是嵌套關(guān)系，那么事務(wù)也變成了嵌套事務(wù)了事務(wù)的保存點(diǎn)事務(wù)的保存點(diǎn)l再之前的嵌套事務(wù)中

20、，內(nèi)層嵌套事務(wù)回滾時(shí)，并沒有回滾到內(nèi)層嵌套事務(wù)的開始狀態(tài)，而是回滾到外層事務(wù)的開始狀態(tài)，然而在現(xiàn)實(shí)運(yùn)用中，往往只需要回滾其中一部分操作即回滾到內(nèi)層嵌套事務(wù)開始狀態(tài)，用什么方法呢？l在 SQL SERVER 2005中，為事務(wù)提供了一個“事務(wù)保存點(diǎn)“，用來保存事務(wù)當(dāng)前所在的位置，設(shè)置好事務(wù)保存點(diǎn)之后，可以讓事務(wù)回滾到該保存點(diǎn)時(shí)的狀態(tài)。l設(shè)置事務(wù)保存點(diǎn)的語法：lSAVE tran|transaciton savepoint_name|savepoint_variablel其中： savepoint_name為保存點(diǎn)名稱lsavepoint_variable為保存點(diǎn)名稱變量l在設(shè)置完事務(wù)保存點(diǎn)之后，

21、可以事務(wù)回滾到某一個保存點(diǎn)，回滾方式：lRollback tran|transaction savepoint_name| savepoint_variablel實(shí)例：在嵌套事務(wù)開始之前做事務(wù)保存點(diǎn)，然后再嵌套事務(wù)插入一個存在的值，然后違反唯一性約束數(shù)據(jù)插入報(bào)錯，然后事務(wù)回滾，看是否回滾到外層事務(wù)初始狀態(tài)還是內(nèi)層嵌套事務(wù)初始狀態(tài)！？l事務(wù)執(zhí)行之前的記錄情況：l事務(wù)嵌套之前插入兩條記錄后的情況：l嵌套后的記錄情況，由于違反約束，記錄回滾到事務(wù)的保存點(diǎn)，而不是外層事務(wù)的初始情況，所以應(yīng)該是五條記錄：l在外層事務(wù)里再插入一條記錄，然后看記錄情況，應(yīng)該是六條記錄，嵌套事務(wù)回滾后五條記錄加上外層事務(wù)又插

22、入的一條共計(jì)六條記錄：l最后在所有事務(wù)都執(zhí)行完畢之后數(shù)據(jù)的記錄情況應(yīng)該為六條記錄：數(shù)據(jù)并發(fā)數(shù)據(jù)并發(fā)l數(shù)據(jù)并發(fā)就是多個事務(wù)同時(shí)操作一個數(shù)據(jù)，這樣會引發(fā)一系列問題：l1. 臟讀（臟讀（Dirty Read）：）：l臟讀是指某個事務(wù)（A）讀取另外事務(wù)（B）尚未提交的更改數(shù)據(jù)，并在這個數(shù)據(jù)的基礎(chǔ)上操作。如果恰巧 B事務(wù)回滾，那么 A事務(wù)讀到的數(shù)據(jù)根本是不被承認(rèn)的。l2. 不可重復(fù)讀（不可重復(fù)讀（Unrepeatable Read）：）：l不可重復(fù)讀是指 A事務(wù)讀取了 B事務(wù)已經(jīng)提交的更改數(shù)據(jù)。 l3. 幻象讀幻象讀（Phantom Read）：lA事務(wù)讀取B事務(wù)提交的新增數(shù)據(jù)，這時(shí) A事務(wù)將出現(xiàn)幻象讀

23、的問題。l4. 第一類丟失更新：第一類丟失更新：lA事務(wù)撤銷時(shí)，把已經(jīng)提交的B事務(wù)的更新數(shù)據(jù)覆蓋了。l5. 第二類丟失更新第二類丟失更新 ：l A事務(wù)覆蓋B事務(wù)已經(jīng)提交的數(shù)據(jù)，造成 B事務(wù)所做操作丟失。 事務(wù)的隔離級別l事務(wù)具有隔離性，因?yàn)槭聞?wù)中所有使用的數(shù)據(jù)和其他的事務(wù)進(jìn)行隔離，在同一時(shí)間可以有很多事務(wù)正在處理數(shù)據(jù)，但是每個數(shù)據(jù)在同一時(shí)刻只能有一個事務(wù)來進(jìn)行操作，將數(shù)據(jù)鎖定可以防止其他事務(wù)的影響本事務(wù)的操作，但是這樣只能讓使用該數(shù)據(jù)的事務(wù)進(jìn)行排隊(duì)等待，這樣影響了數(shù)據(jù)庫使用效率，甚至可能幾個數(shù)據(jù)同時(shí)鎖定了自己，又同時(shí)在等待其他事務(wù)在釋放，造成了死鎖l事務(wù)的隔離級別就是用來設(shè)置事務(wù)在讀取數(shù)據(jù)時(shí)的

24、隔離狀態(tài)，這是提高數(shù)據(jù)并發(fā)使用效率的一種有效手段。事務(wù)隔離級別由低到高分為五個級別lREAD UNCOMMITTED：不隔離數(shù)據(jù)，指定語句可以讀取已由其他事務(wù)修改但尚未提交的行。lREAD COMMITTED：指定語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)。這樣可以避免臟讀。lREPEATABLE READ：指定語句不能讀取已由其他事務(wù)修改但尚未提交的行，并且指定，其他任何事務(wù)都不能在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)。l高一層次，不會發(fā)生臟讀和不可重復(fù)讀。這表明查詢中使用的所有數(shù)據(jù)都會被鎖定，其它事務(wù)不能更新這些數(shù)據(jù)。此選項(xiàng)會影響系統(tǒng)的效能lSNAPSHOT：快照隔離，指定事務(wù)中任何語句讀取的數(shù)據(jù)都將是在事務(wù)開始時(shí)便存在的數(shù)據(jù)的事務(wù)上一致的版本。lSERIALIZABLE：指定：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ù)必須排隊(duì)進(jìn)行等待。l最高級別的,這時(shí)事務(wù)被完全隔離。此選項(xiàng)會嚴(yán)重影響系統(tǒng)的效能設(shè)置事務(wù)隔離級別語法：l SET TRANSACTION ISOLATION LEVELl READ UNCOMMITTEDl | READ CO