sql server 鎖與事務撥雲見日(中)

一.事務的概述上一章節里，重點講到了鎖，以及鎖與事務的關係。離上篇發佈時間好幾天了，每天利用一點空閑時間還真是要堅持。聽《明朝那些事兒》中講到"人與人最小的差距是聰明，人與人最大的差距是堅持"很經典的一句話一直記得。這篇重點圍繞事務來開展。涉及的知識點包括：事務的概述，事務併發控制模型，併發產生的 ...

一.事務的概述

　　上一章節里，重點講到了鎖，以及鎖與事務的關係。離上篇發佈時間好幾天了，每天利用一點空閑時間還真是要堅持。聽《明朝那些事兒》中講到"人與人最小的差距是聰明，人與人最大的差距是堅持"很經典的一句話一直記得。這篇重點圍繞事務來開展。涉及的知識點包括：事務的概述，事務併發控制模型，併發產生的負面影響，事務隔離級別以及不同的表現。本章多以文字描述為主，沒有多少代碼量，重點是闡述不同隔離級別的不同表現，在以後的業務中，涉及到事務時，本文可以用來做個參考。

1.1 事務ACID

　事務作為一個邏輯工作單元執行一系列的操作，它包括四個屬性：原子性、一致性、隔離性和持久性 (ACID) 屬性, 只有這樣才能成為一個事務。　　

　原子性：當一個事務被當作一個單獨的工作單元時，不管事務內有什麼，都是一個整體。對於其數據修改，要麼全都執行，要麼全都不執行。　　

一致性：事務在完成時，必須使所有的數據都保持一個邏輯一致狀態。　　

　　隔離性：併發事務所做的修改必須與其他併發事務所做的修改隔離。事務能識別數據所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是併發事務修改它之後的狀態。

　　持久性：一但事務完全，它的效果是永久存於系統的。該修改即使出現系統故障也將一直保持。 SQL Server 2014和更高版本啟用延遲的持久事務。

1.2 事務的操作模式有幾下幾種：

　　自動提交事務：每條單獨的語句都是一個事務。

　　顯式事務：每個事務均以 BEGIN TRANSACTION 語句顯式開始，以 COMMIT 或 ROLLBACK 語句顯式結束。

　　隱式事務：在前一個事務完成時新事務隱式啟動，但每個事務仍以 COMMIT 或 ROLLBACK 語句顯式完成。

　　批處理級事務：只能應用於多個活動結果集 (MARS)，在 MARS 會話中啟動的 Transact-SQL 顯式或隱式事務變為批處理級事務。在sql server 2000 必須對每個 SqlCommand 對象使用獨立的 SqlConnection 對象。但是 SQL Server 2005 啟用了 MARS，可以共用一個SqlConnection 對象。

本章重點講到顯式事務的隔離級別

二. 事務併發模型

　　2.1　併發訪問是指：多用戶同時訪問一種資源被視為併發訪問資源。併發數據訪問需要某些機制，以防止多個用戶試圖修改其他用戶正在使用的資源時產生負面影響，機制就是下麵講的事務隔離級別。處於活動狀態而不互相干涉的併發用戶數據越多，併發性就越好。當一個正在修改數據的用戶阻止了其他用戶讀取數據，或者當一個正在讀取數據的用戶阻止了其它用戶修改數據時，併發性就降低了。

　　2.2　併發類型

　　　　在sqlserver里資料庫系統可以採用兩種方式來管理併發數據訪問：樂觀併發控制和悲觀併發控制，在sql server 2000以前只有悲觀併發。樂觀併發控制是一種稱為行版本控制(row versioning)的技術支持。這二種技術併發控制的區別在於：是在衝突發生前進行防止，還是在發生後採用某種方法來處理衝突。

　　悲觀併發控制

　　　在悲觀併發中，sql server是獲取鎖來阻塞對於其它用戶正在使用數據的訪問。用戶操作的讀與寫之間是會互相阻塞的。

樂觀併發控制

　　　　樂觀併發控制預設採用行版本控制使其它用戶能夠看到修改操作發生以前的數據狀態，舊版本數據行會保存下來。因些讀取數據不會受到其它用戶正對該數據進行修改操作的影響，換言之修改數據不會受到其它用戶正對該數據進行讀取影響。因為讀取用戶訪問的數據行是一個被保存過的版本。用戶讀與寫之間不會互相阻塞，但寫與寫還是會發生阻塞。

　　2.3 事務併發帶來的負面影響

　　　修改數據的用戶會影響同時讀取或修改相同數據的其他用戶。即這些用戶可以併發訪問數據。如果數據存儲系統沒有併發控制，則用戶可能會看到以下負面影響：

併發影響	定義
丟失更新	當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其他事務的存在。最後的更新將覆蓋由其他事務所做的更新，這將導致數據丟失。
臟讀	當一個用戶修改了數據但尚未提交修改，而另一個正在讀取的用戶會讀到這個修改從而導致不一致的狀態發生。
不可重覆讀	一個用戶在同一個事務中分別以兩個讀操作間隔讀取相同資源時可能會得到不同的值。
虛擬讀取（幻影）	一個事務里執行兩個相同的查詢，但第二個查詢返回的行集合是不同的，此時就會發生虛擬讀取。這種情況發生在where 查詢中，比如 where count(1)<10。同一個事務中多次使用相同的條件查詢，select操作返回不同數據的結果集。

