詳解SQL Server中的事務(wù)與鎖問(wèn)題
一 概述
在數(shù)據(jù)庫(kù)方面,對(duì)于非DBA的程序員來(lái)說(shuō),事務(wù)與鎖是一大難點(diǎn),針對(duì)該難點(diǎn),本篇文章試圖采用圖文的方式來(lái)與大家一起探討。
“淺談SQL Server 事務(wù)與鎖”這個(gè)專題共分兩篇,上篇主講事務(wù)及事務(wù)一致性問(wèn)題,并簡(jiǎn)略的提及一下鎖的種類和鎖的控制級(jí)別。
下篇主講SQL Server中的鎖機(jī)制,鎖控制級(jí)別和死鎖的若干問(wèn)題。
二 事務(wù)
1 何為事務(wù)
預(yù)覽眾多書(shū)籍,對(duì)于事務(wù)的定義,不同文獻(xiàn)不同作者對(duì)其雖有細(xì)微差別卻大致統(tǒng)一,我們將其抽象概括為:
事務(wù):指封裝且執(zhí)行單個(gè)或多個(gè)操作的單個(gè)工作單元,在SqlServer中,其定義表現(xiàn)為顯示定義和隱式定義兩種方式。
基于如上的定義,我們可以將事務(wù)解剖拆分為如下幾個(gè)點(diǎn):
(1)事務(wù)是單個(gè)工作單元,這一定義,才使事務(wù)具有ACID屬性
(2)事務(wù)是封裝操作的,如封裝基本的CRUD操作
--事務(wù) Begin Tran SELECT * FROM UserInfo INSERT INTO UserInfo VALUES('Alan_beijing',35) UPDATE UserInfo SET Age=31 WHERE UserName='Alan_beijing' DELETE UserInfo WHERE UserName='Alan_beijing' Commit Tran
(3)事務(wù)在封裝操作時(shí),可以封裝單個(gè)操作,也可以封裝多個(gè)操作(封裝多個(gè)操作時(shí),應(yīng)注意與批處理的區(qū)別)
(4)在SqlServer中,事務(wù)的定義分為顯示定義和隱式定義兩種方式
顯示定義:以Begin Tran作為開(kāi)始,其中提交事務(wù)為Commit Tran,回滾事務(wù)為RollBack Tran,如我們?cè)谝粋€(gè)事務(wù)中插入兩條操作語(yǔ)句
--顯示定義事務(wù) Begin Tran INSERT INTO UserInfo VALUES('Alan_shanghai',30) INSERT INTO UserInfo VALUES('Alan_beijing',35) Commit Tran
隱式定義:如果不顯示定義事務(wù),SQL Server 默認(rèn)把每個(gè)語(yǔ)句當(dāng)作一個(gè)事務(wù)來(lái)處理(執(zhí)行完每個(gè)語(yǔ)句之后就自動(dòng)提交事務(wù))
2 事務(wù)的ACID屬性
事務(wù)作為單個(gè)工作單元,該定義使其具有ACID屬性,ACID屬性指原子性(Atomicity)、一致性(Consisitency)、隔離性(Isolation)和持久性(Durability)。
(1)原子性(Atomicity)
原子性指事務(wù)必須是原子工作單元,即對(duì)于事務(wù)的封裝操作,要么全部執(zhí)行,要么全都不執(zhí)行。如下情況均會(huì)導(dǎo)致事務(wù)的撤銷或回滾。。。
a.事務(wù)提交之前,系統(tǒng)發(fā)生故障或重新啟動(dòng),SQL Server將會(huì)撤銷在事務(wù)中進(jìn)行的所有操作;
b.事務(wù)處理中遇到錯(cuò)誤,SQL Server通常會(huì)自動(dòng)回滾事務(wù),但也有少數(shù)例外;
c.一些不太嚴(yán)重的錯(cuò)誤不會(huì)引發(fā)事務(wù)的自動(dòng)回滾,如主鍵沖突,鎖超時(shí)等;
d.可以使用錯(cuò)誤處理代碼來(lái)捕獲一些錯(cuò)誤,并采取相應(yīng)的操作,如把錯(cuò)誤記錄在日志中,再回滾事務(wù)等;
(2)一致性(Consisitency)
一致性主要指數(shù)據(jù)一致性,即主要對(duì)象是數(shù)據(jù)。從宏觀上來(lái)說(shuō),指某一段時(shí)間區(qū)間,數(shù)據(jù)要保持一致性狀態(tài),從微觀上來(lái)說(shuō),某個(gè)時(shí)間點(diǎn)數(shù)據(jù)要保持一致性狀態(tài),我們舉個(gè)例子,
假若有兩個(gè)事務(wù)A和B對(duì)同一張表進(jìn)行操作,A向表中寫(xiě)數(shù)據(jù),B向數(shù)據(jù)表中讀取數(shù)據(jù),可以猜測(cè),B讀取的數(shù)據(jù)大致有三種粗粒度可能:
第一種可能:A還沒(méi)向數(shù)據(jù)表中寫(xiě)入數(shù)據(jù)的狀態(tài);
第二種可能:A已向數(shù)據(jù)表中寫(xiě)入部分?jǐn)?shù)據(jù),但還未寫(xiě)完的狀態(tài);
第三種可能:A已向數(shù)據(jù)表中寫(xiě)完數(shù)據(jù);
如此,造成了事務(wù)的不一致性。
關(guān)于事務(wù)一致性,可能會(huì)發(fā)生 丟失更新,臟讀,不可重復(fù)讀和幻讀等問(wèn)題,下文會(huì)詳細(xì)論述這些事務(wù)一致性問(wèn)題。
(3)隔離性(Isolation)
隔離性指當(dāng)兩個(gè)及其以上事務(wù)對(duì)同一邊界資源進(jìn)行操作時(shí),要控制好每個(gè)事務(wù)的邊界,控制好數(shù)據(jù)訪問(wèn)機(jī)制,確保事務(wù)只能訪問(wèn)處于期望的一致性級(jí)別下的數(shù)據(jù)。
在SQL Server中,一般采用鎖機(jī)制來(lái)控制,下文中,我們會(huì)詳細(xì)論述。
(4)持久性(Durability)
我們對(duì)數(shù)據(jù)表進(jìn)行操作時(shí),一般會(huì)按照先后順序執(zhí)行如下兩步:
第一步:將對(duì)數(shù)據(jù)表操作寫(xiě)入到磁盤(pán)上數(shù)據(jù)庫(kù)的事務(wù)日志中(持久還到磁盤(pán)事務(wù)日志中);
第二步:完成第一步后,再將對(duì)數(shù)據(jù)表操作寫(xiě)入到磁盤(pán)上數(shù)據(jù)庫(kù)的數(shù)據(jù)分區(qū)中(持久化到磁盤(pán)上數(shù)據(jù)庫(kù)分區(qū)中);
關(guān)于如上兩步,我們來(lái)想想可能發(fā)生的問(wèn)題:
問(wèn)題1:完成如上第一步之前,系統(tǒng)發(fā)生故障(如系統(tǒng)異常,系統(tǒng)重啟),數(shù)據(jù)庫(kù)引擎會(huì)怎么做?
由于未完成第一步,提交指令還未記錄到磁盤(pán)的事務(wù)日志中,此時(shí)事務(wù)并未持久化,系統(tǒng)發(fā)生故障后,SQL Server
會(huì)檢查每個(gè)數(shù)據(jù)庫(kù)的事務(wù)日志,進(jìn)行恢復(fù)處理(恢復(fù)處理一般分為重做階段和撤銷階段),此時(shí)的恢復(fù)處理為重做階段,即提交指令還未記錄到磁盤(pán)的事務(wù)日志中,
數(shù)據(jù)庫(kù)引擎會(huì)撤銷這些事務(wù)所做的所有修改,這個(gè)過(guò)程也成為回滾。
問(wèn)題2:完成如上第一步但還未完成第二步,系統(tǒng)發(fā)生故障(如系統(tǒng)異常,系統(tǒng)重啟),數(shù)據(jù)庫(kù)引擎會(huì)怎么做?
完成第一步后,提交指令已記錄到磁盤(pán)的事務(wù)日志中,無(wú)論數(shù)據(jù)操作是否被寫(xiě)入到磁盤(pán)的數(shù)據(jù)分區(qū),此時(shí)事務(wù)已持久化,系統(tǒng)發(fā)生故障后,SQL Server
會(huì)檢查每個(gè)數(shù)據(jù)庫(kù)的事務(wù)日志,進(jìn)行恢復(fù)處理(恢復(fù)處理一般分為重做階段和撤銷階段),此時(shí)的恢復(fù)處理為重做階段,即由于數(shù)據(jù)修改還沒(méi)有運(yùn)用到數(shù)據(jù)分區(qū)的事務(wù),
數(shù)據(jù)庫(kù)引擎會(huì)重做這些事務(wù)所做的所有修改,這個(gè)過(guò)程也成為前滾。
三 事務(wù)的隔離級(jí)別和隔離級(jí)別產(chǎn)生的一致性問(wèn)題
1 未提交讀(READ UNCOMMITTED)
未提交讀(READ UNCOMMITTED)指讀取未提交的數(shù)據(jù),此時(shí)產(chǎn)生的數(shù)據(jù)不一致性,我們稱為數(shù)據(jù)臟讀。
1.1 未提交讀為什么會(huì)產(chǎn)生數(shù)據(jù)臟讀
未提交讀是最低級(jí)的隔離級(jí)別,在這個(gè)隔離級(jí)別運(yùn)行的事務(wù),讀操作是不需要請(qǐng)求共享鎖的,如果讀操作不需要共享鎖,就不會(huì)產(chǎn)生與持有排它鎖的事務(wù)操作發(fā)生沖突,
那么也就是說(shuō),在這個(gè)事務(wù)隔離級(jí)別,讀操作可以與寫(xiě)操作同時(shí)進(jìn)行,互不排斥,讀操作可以讀取寫(xiě)操作未提交的修改,從而造成數(shù)據(jù)的不一致性,這種情況,我們稱
數(shù)據(jù)臟讀。
1.2 圖解數(shù)據(jù)臟讀
1.3 SQL演示數(shù)據(jù)臟讀
2 已提交讀(READ COMMITTED)
已提交讀(READ COMMITTED)指只能讀取已提交事務(wù)的數(shù)據(jù),是防止數(shù)據(jù)臟讀的最低隔離級(jí)別,也是SQL Server默認(rèn)的隔離級(jí)別,它要求讀操作必須獲得共享鎖后
才能進(jìn)行操作,防止讀取未提交的修改,雖然已提交讀能防止產(chǎn)生數(shù)據(jù)臟讀,但卻不可避免不可重復(fù)讀數(shù)據(jù)一致性問(wèn)題。
2.1 為什么已提交讀能夠防止數(shù)據(jù)臟讀
已提交讀只允許讀取事務(wù)已提交的數(shù)據(jù),它要求讀操作必須獲得共享鎖才能盡心操作,而讀操作的共享鎖與寫(xiě)操作的排他鎖是互斥的,兩者互斥會(huì)發(fā)生沖突,所以讀操作
在讀取數(shù)據(jù)時(shí),必須等待寫(xiě)操作完成后,才能獲取共享鎖,然后才能讀取數(shù)據(jù),此時(shí)讀取的數(shù)據(jù)是已經(jīng)提交結(jié)束的數(shù)據(jù),因此就防止了數(shù)據(jù)臟讀的問(wèn)題。
2.2 SQL演示已提交讀
2.3 為什么已提交讀會(huì)產(chǎn)生不可重復(fù)讀問(wèn)題
我們知道,雖然已提交讀能獲得共享鎖,然而,讀操作一完成,就會(huì)立即釋放資源上的共享鎖(該操作不會(huì)在事務(wù)持續(xù)期間一致保留共享鎖),如此就會(huì)產(chǎn)生一個(gè)問(wèn)題,
即在一個(gè)事務(wù)處理內(nèi)部對(duì)相同數(shù)據(jù)資源讀操作之間,沒(méi)有共享鎖會(huì)鎖定該資源,導(dǎo)致其他事務(wù)可以在兩個(gè)讀操作之間更改數(shù)據(jù)資源,讀操作因而可能每次得到不同的
取值,這種現(xiàn)象稱為數(shù)據(jù)的不可重復(fù)讀。
2.4 圖解不可重復(fù)讀
3 可重復(fù)讀(REPEATABLE READ)
為了防止不可重復(fù)讀現(xiàn)象,SQL Sever中采用隔離級(jí)別升級(jí)的方式,即將已提交讀升級(jí)為可重復(fù)讀。在可重復(fù)讀隔離級(jí)別下,事務(wù)中的讀操作不僅能獲得共享鎖,
而且獲得的共享鎖一直保持到事務(wù)完成為止, 在該事務(wù)完成之前,其他事務(wù)不可能獲得排他鎖來(lái)修改這一數(shù)據(jù),如此,便實(shí)現(xiàn)了可重復(fù)讀,防止了不可重復(fù)讀造
成的數(shù)據(jù)不一致性。可重復(fù)讀不僅能解決不可重復(fù)讀數(shù)據(jù)不一致性問(wèn)題,還能解決丟失更新問(wèn)題。然而,可重復(fù)讀也存在問(wèn)題,那就是死鎖和幻讀等問(wèn)題。
3.1 SQL演示可重復(fù)讀
3.2 何為丟失更新?
在比可重復(fù)讀低的隔離級(jí)別中,兩個(gè)事務(wù)在讀取數(shù)據(jù)之后就不再持有該資源的任何鎖,此時(shí),兩個(gè)事務(wù)都能更新這個(gè)值,
從而發(fā)生最后事務(wù)更新的值覆蓋前面事務(wù)更新的值,從而造成數(shù)據(jù)的丟失,這稱為丟失更新。
3.3 圖解丟失更新
4 可序列化(SERIALIZABLE)
4.1 何為幻讀?
我們知道,在可重復(fù)讀隔離級(jí)別下,讀事務(wù)持有的共享鎖一直保持到該事務(wù)完成為止,但是事務(wù)只鎖定查詢第一次運(yùn)行時(shí)找到的那些數(shù)據(jù)資源(如,行),
而不會(huì)鎖定查詢結(jié)果范圍以外的其他行(其實(shí),控制事務(wù)時(shí),有數(shù)據(jù)庫(kù)架構(gòu)級(jí)別,表,頁(yè)和行等)。因此,在同一事務(wù)中進(jìn)行第二次讀取之前,若其他事
務(wù)插入新行,并且新行能滿足讀操作的查詢過(guò)濾條件,那么這些新行也會(huì)出現(xiàn)在第二次讀操作返回的結(jié)果中,這些新行稱為幻影子,也叫做幻讀。
4.2 圖解幻讀
4.3 如何解決幻讀?
SQL SERVER中,更高級(jí)別的可序列化(SERIALIZABLE)能夠解決該問(wèn)題。
4.4 何為可序列化(SERIALIZABLE)?
大多數(shù)時(shí)候,可序列化(SERIALIZABLE)隔離級(jí)別的處理方式和可重復(fù)都得處理方式是類似的,只不過(guò),可序列化(SERIALIZABLE)隔離級(jí)別
增加了一個(gè)新的內(nèi)容——邏輯上,這個(gè)隔離級(jí)別會(huì)讓讀操作鎖定滿足查詢搜索條件的鍵的整范圍,這就意味著讀操作不僅鎖定了滿足查詢搜索
條件的現(xiàn)有的那些行,還鎖定了未來(lái)可能滿足查詢搜索條件的行。
5 SNAPSHOT
略。
四 事務(wù)的隔離級(jí)別總結(jié)
下表總結(jié)了每種隔離級(jí)別與邏輯一致性問(wèn)題,檢測(cè)沖突和行版本控制之間關(guān)系
五 鎖定
1 兩種并發(fā)控制模型
關(guān)于并發(fā)控制模型,主要有兩種,即悲觀控制模型和樂(lè)觀控制模型。
(1)悲觀控制模型: 該模型假設(shè)總是存在多個(gè)事務(wù)對(duì)同一資源操作(讀/寫(xiě)),即假定沖突總是會(huì)發(fā)生。在SQL Server中,采用事務(wù)
隔離級(jí)別來(lái)控制(也可叫做采用鎖來(lái)控制)。一般在事務(wù)發(fā)生沖突前進(jìn)行控制,也叫事前控制;
(2)樂(lè)觀控制模型:該模型與悲觀控制模型是對(duì)立的,即該模型總是假設(shè)系統(tǒng)中并不存在或較少存在多個(gè)事務(wù)對(duì)同一資源操作(讀/寫(xiě))
,即假定沖突是不會(huì)發(fā)生的或很少發(fā)生的。在SQL Server中,采用行版本控制來(lái)處理。一般在事務(wù)發(fā)生沖突后進(jìn)行控制,也叫事后
控制;
2 何為鎖定及鎖定的種類
2.1 何為鎖定
鎖定,指在并發(fā)操作時(shí),確保數(shù)據(jù)的一致性所采用的一種手段。在SQL Server中,采用鎖機(jī)制與事務(wù)隔離級(jí)別來(lái)控制數(shù)據(jù)的一致性,
2.2 鎖定的種類
常用的四大類鎖包括:共享鎖,意向鎖,更新鎖和排他鎖。
(1)共享鎖:在SQL SERVER中,當(dāng)事務(wù)要讀取數(shù)據(jù)時(shí),需要獲取共享鎖。
(2)意向鎖:在SQL SERVER中,準(zhǔn)確來(lái)說(shuō),意向鎖并不是一種獨(dú)立的鎖,其主要作用在于獲取鎖的控制粒度(如,頁(yè),表,行等)。
(3)更新鎖:在SQL SERVER中,準(zhǔn)確來(lái)說(shuō),更新鎖并不是一種獨(dú)立的鎖,而是由共享鎖和排它鎖組成的混合鎖,其隔離級(jí)別高于共享鎖,
低于排他鎖,更新鎖能夠預(yù)防鎖升級(jí)而產(chǎn)生的死鎖。
(4)排它鎖:在SQL SERVER中,當(dāng)事務(wù)要寫(xiě)數(shù)據(jù)、更細(xì)數(shù)據(jù)和刪除數(shù)據(jù)時(shí),需要獲取排他鎖。
3 鎖的控制粒度
在SQL SERVER中,鎖可以控制表,頁(yè)和行等資源。
總結(jié)
以上所述是小編給大家介紹的SQL Server中的事務(wù)與鎖問(wèn)題,希望對(duì)大家有所幫助,如果大家有任何疑問(wèn)請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)本站網(wǎng)站的支持!
版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。