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

標籤：設定   from   事務隔離   無法查詢   server   適合   事務所   date   database   

五、鎖與交易隔離等級

交易隔離等級簡單的說，就是當啟用事務時，控制事務內因SQL語句產生的鎖定需要保留多入，影響範圍多大，以防止多人訪問時，在事務內發生資料查詢的錯誤。設定交易隔離等級將影響整條串連。

SQL Server 資料庫引擎支援所有這些隔離等級：

· 未提交讀（隔離事務的最低層級，只能保證不讀取物理上損壞的資料）

· 已提交讀（資料庫引擎的預設層級）

· 可重複讀

· 可序列化（隔離事務的最進階別，事務之間完全隔離）

SQL Server 還支援使用資料列版本設定的兩個交易隔離等級。一個是已提交讀隔離的新實現，另一個是新交易隔離等級（快照）。

 

設定語句如下：

SET TRANSACTION ISOLATION LEVEL     { READ UNCOMMITTED     | READ COMMITTED     | REPEATABLE READ     | SNAPSHOT     | SERIALIZABLE     } [ ; ]

 

（一）未提交讀

未提交讀是最低的交易隔離等級，允許讀取其他事務已經修改但未提交的資料行。SQL SERVER 當此事務等級進行嘗試讀取資料時，不會放置共用鎖定，直接讀取資料，所以忽略已存在的互斥鎖。換句話說，即使該資源已經受到了獨佔鎖的保護，當使用未提交讀隔離等級時，此資料還是可以被讀取，加快查詢速度，但是會讀取到別人未修改的資料，所以此種讀取被稱為髒讀。此種隔離等級適合不在乎資料變更的查詢情境。此隔離等級與SELECT 語句搭配 NOLOCK 所起到的效果相同

未提交讀樣本：

--1.--1.建立測試表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增記錄

insert tbUnRead 

select 1,‘Tom‘

union

select 2,‘Jack‘

--3開啟事務，並進行更新

 

begin tran

update tbUnRead

set name=‘Jack_upd‘

where ID=2

---4查詢事務數量(由於沒有復原或提交事務)

SELECT @@TRANCOUNT

事務查詢結果如下：

 

--5開啟另一條串連，設定交易隔離等級為(未提交讀)

set Transaction isolation level read uncommitted

--6查詢資料，查詢到的資料是修改之後的資料。

select * from tbUnRead where ID=2

如：

 

 

（二）已提交讀

已提交讀是SQL SERVER 預設的交易隔離等級。當事務正在讀取資料時，SQL SERVER 會放置共用鎖定以防止其他事務修改資料，當資料讀取完成之後，會自動釋放共用鎖定，其他事務可以進行資料修改。因為共用鎖定會同時封鎖封鎖語句執行，所以在事務完成資料修改之前，是無法讀取該事務正在修改的資料行。因此此隔離等級可以防止髒讀。

 

在SQL SERVER 2005以上版本中，如果設定READ_COMMITTED_SNAPSHOT為ON，則已提交讀的事務全使用資料資料列版本設定的隔離下讀取資料。讀取操作不會擷取正被讀取的資料上的共用鎖定（S 鎖），因此不會阻塞正在修改資料的事務。同時，由於減少了所擷取的鎖的數量，因此最大程度地降低了鎖定資源的開銷。使用資料列版本設定的已提交讀隔離和快照隔離旨在提供副本資料的語句級或事務級讀取一致性。

樣本一：設定READ_COMMITTED_SNAPSHOT為OFF

--1.建立測試表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增記錄

insert tbUnRead 

select 1,‘Tom‘

union

select 2,‘Jack‘

--3開啟事務，並進行更新

 

begin tran

update tbUnRead

set name=‘Jack_upd‘

where ID=2

---4查詢事務數量(由於沒有復原或提交事務)

SELECT @@TRANCOUNT

--5開啟另一條串連，設定交易隔離等級為(已提交讀)

set Transaction isolation level read committed

--6查詢資料，由於當前事務沒有提交，所以無法查詢資料

select * from tbUnRead where ID=2

6查詢資料的結果 如：

 

 

 

樣本二：設定READ_COMMITTED_SNAPSHOT為ON

use master

go

---建立測試資料庫

create database read_committed_SNAPSHOT_Test

go

---啟用資料資料列版本設定

alter database read_committed_SNAPSHOT_Test  set read_committed_SNAPSHOT on

go

 

use read_committed_SNAPSHOT_Test

go

 

--1.建立測試表

create table tbReadLevel

(ID INT,

name nvarchar(20)

)

 

--2新增記錄

insert tbReadLevel

select 1,‘測試‘

go

select ID,name as "修改前資料"  from tbReadLevel

如：

 

go

--3開啟事務，並進行更新

 

begin tran

update tbReadLevel

set name=‘Jack_upd‘

where ID=1

---4查詢事務數量(由於沒有復原或提交事務)

