筆記_SQL Server交易(Transaction)特性與交易隔離層級(Isolation Level)

上次討論到SQL Server使用的鎖定類型(lock types)有Shared(S)、Exclusive(X)與Update(U)，這次我們要討論這些鎖定類型與交易(Transaction)的關係，我們先討論一下甚麼是交易？交易可以看成一個工作單位，這個單位會完成多個行為，例如ATM的轉帳是一個交易單位，這個交易有兩個行為，從A帳號轉出500元給B帳號與B帳號收到500元，除此之外交易還必須符合ACID的特性(ACID為縮寫)。

1.  交易的特性摘要如下：

(1)     Atomicity：交易內的行為只有兩種結果，全成功或全失敗，SQL SERVER要達成Atomicity時需將XACT_ABORT設定為ON。

(2)     Consistency：SQL SERVER提供constraints 完成資料的完整性，例如：PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, and NOT NUL。

(3)     Isolation：針對正在進行交易的資料，其他交易是不可讀取與異動的，簡單來說就是該筆資料會與其他交易隔離。例如：A交易發生時要將王小明年齡改從19歲改為20歲時，此時B交易是看不到也摸不到王小明這筆資料。

(4)     Durability：交易完成後，資料無論如何都會留在資料庫內，SQL Server透過Write-ahead log的方式來實現Durability，也就是先將資料寫入LOG檔後再寫入資料檔，所以當發生意外時，可以靠著LOG的內容依序還原資料。

2.  常見的交易迷思：

Q1:當我們執行Insert/Update/Delete指令時沒有使用begin tran或commit等關鍵字時是不會有交易的。

ANS1：SQL SERVER預設是implicit transaction，所以是有交易的。implicit transaction在某些情境下是相當耗資源的，最常見的例子就是使用insert into table values匯入10萬筆資料，implicit transaction耗費資源的時間與log檔上都會多很多。

Q2:Select指令是沒有交易存在的？

ANS2：Ｓelect是有交易的喔，這部份會在後面的筆記說明。

Q3:如果使用NOLOCK等HINT是沒有交易的

ANS3：交易還是有的，只是我們將isolation層級降為read uncommitted。

3.  Isolation Level

每個交易都有自己的Isolation Level，SQL SERVER有4種悲觀（Pessimistic）的isolation level，如下表：

Isolation層級	Share Locks 行為	Table Hint
Read uncommitted,	不需要Share locks	NoLock
read committed,	Share locks在Commit完後釋放	READ committed
repeatable read	Share locks在交易結束後釋放	repeatableread
serializable	Share Locks的範圍變大(RANGE locks)，且直到交易結束才釋放。	HOLDLOCK

除了Read uncommitted之外，每個Isolation Level在資料寫入時會鎖住(BLOCK)讀取行為。此外不論Isolation Level為何，資料異動時的互斥鎖(exclusive lock)都會在持續到交易結束，所以上述四種Isolation Level的差異主要是在於Share Locks的行為。

Read uncommitted：沒有shared locks，所以當其他還沒有COMMIT交易在修改資料時，其他交易是可以讀取該筆資料的，所以當A交易異動資料且尚未commit時，B交易是可以讀取A交易異動過的資料。

Read committed：shared locks會在讀取完資料後就釋放，當A交易讀取資料時，B交易是無法修改被A交易資料的正在讀取的資料，但是一旦讀取完成shared locks 就會釋放(此時A交易尚未完成)，B交易就可以修改A交易之前讀取的資料。

Repeatable read：shared locks交易結束後才會釋放，所以只要A交易讀取資料時，除非到整個交易結束，否則B交易無法法修改被讀取的資料。

Serializable ：shared locks會持續到交易結束，而且shared locks範圍會變大。所以只要A交易讀取資料時，除非整個交易結束，否則B交易無法修改讀取的資料與被讀取資料的特定範圍是無法進行新增的行為。

我們可以使用 set transaction isolation level等指令去設定交易的Isolation Level，例如：範例語法，如果要在TABLE設定交易的isolation level的話，可以在資料表後面使用TABLE HINT，例如範例語法2

範例語法1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION Transaction2

SELECT *

 FROM TestTable

 WHERE Value='C'

 GO

COMMIT

範例語法2

SELECT *  FROM TestTable WITH (HOLDLOCK)

WHERE Value='C'

4.結論：

交易的Isolation Level其實是很容易理解的，我們只要專注在不同Level上shared locks的差異即可，此外我們目前只討論悲觀(Pessimistic)的 Isolation Level，至於SQL SERVER的2種樂觀(optimisitic)的Isolation Level：Snapshot and read committed snapshot又是另一個故事了。

參考：

Locking in Microsoft SQL Server (Part 2 – Locks and transaction isolation levels)
[SQL Server] 了解ACID
Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels