Aaron Yang

相信網路上很多介紹隔離層級的文章,

除了本文所寫的鎖機制隔離層級外,還有快照隔離.

並且也是資料庫古早的觀念,但卻非常重要.

此文僅作為一篇讓自己加深隔離層級印象的輔助,

並且自己遺忘時能快速熟悉.(網上也有很多類似的文章)

介紹隔離層級之前,先了解資料庫的ACID

ACID是指為了確保transaction過程中資料正確可靠所需要的四個特性(From 維基百科).

A : 原子性 => transaction 全部完成 或 全部不完成的特性

C : 一致性 => transaction前後的資料符合DDL的定義

I  :  隔離性 => 本文重點,允許多個transaction同時對資料存取修改,又不因多個transaction交叉執行造成數據不一致.

D : 持久性 => transaction結束後對資料的修改是永久的(基本上就是寫回硬體了)

事務隔離層級的類型

事務隔離層級是對資料庫不同程度的鎖定,是基於鎖機制的事務隔離方式,有以下類型與特性.

read uncommitted => read committed => repeatable read => serializable

高 <===============Transaction 併發效能==============> 低

高 <=================併發造成不一致================> 低

低 <=================發生Lock的機率================> 高

低 <================鎖耗用的資源開銷================> 高

-----------------------------------------------------------------------------------

基本上,資料庫依目前連線的隔離層級,設置Transaction併發時,所要使用的鎖(Lock)

鎖(Lock)的部分有點複雜,這篇文章先針對隔離層級作記錄.

一般常見的就是下面的表格,其中描述了幾類transaction併發時,在沒有適當的鎖下,可能造成的資料不一致現象.

更新遺失(Lost Update)

在沒有鎖的情況下,事務A的更新會消失.

髒讀(Dirty Read)

事務B取了錯誤的資料還Commit了

不可重覆讀取(Unrepeatable Read)

事務A兩次讀取的資料不相同.

幻讀(Phantom Read)

第二次統計筆數會多/少了,事務B新增/刪除的部分

不同隔離層級的保證

Read Uncommitted

    事務更新但未提交的資料,其他事務僅可讀取.

Read Committed

    事務讀取的資料,必須為確認的資料

Repeatable Read

    同一事務內多次讀取的資料必須相同

Serializable

    事務必須循序,在資料表上放獨占鎖

而這些事務隔離層級的差異,

實際上是藉由鎖的持續時間和類型互相配合而成的.

鎖機制(Locks)

不論哪種隔離層級,實際在讀取/修改/新增/刪除資料時,都是使用鎖來實作,

所以要先了解一下主要的鎖(Lock)有哪些,

才能進一步的了解各隔離層級如何使用這些鎖.

並且往後在解Dead Lock時也能更明瞭原因.

共享鎖(Shared Locks S)

    用於Select,允許其他事務讀取但不可修改,如果是read committed層級下,僅維持到語句結束

更新鎖(Update Locks U)

    用於update,分為前後兩個階段 ( S -> X )

    更新前,共享性質,其他事務可讀取不可修改

    開始更新,獨占性質,其他事務不可讀取或修改,如果是read committed層級下,會維持到事務結束

    能用於解決dead lock的問題

獨占鎖(Exclusive Locks X)

    用於修改資料,並且拒絕其他事務的讀取修改.

意圖鎖(Intent Locks I)

    像是提前告知事務意欲為何,同時防止其他交易擴大鎖定.

    如IX代表意圖獨占鎖

綱要鎖(Schema Locks Sch-M,Sch-S)

    Sch-M用於資料庫結構變更 - 不允許存取物件資料

    Sch-S用於查詢編譯時 - 可存取其他資料

    但兩者皆不會與事務爭搶資源

各種鎖的對應關係可以參考下面經典的相容矩陣,(截自MSDN

隔離層級 VS 鎖的持續時間

S代表持續至當前語句執行完; C代表持續至事務提交,如下表(截自維基百科

由上可知,儘管相同的T-SQL在事務內所使用的鎖(Lock)基本一致,

但不同的持續時間,就能防止不同的事務併發問題.

EX:

1. repeatable的Read行為鎖定持續至事務提交,代表過程中,其他事務不可修改該值,

從而確保了不會發生不可重覆讀取.

2. Serializable的Range操作,持續到事務提交,

代表事務內的條件下的統計查詢結果(Range),不會異動.避免了幻讀.

參考

事務隔離

SQL Server - 鎖定類型介紹

SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因

Lock Compatibility