詳解SQL Server中的事務與鎖問題

一  概述

在數(shù)據(jù)庫方面，對于非DBA的程序員來說，事務與鎖是一大難點，針對該難點，本篇文章試圖采用圖文的方式來與大家一起探討。

“淺談SQL Server 事務與鎖”這個專題共分兩篇，上篇主講事務及事務一致性問題，并簡略的提及一下鎖的種類和鎖的控制級別。

下篇主講SQL Server中的鎖機制，鎖控制級別和死鎖的若干問題。

二   事務

1   何為事務

 預覽眾多書籍，對于事務的定義，不同文獻不同作者對其雖有細微差別卻大致統(tǒng)一，我們將其抽象概括為：

事務：指封裝且執(zhí)行單個或多個操作的單個工作單元，在SqlServer中，其定義表現(xiàn)為顯示定義和隱式定義兩種方式。
 基于如上的定義，我們可以將事務解剖拆分為如下幾個點：

(1)事務是單個工作單元，這一定義，才使事務具有ACID屬性

(2)事務是封裝操作的，如封裝基本的CRUD操作

 --事務
 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)事務在封裝操作時，可以封裝單個操作，也可以封裝多個操作(封裝多個操作時，應注意與批處理的區(qū)別)

(4)在SqlServer中，事務的定義分為顯示定義和隱式定義兩種方式

 顯示定義：以Begin Tran作為開始，其中提交事務為Commit Tran，回滾事務為RollBack Tran，如我們在一個事務中插入兩條操作語句

--顯示定義事務
Begin Tran
INSERT INTO UserInfo VALUES('Alan_shanghai',30)
INSERT INTO UserInfo VALUES('Alan_beijing',35) Commit Tran

隱式定義：如果不顯示定義事務，SQL Server 默認把每個語句當作一個事務來處理(執(zhí)行完每個語句之后就自動提交事務)

2   事務的ACID屬性

 事務作為單個工作單元，該定義使其具有ACID屬性，ACID屬性指原子性(Atomicity)、一致性(Consisitency)、隔離性(Isolation)和持久性(Durability)。

(1)原子性(Atomicity)

原子性指事務必須是原子工作單元，即對于事務的封裝操作，要么全部執(zhí)行，要么全都不執(zhí)行。如下情況均會導致事務的撤銷或回滾。。。

a.事務提交之前，系統(tǒng)發(fā)生故障或重新啟動，SQL Server將會撤銷在事務中進行的所有操作；

b.事務處理中遇到錯誤，SQL Server通常會自動回滾事務，但也有少數(shù)例外；

c.一些不太嚴重的錯誤不會引發(fā)事務的自動回滾，如主鍵沖突，鎖超時等；

d.可以使用錯誤處理代碼來捕獲一些錯誤，并采取相應的操作，如把錯誤記錄在日志中，再回滾事務等；

(2)一致性(Consisitency)

一致性主要指數(shù)據(jù)一致性，即主要對象是數(shù)據(jù)。從宏觀上來說，指某一段時間區(qū)間，數(shù)據(jù)要保持一致性狀態(tài)，從微觀上來說，某個時間點數(shù)據(jù)要保持一致性狀態(tài)，我們舉個例子，

假若有兩個事務A和B對同一張表進行操作，A向表中寫數(shù)據(jù)，B向數(shù)據(jù)表中讀取數(shù)據(jù)，可以猜測，B讀取的數(shù)據(jù)大致有三種粗粒度可能：

第一種可能：A還沒向數(shù)據(jù)表中寫入數(shù)據(jù)的狀態(tài);

第二種可能：A已向數(shù)據(jù)表中寫入部分數(shù)據(jù)，但還未寫完的狀態(tài)；

第三種可能：A已向數(shù)據(jù)表中寫完數(shù)據(jù)；

如此，造成了事務的不一致性。

關(guān)于事務一致性，可能會發(fā)生 丟失更新，臟讀，不可重復讀和幻讀等問題，下文會詳細論述這些事務一致性問題。

(3)隔離性(Isolation)

隔離性指當兩個及其以上事務對同一邊界資源進行操作時，要控制好每個事務的邊界，控制好數(shù)據(jù)訪問機制，確保事務只能訪問處于期望的一致性級別下的數(shù)據(jù)。

在SQL Server中，一般采用鎖機制來控制，下文中，我們會詳細論述。

(4)持久性(Durability)

 我們對數(shù)據(jù)表進行操作時，一般會按照先后順序執(zhí)行如下兩步：

第一步：將對數(shù)據(jù)表操作寫入到磁盤上數(shù)據(jù)庫的事務日志中(持久還到磁盤事務日志中)；

第二步：完成第一步后，再將對數(shù)據(jù)表操作寫入到磁盤上數(shù)據(jù)庫的數(shù)據(jù)分區(qū)中(持久化到磁盤上數(shù)據(jù)庫分區(qū)中)；

