SQL Server事務全攻略(下)

四 交易處理的隔離等級

　　使用SET TRANSACTION ISOLATION LEVEL來控制由串連發出的所有語句的預設事務鎖定行為

　　從低到高依次是 READ UNCOMMITTED

　　執行髒讀或 0
級隔離鎖定，這表示不發出共用鎖定，也不接受排它鎖。當設定該選項時，可以對資料執行未提交讀或髒讀；在事務結束前可以更改資料內的數值，行也可以出現在數
據集中或從資料集消失。該選項的作用與在事務內所有語句中的所有表上設定 NOLOCK 相同。這是四個隔離等級中限制最小的層級。

　　舉例，設table1(A，B，C)

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　建立兩個串連

　　在第一個串連中執行以下語句 

select * from table1
begin tran
update table1 set c='c'
select * from table1
waitfor delay '00:00:10' --等待10秒
rollback tran
select * from table1

 　　在第二個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
print '髒讀'
select * from table1
if @@rowcount>0
begin
waitfor delay '00:00:10'
print '不重複讀'
select * from table1
end 

　　第二個串連的結果

　　髒讀 

A	B	C
a1	b1	c1
a2	b2	c
a3	b3	c

　　'不重複讀'

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　READ COMMITTED

　　指定在讀取資料時控制共用鎖定以避免髒讀，但資料可在事務結束前更改，從而產生不可重複讀取或幻像資料。該選項是 SQL Server
的預設值。

　　在第一個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '不重複讀'
select * from table1
rollback tran 

　　在第二個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
update table1 set c='c' 

　　第一個串連的結果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　不重複讀

A	B	C
a1	b1	c
a2	b2	c
a3	b3	c

　　REPEATABLE READ

　　鎖定查詢中使用的所有資料以防止其他使用者更新資料，但是其他使用者可以將新的幻像行插入資料集，且幻像行包括在當前事務的後續讀取中。因為並發低
於預設隔離等級，所以應只在必要時才使用該選項。

　　在第一個串連中執行以下語句 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '幻像讀'
select * from table1
rollback tran

 　　在第二個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
insert table1 select 'a4'，'b4'，'c4' 

　　第一個串連的結果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

 

　　幻像讀

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3
a4	b4	c4

    　SERIALIZABLE

　　在資料集上放置一個範圍鎖，以防止其他使用者在事務完成之前更新資料集或將行插入資料集內。這是四個隔離等級中限制最大的層級。因為並發層級較
低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。

　　在第一個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
print '初始'
select * from table1
waitfor delay '00:00:10' --等待10秒
print '沒有變化'
select * from table1
rollback tran 

　　在第二個串連中執行以下語句

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
insert table1 select 'a4'，'b4'，'c4' 

　　第一個串連的結果

　　初始

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

 　　沒有變化

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

　　五 交易處理嵌套的文法和對@@TRANCOUNT的影響

BEGIN TRAN @@TRANCOUNT＋1
COMMIT TRAN @@TRANCOUNT－1
ROLLBACK TRAN