SELECT @@TRANCOUNT

 

--5開啟另一條串連，設定交易隔離等級為(已提交讀)

--查詢資料，查詢到的資料是上一次提交的資料

select * from tbReadLevel where ID=1

 5的查詢結果如：

 

（三）可重複讀

可重複讀交易隔離等級在事務過程中，所有的共用鎖定均保留到事務結束，而不是讀取結束就釋放，這與已提交讀的行為截然不同，雖然在事務過程中，重複查詢相同記錄時不受其他事務的影響，但可能由於鎖定資料過久，而導致其他人無法處理資料，影響並發率，更嚴重的可能提高發生死結的機率。

  總之，如果使用可重複讀隔離等級讀取資料，資料讀出之後，其他事務只能對此範圍中的資料進行讀取或新增，但不可以進行修改，直到讀取事務完成。因此，使用此隔離等級需要謹慎小心，根據實際情況進行設定。

 

樣本：

 

--1.建立測試表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增記錄

insert tbUnRead 

select 1,‘Tom‘

union

select 2,‘Jack‘

 

--3設定交易隔離等級為(可重複讀)

set Transaction isolation level REPEATABLE READ

--4開啟事務，並進行更新

begin tran

 

--5查詢資料

select * from tbUnRead where ID=2

---6查詢事務數量(沒有復原或提交事務)

SELECT @@TRANCOUNT

5與6的執行結果如

 

---7開啟另一條串連，查詢資料與修改資料 

---事務雖然沒有完成，但可以查詢到之前的資料

select * from tbUnRead where ID=2

Go

---8，修改資料，由於事務沒有完成，所以無法進行修改

update tbUnRead

set name=‘Jack_upd‘

where ID=2

go

--7、8的執行結果如下，可以查詢資料，但無法更新資料，如。

 

 

 

（四）快照

快照隔離等級是SQL SERVER 2005之後版本新增的隔離等級，開啟之後，允許事務過程中讀取操作不受異動影響，事務中任一語句所讀取的資料，均予事務啟用時，就已經完成提交，符合事務一致性的資料行版本。所以只能查核事務啟用之前已經完成提交的資料，也就是說可以查詢已經完成提交的資料行快照集，但看不見已啟用的事務進行中修改的資料行。當使用快照隔離等級讀取資料時不會要求對資料進行鎖定，如果所讀取的記錄正在被某事務進行修改，它也會讀取此記錄之前已經提交的資料。故當某記錄被事務進行修改時，SQL SERVER的TEMPDB資料庫會儲存最近提交的資料行，以供快照隔離等級的事務讀取資料時使用。將Allow_SNAPSHOT_isolation設為ON，事務就會設定快照隔離等級。

 

use master

go

---建立測試資料庫(快照)

create database SNAPSHOT_Test

go

---啟用資料資料列版本設定

alter database SNAPSHOT_Test  set Allow_SNAPSHOT_isolation on

go

 

use SNAPSHOT_Test

go

 

--1.建立測試表

create table tbReadLevel

(ID INT,

name nvarchar(20)

)

 

--2新增記錄

insert tbReadLevel

select 1,‘測試‘

union

select 2,‘快照測試‘

go

select ID,name as "修改前資料"

from tbReadLevel

go

--3開啟事務，並進行更新

begin tran

update tbReadLevel

set name=‘Jack_upd_快照‘

where ID=1

---4查詢事務數量(沒有復原或提交事務)

SELECT @@TRANCOUNT

--2、4的執行結果，如。

 

--5開啟另一條串連，設定交易隔離等級為(快照)

set Transaction isolation level SNAPSHOT

--6查詢資料，查詢的資料是上一次提交的資料

select * from tbReadLevel where ID=1

 

 

（五）可序列化

可序列化是交易隔離等級中最高的層級，為最嚴謹的隔離等級，因為它會鎖定整個範圍的索引鍵，使事務與其他事務完全隔離。在現行事務完成之前，其他事務不能插入新的資料行，其索引索引值存在於現行事務所讀取的索引鍵範圍之中。此隔離等級與Select 搭配holdlock效果一樣。

樣本：

--1.建立測試表

create table tbUnRead

(ID INT,

name nvarchar(20)

)

--2新增記錄

insert tbUnRead 

select 1,‘Tom‘

union

select 2,‘Jack‘

--3設定交易隔離等級為(可序列化)

 

set Transaction isolation level SERIALIZABLE

--5開啟事務，並進行更新

begin tran

select * from tbUnRead where ID=2

---6查詢事務數量(沒有復原或提交事務)

SELECT @@TRANCOUNT

5、6執行結果如。

---7，開啟另一條串連，查詢資料，可以查詢到之前的資料

select * from tbUnRead where ID=2

---8，修改資料，無法修改資料

update tbUnRead

set name=‘Jack_upd‘

where ID=2

--新增資料，無法插入資料

insert tbUnRead 

select 3,‘May‘

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