關(guān)于如上兩步，我們來想想可能發(fā)生的問題：

問題1：完成如上第一步之前，系統(tǒng)發(fā)生故障(如系統(tǒng)異常，系統(tǒng)重啟)，數(shù)據(jù)庫引擎會怎么做？

 由于未完成第一步，提交指令還未記錄到磁盤的事務日志中，此時事務并未持久化，系統(tǒng)發(fā)生故障后，SQL Server

會檢查每個數(shù)據(jù)庫的事務日志，進行恢復處理(恢復處理一般分為重做階段和撤銷階段)，此時的恢復處理為重做階段，即提交指令還未記錄到磁盤的事務日志中，

數(shù)據(jù)庫引擎會撤銷這些事務所做的所有修改，這個過程也成為回滾。

問題2：完成如上第一步但還未完成第二步，系統(tǒng)發(fā)生故障(如系統(tǒng)異常，系統(tǒng)重啟)，數(shù)據(jù)庫引擎會怎么做？

 完成第一步后，提交指令已記錄到磁盤的事務日志中，無論數(shù)據(jù)操作是否被寫入到磁盤的數(shù)據(jù)分區(qū)，此時事務已持久化，系統(tǒng)發(fā)生故障后，SQL Server

會檢查每個數(shù)據(jù)庫的事務日志，進行恢復處理(恢復處理一般分為重做階段和撤銷階段)，此時的恢復處理為重做階段，即由于數(shù)據(jù)修改還沒有運用到數(shù)據(jù)分區(qū)的事務，

數(shù)據(jù)庫引擎會重做這些事務所做的所有修改，這個過程也成為前滾。

三   事務的隔離級別和隔離級別產(chǎn)生的一致性問題

1   未提交讀(READ UNCOMMITTED)

 未提交讀(READ UNCOMMITTED)指讀取未提交的數(shù)據(jù)，此時產(chǎn)生的數(shù)據(jù)不一致性，我們稱為數(shù)據(jù)臟讀。

1.1   未提交讀為什么會產(chǎn)生數(shù)據(jù)臟讀

未提交讀是最低級的隔離級別，在這個隔離級別運行的事務，讀操作是不需要請求共享鎖的，如果讀操作不需要共享鎖，就不會產(chǎn)生與持有排它鎖的事務操作發(fā)生沖突，

那么也就是說，在這個事務隔離級別，讀操作可以與寫操作同時進行，互不排斥，讀操作可以讀取寫操作未提交的修改，從而造成數(shù)據(jù)的不一致性，這種情況，我們稱

數(shù)據(jù)臟讀。

1.2   圖解數(shù)據(jù)臟讀

1.3   SQL演示數(shù)據(jù)臟讀

2   已提交讀(READ COMMITTED)

 已提交讀(READ COMMITTED)指只能讀取已提交事務的數(shù)據(jù)，是防止數(shù)據(jù)臟讀的最低隔離級別，也是SQL Server默認的隔離級別，它要求讀操作必須獲得共享鎖后

才能進行操作，防止讀取未提交的修改，雖然已提交讀能防止產(chǎn)生數(shù)據(jù)臟讀，但卻不可避免不可重復讀數(shù)據(jù)一致性問題。

2.1   為什么已提交讀能夠防止數(shù)據(jù)臟讀

已提交讀只允許讀取事務已提交的數(shù)據(jù)，它要求讀操作必須獲得共享鎖才能盡心操作，而讀操作的共享鎖與寫操作的排他鎖是互斥的，兩者互斥會發(fā)生沖突，所以讀操作

在讀取數(shù)據(jù)時，必須等待寫操作完成后，才能獲取共享鎖，然后才能讀取數(shù)據(jù)，此時讀取的數(shù)據(jù)是已經(jīng)提交結(jié)束的數(shù)據(jù)，因此就防止了數(shù)據(jù)臟讀的問題。

2.2   SQL演示已提交讀

2.3   為什么已提交讀會產(chǎn)生不可重復讀問題

我們知道，雖然已提交讀能獲得共享鎖，然而，讀操作一完成，就會立即釋放資源上的共享鎖(該操作不會在事務持續(xù)期間一致保留共享鎖)，如此就會產(chǎn)生一個問題，

即在一個事務處理內(nèi)部對相同數(shù)據(jù)資源讀操作之間，沒有共享鎖會鎖定該資源，導致其他事務可以在兩個讀操作之間更改數(shù)據(jù)資源，讀操作因而可能每次得到不同的

取值，這種現(xiàn)象稱為數(shù)據(jù)的不可重復讀。

2.4   圖解不可重復讀

3  可重復讀(REPEATABLE READ)

 為了防止不可重復讀現(xiàn)象，SQL Sever中采用隔離級別升級的方式，即將已提交讀升級為可重復讀。在可重復讀隔離級別下，事務中的讀操作不僅能獲得共享鎖，

