sql server事務全攻略

一 事務的屬性 
事務具有ACID屬性 
即 Atomic原子性, Consistent一致性, Isolated隔離性, Durable永久性 

原子性 

就是事務應作為一個工作單元,交易處理完成，所有的工作要麼都在資料庫中儲存下來，要麼完全 
復原，全部不保留 

一致性 
事務完成或者撤銷後，都應該處於一致的狀態 

隔離性 

多個事務同時進行，它們之間應該互不干擾.應該防止一個交易處理其他事務也要修改的資料時， 
不合理的存取和不完整的讀取資料 

永久性 
事務提交以後，所做的工作就被永久的儲存下來 

二 事務並發處理會產生的問題 

丟失更新 

當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題、 
每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。 

髒讀 
當第二個事務選擇其它事務正在更新的行時，會發生未確認的相關性問題。 
第二個事務正在讀取的資料還沒有確認並且可能由更新此行的事務所更改。 

不可重複讀取 

當第二個事務多次訪問同一行而且每次讀取不同的資料時，會發生不一致的分析問題。 
不一致的分析與未確認的相關性類似，因為其它事務也是正在更改第二個事務正在讀取的資料。 
然而，在不一致的分析中，第二個事務讀取的資料是由已進行了更改的事務提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次資訊都由其它事務更改；因而該行被非重複讀取。 

幻像讀 

當對某行執行插入或刪除操作，而該行屬於某個事務正在讀取的行的範圍時，會發生幻像讀問題。 
事務第一次讀的行範圍顯示出其中一行已不複存在於第二次讀或後續讀中，因為該行已被其它事務刪除。同樣，由於其它事務的插入操作，事務的第二次或後續讀顯示有一行已不存在於原始讀中。 

三 交易處理類型 

自動處理事務 

系統預設每個T－SQL命令都是交易處理 由系統自動開始並提交 

隱含交易 

當有大量的DDL 和DML命令執行時會自動開始，並一直保持到使用者明確提交為止，切換隱含交易可以用SET IMPLICIT_TRANSACTIONS 
為串連設定隱性事務模式.當設定為 ON 時，SET IMPLICIT_TRANSACTIONS 將串連設定為隱性事務模式。當設定為 OFF 時，則使串連返回到自動認可事務模式 

使用者定義事務 

由使用者來控制事務的開始和結束 命令有: begin tran commit tran rollback tran 命令 

分散式交易 
跨越多個伺服器的事務稱為分散式交易,sql server 可以由DTc microsoft distributed transaction coordinator 
來支援處理分散式交易,可以使用 BEgin distributed transaction 命令啟動一個分散式交易處理 

 

四 交易處理的隔離等級 

使用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 c 
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 TR