并發事務下各資料庫外部表現實測之一（SQL Server篇）

當同時有多個事務通路相同資料時，DBMS會采取鎖或MVCC的機制確定資料的完整性。反映到應用程式上的表現可能就是等待或報錯。不同DBMS因為采取的機制或政策不同，其外部表現也有很大差異。于是筆者決定通過測試一探究竟。先以SQL Server作為對象進行測試。

一、 背景知識

對并發事務的資料完整性保護的強弱可通過隔離級别控制，如果過強會影響并發性能，過弱可能不滿足應用的需要。SQL 标準用三個存在并發的事務時應該避免的現像定義了四個級别的事務隔離。 

髒讀 

  一個事務讀取了另一個未送出事務寫入的資料。

不可重複讀 

  一個事務重新讀取前面讀取過的資料，發現該資料已經被另一個已送出事務修改。

幻讀 

  一個事務重新執行一個查詢，傳回一套符合查詢條件的行，發現這些行因為其它最近送出的事務而發生了改變。

SQL 事務隔離級别

<b>隔離級别</b>

<b>髒讀</b>

<b>不可重複讀</b>

<b>幻讀</b>

讀未送出

可能

讀已送出

不可能

可重複讀

可串行化

二、測試準備

1. 測試環境

OS:Windows 7

DBMS:SQL Server 2008 Express

2. 測試觀點

考慮以下因素組合的的情況下2個并發事務的互相影響。

1）事務隔離級别

  讀未送出，讀已送出，可重複讀，可串行化

2）DML語句

  select,insert,update,delete

3）通路對象

 表，同一行，不同行

3. 資料定義

使用下面具有代性的表定義，并插入兩條記錄

create table tb1(id int primary key,name varchar(30));

insert into tb1 values(1,'a');

insert into tb1 values(2,'a');

三、測試方法

同時開2個終端并開始事務塊，先在終端1上執行一個SQL語句，不送出，然後在終端2上執行第二個SQL語句，觀察第二個SQL語句是否受第一個SQL語句影響。

先執行的SQL語句根據作用範圍，分為單行和整表的查詢/更新/删除(單行處理一般會走索引，而整表處理則順序掃描)；後執行的SQL語句根據前一SQL語句作用範圍，分為同一行，不同行和整表的操作。

在不同的事務隔離級别下，分别做以上測試。

先執行的SQL語句如下:

 名稱

 SQL語句

 單行查詢

 select * from tb1 where id = 1

 整表查詢

 select * from tb1

 插入

 insert into tb1 values(5,'b')

 單行更新

 update tb1 set name = 'b' where id = 1

 整表更新

 update tb1 set name = 'b'

 單行删除

 delete from tb1 where id = 1

 整表删除

 delete from tb1

後執行的SQL語句，根據先執行的SQL語句有所不同。先執行的SQL語句是單行查詢、單行更新或單行删除時，後執行的SQL語句如下：

 同一行查詢

 非同行查詢

 select * from tb1 where id = 2

 同一行插入

 insert into tb1 values(1,'c')

 非同行插入

 insert into tb1 values(6,'c')

 同一行更新

 update tb1 set name = 'c' where id = 1

 非同行更新

 update tb1 set name = 'c' where id = 2

 update tb1 set name = 'c'

 同一行删除

 非同行删除

 delete from tb1 where id = 2

先執行的SQL語句是整表查詢、整表更新或整表删除時，非同行的查詢、更新和删除的對象為不存在的行，其他和先行SQL是單行操作時相同。

名稱

SQL語句

同一行查詢

select * from tb1 where id = 1

非同行查詢

select * from tb1 where id = 100

整表查詢

select * from tb1

同一行插入

insert into tb1 values(1,'c')

非同行插入

insert into tb1 values(6,'c')

同一行更新

update tb1 set name = 'c' where id = 1

非同行更新

update tb1 set name = 'c' where id = 100

整表更新

update tb1 set name = 'c'

同一行删除

delete from tb1 where id = 1

非同行删除

delete from tb1 where id = 100

整表删除

delete from tb1

注：黃色代表和先行SQL是單行操作時不同的地方

先執行的SQL語句是插入時，同一行代表和插入語句的id相同，非同行代表完全新的行。

select * from tb1 where id = 5

select * from tb1 where id = 2

insert into tb1 values(5,'c')

update tb1 set name = 'c' where id = 5

update tb1 set name = 'c' where id = 2

delete from tb1 where id = 5

delete from tb1 where id = 2

四、測試結果

4種隔離級别下的測試結果如下

讀未送出:

先執行SQL\後執行SQL

單行查詢

OK

主鍵沖突

插入

OK(*)

等待(*)

單行更新

單行删除

OK(*):基于更新後資料，即發生了髒讀

等待(*):如果先行的SQL送出，則基于更新後的資料，否則基于原來的資料

讀已送出:

注：黃色代表和讀未送出不同的地方

可重複讀:

等待

注：黃色代表和讀已送出不同的地方

可串行化：

 同一行插入 

注：黃色代表和可重複讀不同的地方

不難看出上述4種隔離級别是通過鎖實作的，SQL Server中還支援基于行版本控制(MVCC)的隔離級别。啟用這個功能需要打開下面2個選項

ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON

打開READ_COMMITTED_SNAPSHOT開關後，讀已送出的實作方式及外部表現就不一樣了。

讀已送出(READ_COMMITTED_SNAPSHOT=ON)：

OK(**)

OK(**):基于更新前的資料，即看到是查詢時的快照

注：黃色代表和READ_COMMITTED_SNAPSHOT=OFF時的讀已送出不同的地方

打開ALLOW_SNAPSHOT_ISOLATION開關後，可以使用SQL Server擴充的一種隔離級别SNAPSHOT，也稱作SI（SNAPSHOT ISOLATION）。

SNAPSHOT滿足SQL規範的可串行化隔離級别定義，即髒讀、不可重複讀和幻讀都不會出現，但SNAPSHOT并不是真正的可串行化，關于這一點準備以後詳細進行說明。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SNAPSHOT：

OK(***)

等待(**)

OK(***) 

OK(***):基于更新前的資料，查詢看到的是事務開始時的快照

等待(**):如果先行的SQL送出，則報更新沖突的錯誤

注：黃色代表和READ_COMMITTED_SNAPSHOT=ON時的讀已送出不同的地方

五、小結

SQL Server完整實作了SQL标準定義的4個隔離級别。并且還提供了基于MVCC的讀未送出和SNAPSHOT隔離級别，可避免讀和寫之間的鎖定提高并發性能。