而且獲得的共享鎖一直保持到事務完成為止， 在該事務完成之前，其他事務不可能獲得排他鎖來修改這一數(shù)據(jù)，如此，便實現(xiàn)了可重復讀，防止了不可重復讀造

成的數(shù)據(jù)不一致性?？芍貜妥x不僅能解決不可重復讀數(shù)據(jù)不一致性問題，還能解決丟失更新問題。然而，可重復讀也存在問題，那就是死鎖和幻讀等問題。

3.1 SQL演示可重復讀

3.2  何為丟失更新？

在比可重復讀低的隔離級別中，兩個事務在讀取數(shù)據(jù)之后就不再持有該資源的任何鎖，此時，兩個事務都能更新這個值，

從而發(fā)生最后事務更新的值覆蓋前面事務更新的值，從而造成數(shù)據(jù)的丟失，這稱為丟失更新。

3.3  圖解丟失更新

4   可序列化(SERIALIZABLE)

4.1   何為幻讀？

我們知道，在可重復讀隔離級別下，讀事務持有的共享鎖一直保持到該事務完成為止，但是事務只鎖定查詢第一次運行時找到的那些數(shù)據(jù)資源(如，行)，

而不會鎖定查詢結(jié)果范圍以外的其他行(其實，控制事務時，有數(shù)據(jù)庫架構(gòu)級別，表，頁和行等)。因此，在同一事務中進行第二次讀取之前，若其他事

務插入新行，并且新行能滿足讀操作的查詢過濾條件，那么這些新行也會出現(xiàn)在第二次讀操作返回的結(jié)果中，這些新行稱為幻影子，也叫做幻讀。

4.2  圖解幻讀

 4.3  如何解決幻讀？

SQL SERVER中，更高級別的可序列化(SERIALIZABLE)能夠解決該問題。

4.4  何為可序列化(SERIALIZABLE)？

大多數(shù)時候，可序列化(SERIALIZABLE)隔離級別的處理方式和可重復都得處理方式是類似的，只不過，可序列化(SERIALIZABLE)隔離級別

增加了一個新的內(nèi)容——邏輯上，這個隔離級別會讓讀操作鎖定滿足查詢搜索條件的鍵的整范圍，這就意味著讀操作不僅鎖定了滿足查詢搜索

條件的現(xiàn)有的那些行，還鎖定了未來可能滿足查詢搜索條件的行。

5   SNAPSHOT

 略。

四    事務的隔離級別總結(jié)

下表總結(jié)了每種隔離級別與邏輯一致性問題，檢測沖突和行版本控制之間關(guān)系

五   鎖定

1  兩種并發(fā)控制模型

關(guān)于并發(fā)控制模型，主要有兩種，即悲觀控制模型和樂觀控制模型。

(1)悲觀控制模型： 該模型假設(shè)總是存在多個事務對同一資源操作(讀/寫)，即假定沖突總是會發(fā)生。在SQL Server中，采用事務

隔離級別來控制(也可叫做采用鎖來控制)。一般在事務發(fā)生沖突前進行控制，也叫事前控制；

(2)樂觀控制模型：該模型與悲觀控制模型是對立的，即該模型總是假設(shè)系統(tǒng)中并不存在或較少存在多個事務對同一資源操作(讀/寫)

，即假定沖突是不會發(fā)生的或很少發(fā)生的。在SQL Server中，采用行版本控制來處理。一般在事務發(fā)生沖突后進行控制，也叫事后

控制；

2 何為鎖定及鎖定的種類

2.1  何為鎖定

鎖定，指在并發(fā)操作時，確保數(shù)據(jù)的一致性所采用的一種手段。在SQL Server中，采用鎖機制與事務隔離級別來控制數(shù)據(jù)的一致性，

2.2 鎖定的種類

常用的四大類鎖包括：共享鎖，意向鎖，更新鎖和排他鎖。

(1)共享鎖：在SQL SERVER中，當事務要讀取數(shù)據(jù)時，需要獲取共享鎖。

(2)意向鎖：在SQL SERVER中，準確來說，意向鎖并不是一種獨立的鎖，其主要作用在于獲取鎖的控制粒度(如，頁，表，行等)。

(3)更新鎖：在SQL SERVER中，準確來說，更新鎖并不是一種獨立的鎖，而是由共享鎖和排它鎖組成的混合鎖，其隔離級別高于共享鎖，

低于排他鎖，更新鎖能夠預防鎖升級而產(chǎn)生的死鎖。

(4)排它鎖：在SQL SERVER中，當事務要寫數(shù)據(jù)、更細數(shù)據(jù)和刪除數(shù)據(jù)時，需要獲取排他鎖。

3 鎖的控制粒度

 在SQL SERVER中，鎖可以控制表，頁和行等資源。

總結(jié)

以上所述是小編給大家介紹的SQL Server中的事務與鎖問題，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！