技術文章 » SQL Server事務及隔離級別

SQL Server事務及隔離級別

2023-10-27 15:00:44

事務的並行操作可能出現的問題

中文 英文 描述
髒讀 Dirty Reads 事務2讀到了事務1未提交的事務，事務1隨後回滾，但事務2讀到了事務1的「中間資料」。 在Read Uncommitted隔離級別下會發生，其它級別不會。
(update&read)
丟失更新 Lost Updates 兩個事務對同一個行分別進行更新，其中一個更新覆蓋了另一個，導致丟失了一個更新。 在Read Committed的隔離級別下仍能發生，Repeatable Read能夠避免它發生。
為什麼官方檔案裡沒有提到Lost Updates這個現象？
官方檔案：Transaction Isolation Levels
(read&update)
不可重複讀 Non-repeatable Reads 事務2在事務1的兩次讀取之間更新了資料導致事務1兩次讀到不一樣的資料。 在Repeatable Read隔離級別下解決，和Lost Update一樣，本質都是因為在此隔離級別下S鎖持有到事務結束使其它事務無法在本事務執行過程中更新資料。
(read&update)
幻讀 Phantom Reads 當一個事務執行一個query兩次, 但得到不同行數的結果集。 幻讀和不可重複讀不一樣地方在於，解決不可重複讀問題時針對的是已有資料，因此可以持有它們的S鎖，使其它事務請求X鎖時等待；而幻讀是新插入的資料。
(insert\delete)

事務

  • 原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行。

  • 一致性（Consistency）：事務應確保資料庫的狀態從一個一致狀態轉變為另一個一致狀態。

我一直納悶這個「一致」是什麼意思，然後在#參照.1.這本書裡看到了解釋：資料庫在某一時刻的狀態反應的是真實世界在這個時刻的資料，真實世界被抽象成資料，然後真實世界不停執行，資料庫中的資料就像真實世界的一個切面，只不過真實世界是連續的，而資料庫的資料是真實世界某一個時刻的狀態，是離散的。隨著真實世界的執行，當資料庫中的資料從一個時刻到下一個時刻且資料都是「正確的」的時候，就稱資料庫從一個一致性狀態到了另一個一致性狀態，保證這種「一致性」的手段就是「資料庫約束」，它對資料的正確性進行校驗。不過，這種校驗是片面的，資料的正確性也取決於業務邏輯（後臺程式碼），但在資料庫層面能做的就是「資料庫約束」。

  • 隔離性（Isolation）：多個事務並行執行時，一個事務的執行不應影響其他事務的執行。

  • 永續性（Durability）：即便發生斷電等意外情況，已被提交的事務對資料庫的修改應該永久儲存在資料庫中（硬碟上）。這點一般通過紀錄檔來保證。

事務的隔離級別

隔離級別影響鎖模式的表現。

Read Uncommitted

  • 不要求SELECT時請求S鎖，因此它不會阻塞X鎖或者被X鎖阻塞，因此可以發生髒讀。

  • The intention of this isolation level is for systems primarily focused on reporting and business intelligence, not online transaction processing.

  • 別用。

Read Committed

  • 要求SELECT時請求S鎖，因此它會阻塞X鎖或者被X鎖阻塞，因此可以避免髒讀。

  • 它是SQL Server預設的隔離級別。

  • 由於S鎖不會持有到事務結束，而是在SELECT完成後就釋放了， 因此可能發生幻讀和不可重複讀。

    • 幻讀：INSERT, DELETE

    • 不可重複讀：UPDATE

Repeatable Read

  • 要求SELECT時請求S鎖，並持續到事務結束，因此避免了事務過程中其它事務對資料的修改，因此可以避免幻讀和不可重複讀。

  • 可重複讀：在當前的事務中可再次讀取（R），讀取的結果沒有被修改（CUD）。

Serializable

  • 最高階別的隔離級別

  • 相比直接在所需的行上加鎖，可序列化隔離級別在所需的行和下一行（索引順序）上獲得一個range lock。因此它可以避免插入新的資料，從而避免幻讀現象。

  • 上圖是SQLite中的頁的結構，但DBMS設計思路是類似的。在一個頁中，儲存著多個Key，這些Key按順序排列在一個陣列中，所謂的「下一個」就是這個索引的下一個。

  • 如果key存在

    • 如果next key存在：
      • 在non-unique index的情況下，在當前請求的key和next key上範圍鎖。
      • 在unique index的情況下，理論上在key上獲取S鎖就足夠，但SQL Server仍會獲取範圍鎖
    • 如果next key不存在：taken on the ‘infinity’ value.

  • 如果key不存在

    • 如果next key存在：taken on the next key.
    • 如果next key不存在： taken on the ‘infinity’ value.

  • 在無限大上的範圍鎖會鎖定 >= 當前key的範圍

  • BETWEEN 在請求的key上和next key上獲取範圍鎖

  • WHERE 在請求的key及前後獲取範圍鎖

create table foo (c1 int)

go

insert into foo values (1)

insert into foo values (2)

insert into foo values (3)

insert into foo values (4)

insert into foo values (5)

create unique clustered index foo_ci on foo(c1)

set tran isolation level serializable

begin tran

select * from foo where c1 between 2 and 4 

SELECT dtl.request_session_id,
 dtl.resource_database_id,
 dtl.resource_associated_entity_id,
 dtl.resource_type,
 dtl.resource_description,
 dtl.request_mode,
 dtl.request_status
FROM sys.dm_tran_locks AS dtl
WHERE dtl.request_session_id = @@SPID;

參照

  1. 《SQL Server 2017 Query Performance Tuning 5th Edition》
  2. dotnettutorials: sql-server-concurrent-transactions
  3. stackoverflow: what are range locks
  4. techcommunity.microsoft: SQL Server Range Lock
  5. Microsoft: Transaction Isolation Levels