SqlServer事務中的可重複讀和序列化隔離界別

有表t_lock:

ID是主鍵，表中有5行資料，1~2，4~6

 

可重複讀:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
begin tran
select * from t_lock where id between 1 and 6

執行這個查詢後，會在表ID為1，2，4，5，6的行上加上共用鎖定(s)

執行插入語句

insert into t_lock values(3,'3')

執行成功，原因是插入的是ID為3的資料行並加上獨佔鎖定(x)，插入語句不會去1，2，4，5，6行申請排它鎖，否則就發生阻塞了

 

 

序列化:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from t_lock where id between 1 and 6

 

根據序列化層級的定義執行查詢後會在ID為1~6的範圍上RangeS-S範圍共用鎖定,雖然ID為3的行在資料庫並不存在，但是由於這裡在ID為1~6加上的是範圍鎖，所以實際上ID為3的行也被加上了RangeS-S鎖，一定要記住範圍鎖是連續的範圍，並不因為資料庫裡資料不存在就不加鎖，比如你可以同樣執行插入ID為3的sql語句:

insert into t_lock values(3,'3')

 

結果發生了阻塞，原因就是ID為1~6的行（不管是否存在）都被加上了範圍為共用鎖定，在這個範圍除了查詢什麼也做不了,而上面的插入語句要對ID為3的資料行申請排它鎖，肯定會被阻塞.

 

 

序列化的小陷阱:

按道理來說執行了以下sql:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from t_lock where id between 1 and 6

 

然後插入ID大於6和ID小於1的資料行應該不會被阻塞，因為插入資料的ID位於1~6的範圍之外了，可是你可以試著執行以下插入語句:

insert into t_lock values(9,'9')
insert into t_lock values(-1,'-1')

無論是插入ID為-1的行還是ID為9的行都被阻塞，這是為什嗎？明明插入資料的ID在範圍之外啊？

 

讓我們更改資料庫中的資料，再來看看兩種情況就明白了

首先如果在事務中使用序列化查詢後，資料庫裡的資料是這樣:

執行insert into t_lock values(9,'9')不會阻塞
但是執行insert into t_lock values(-1,'-1')會被阻塞

 

如果在事務中使用序列化查詢後，資料庫裡的資料是這樣:

執行insert  into t_lock values(-1,'-1')不會阻塞
但是執行insert into t_lock values(9,'9')會被阻塞

 

如果在事務中使用序列化查詢後，資料庫裡的資料是這樣:

執行insert  into t_lock values(-1,'-1')或insert into t_lock values(9,'9')都不會被阻塞

 

看出點眉目來了嗎？
原來SqlServer在對行加範圍鎖的時候會先去探測資料庫中加鎖範圍外是否還有其他資料行，如果沒有，會對加鎖範圍外的其它行也加上範圍鎖，比如本文中範圍鎖會對ID為1~6的資料行加範圍鎖，它會先去查看資料庫中是否存在ID小於1的資料行，如果不存在它會將ID小於1和ID為1~6的行全加上範圍鎖，同樣它也會去看資料庫中是否有ID大於6的資料行，如果沒有它會將ID大於6和ID為1~6的行全加上範圍鎖

• 所以在最後一個圖中因為SqlServer探索資料庫中既有ID小於1的資料行(ID=0)也有ID大於6的資料行(ID=7)，所以只在ID為1~6的範圍上加範圍鎖,所以執行insert  into t_lock values(-1,'-1')或insert into t_lock values(9,'9')都不會被阻塞
• 倒數第二個圖的情況是SqlServer在資料庫中只發現了有小於1的資料行(ID=0)，所以將ID大於1的行都加上了範圍鎖，執行insert  into t_lock values(9,'9')被阻塞
• 倒數第三個圖的情況是SqlServer在資料庫中只發現了有大於6的資料行(ID=7)，所以將ID小於6的行都加上了範圍鎖，執行insert  into t_lock values(-1,'-1')被阻塞