T-SQL筆記41_Isolation Levels4種悲觀鎖定隔離等級介紹

SQL Server 資料庫引擎支援悲觀(Pessimistic)的isolation level：

READ UNCOMMITTED(未提交讀) : 完全沒有隔離效果，可能讀取其他交易進行中尚未被COMMIT的資料。（隔離事務的最低層級，只能保證不讀取物理上損壞的資料）

READ COMMITTED (已提交讀): 不允許讀取尚未COMMIT的資料，因為尚未被COMMITTED的資料可能隨時會再變。

REPEATABLE READ(可重複讀) : 在查詢中所讀取的資料會被鎖定，以免被其他使用者更改或刪除，以保證在交易中每次都可以讀到相同的資料。但是，仍然允許其他使用者對資料表的新增資料作業。

SERIALIZABLE(可序列化) : 在查詢中所讀取的資料會被鎖定，以免被其他使用者更改或刪除，以保證在交易中每次都可以讀到相同的資料。但是，仍然允許其他使用者對資料表的新增資料作業。

隔離等級	中途讀取 (Dirty read)	非可重複讀取	幽靈 (Phantom)
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	是
SNAPSHOT	否	否	否
SERIALIZABLE	否	否	否

其中READ COMMITTED為SQL Server Database Engine的預設交易隔離層級。

可以用 set transaction isolation level等指令去設定交易的Isolation Level。

自SQL Server 2005以後又引進了

「讀取認可快照(READ_COMMITTED_SNAPSHOT)」

「快照集隔離(SNAPSHOT isolation)」

設定語句如下：

SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

}

所謂的悲觀鎖定(Pressimistic Lock)即資料庫引擎認為更新資料期間，其他的使用者也會同時來存取相同資料，所以為了確保資料的正確性，在資料的操作一關始時，即立即鎖定資料，直到操作結束才釋放鎖定。

相對概念樂觀鎖定(Optimistic Lock)即資料庫引擎認為更新資料期間，不會有其他的使用者會來存取相同資料，因此不對資料進行鎖定，讓存取的速度可以加快。

Ref:

[SQL] 使用交易隔離等級鎖定資料表動作

如何使用快照集隔離來降低鎖定問題的發生

Locking/Blocking

DIY Example

SQL SERVER的鎖機制（三）——概述（鎖與交易隔離等級）

Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels

SQL Server Isolation Levels with examples

第25個冬天