三.事務隔離級別

　　在sql server 2005及以上支持五種隔離級別來控制“讀”操作的行為，其中有三個是悲觀併發模式，一個是樂觀併發模式，剩下一個存在兩種模式。下麵介紹隔離級別從允許的併發負作用（例如臟讀或虛擬讀取）的角度進行描述。

隔離級別	定義
未提交讀 READUNCOMMITTED	隔離事務的最低級別，未提交讀不會發出共用鎖，允許臟讀，一個事務可能看見其他事務所做的尚未提交的更改。未提交讀不會發出共用鎖. 該項的作用與與SELECT表上加NOLOCK相同。
已提交讀 READ COMMITTED	一個事務不能讀取其它事務修改但未提交的數據，避免了臟讀。事務內語句運行完後便會釋放共用鎖，而不是等到事務提交的時候。這是資料庫引擎預設級別。
可重覆讀 REPEATABLE READ	事務內查詢語句運行完後不會釋放共用鎖，而是等到事務提交後.其它事務不能修改，刪除，但可以插入新數據。因為不是範圍鎖，可能發生虛擬讀取。
可序列化SERIALIZABLE	隔離事務的最高級別，事務之間完全隔離。阻止其它事務刪除或插入任何行。相當於SELECT上加HOLDLOCK相同， SELECT 操作使用 WHERE 子句時獲取範圍鎖，主要為了避免虛擬讀取。
已提交讀快照隔離 READ COMMITTED SNAPSHOT ISOLATION level (RCSI)	當 READ_COMMITTED_SNAPSHOT 資料庫選項設置為 ON 時，已提交讀隔離使用行版本控制提供語句級讀取一致性。讀取操作只需要 SCH-S 表級別的鎖，不需要頁鎖或行鎖。使用行版本控製為每個語句提供一個在事務上一致的數據快照，因為該數據在語句開始時就存在。
快照隔離 SNAPSHOT ISOLATION level (SI)	快照隔離級別使用行版本控制來提供事務級別的讀取一致性。讀取操作不獲取頁鎖或行鎖，只獲取 SCH-S 表鎖。讀取其他事務修改的行時，讀取操作將檢索啟動事務時存在的行的版本。當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設置為 ON 時，只能對資料庫使用快照隔離。預設情況下，用戶資料庫的此選項設置為 OFF。

　　sql server主要是通過共用鎖申請和釋放機制的不同處理，來實現不同的事務隔離級別。不同隔離級別允許的併發副作用如下：

隔離級別	臟讀	不可重覆讀	幻影讀	併發控制模型
未提交讀	是	是	是	悲觀
已提交讀	否	是	是	悲觀
已提交讀快照	否	是	是	樂觀
可重覆讀	否	否	是	悲觀
快照	否	否	否	樂觀
可串列化	否	否	否	悲觀

　　不同隔離級別對共用鎖的不同處理方式如下：

隔離級別	是否申請共用鎖	何時釋放	有無範圍鎖
未提交讀	否		無
已提交讀	是	當前語句做完時	無
可重覆讀	是	事務提交時	無
可序列化	是	事務提交時	無

四.事務隔離不同表現

設置未提交讀

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

　設置提交讀

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

設置可重覆讀

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

　　4.1 未提交讀和提交讀與其它事務併發，的區別如下表格：

未提交讀

提交讀

其它事務

SELECT Model FROM Product

WHERE SID=10905

顯示model 值為test

SELECT Model FROM Product

WHERE SID=10905

顯示model 值為test

begin tran

update product set model='test1'

where SID=10905

SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED

SET TRANSACTION ISOLATION

LEVEL READ COMMITTED

這個事務將model值改為test1.

此時修改的X鎖未釋放

SELECT Model FROM Product

WHERE SID=10905

顯示model值為test1,但這並不正確，

因為其它事務還沒有提交。沒有獲取共用鎖

SELECT Model FROM Product

WHERE SID=10905

查詢被阻塞

申請獲取共用鎖時失敗，因為X鎖未釋放

阻塞消失，得到的值還是test

rollback tran

這裡事務回滾了x鎖釋放，值還是test

　　4.2 已提交讀和可重覆讀與其它事務併發，的區別如下表格：

已提交讀	可重覆讀	其它事務
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED begin tran SELECT Model FROM ProductWHERE SID=10905 第一次查詢顯示model值為 test	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin tran SELECT Model FROM Product WHERE SID=10905 第一次查詢顯示model值為 test
		begin tran update product set model='test1' where SID=10905 將model值改為 test1 另一事務是已提交讀時，這裡事務修改成功提交讀共用鎖查詢後就釋放。另一事務是可重覆讀時，這裡事務修改阻塞可重覆讀共用鎖一直保留到事務提交。
SELECT Model FROM Product WHERE SID=10905 第二次查詢值顯示為 test1	SELECT Model FROM Product WHERE SID=10905 第二次查詢顯示值顯示為 test
commit tran 這裡就是一個事務里多次讀取同一值結果可能不一致	commit tran

　　未完...sql server 鎖與事務撥雲見日(下)