SQL Server事務

標籤：

事務的特性

原子性：要麼全部執行，要麼全部不執行。

一致性：事務完成時，所有的資料都保持一致狀態。

隔離性：一個並發事務要麼修改另一個事務之前的狀態，要麼修改它之後的狀態，不能在該事務運行時去修改的它的狀態。

持久性：事務成功提交後，不能在次復原到提交前的狀態了。

 

事務以“begin tran”語句開始，以“commit tran”或“rollback tran”語句結束。

 

事務執行時注意事項

u 每個操作之後都要檢查@@ERROR和@@ROWCOUNT的值。@ERROR：當前一個語句遇到錯誤，則返回錯誤號碼，否則返回0；@@ROWCOUNT:返回上一語句受影響的行數。在每一條語句執行後至兩個變數都會被立刻重設。當沒有合格記錄時，那麼所影響的記錄數為0，但不出錯，所以@@ERROR的值也為0。

 

u 無法復原的語句不能在事務中使用，以下是不能使用在事務中的語句：

create database、alert database、drop database、load database、restore database、backup log、restore log、load transaction、dump transaction、disk init、reconfigure、update statistics

 

事務的工作原理

事務開始時，SQL Server就會將要修改的資料鎖定，同時建立一個臨時的交易記錄，在該臨時的交易記錄裡存放更改的資料和更改的過程。在事務未提交時，事務中所有的資料操作是臨時的，一旦發生資料操作失敗，就使用臨時日誌裡的資料去復原事務操作，並解除鎖定。事務被成功提交後，資料庫就將臨時交易記錄的內容儲存到資料庫的事務當中去，此時事務操作完成。

 

事務執行的模式

 

顯示事務：明確定義事務的開始和結束，顯示事務是以“begin tran”語句開始，以“commit tran”或“rollback tran”語句結束的事務。

隱含交易：隱含交易是指系統在提交或復原事務後自動啟動的事務，隱含交易產生的是一個連續的事務鏈，當執行“SET IMPLICIT_TRANSACTIONS ON”語句後，SQL Server進入隱含交易模式，執行“SET IMPLICIT_TRANSACTIONS OFF”才結束隱含交易模式。

在隱含交易模式下會自動啟動事務的語句：

alter table、create、open、insert、select、update、delete、drop、truncate table、fetch、grant、revoke

自動認可事務：是SQL Server Database Engine的預設事務方式，在沒有明確定義事務的開始和接受時，只要一出錯，也會復原到未執行前的狀態。

自動認可事務的語句：

alter table、truncate table、create、select、insert、update、delete、drop、open、fetch、revoke、grant

 

事務編寫遵循的原則

l 事務要儘可能簡短

l 事務中涉及的資料量要盡量少

l 瀏覽資料時盡量不要開啟事務

l 交易處理期間盡量不要請求使用者輸入

資料庫管理系統會在事務結束之前保留很多資源，以保證事務的原子性、一致性、隔離性和持久性。當事務需要修改資料時，系統會使用鎖來保護修改過的記錄，此時其他事務不能對這些記錄進行操作，直到事務結束。在多使用者系統中，大資源的佔有和長時間的鎖定是不能忍受的。

嵌套事務的注意事項

嵌套事務只提交最外層事務的commit語句，而嵌套的內層事務裡的commit是沒有意義的，但是也不能將其省略，因為事務是以commit或rollback語句作為結束標誌的；內層事務裡的rollback語句仍然有效，但事務的復原是復原到最外層事務開始之前的狀態（如果要內層事務要復原到內層事務開始之前，可以使用事務保持點）。

 

擷取嵌套的層次：使用全域變數@@TRANCOUNT擷取當前代碼所在層次。

 

事務保持點

在SQL Server 2008中，為事務提供了一個“事務儲存點”，用來儲存事務當前所在位置，設定好了事務保持點之後，可以讓交易回復到儲存點時的狀態。

設定事務儲存點的代碼如下：

save { tran | transaction } { savepoint_name | @savepoint_vatiable}

復原代碼如下：

rollback { tran | transaction } { savepoint_name | @savepoint_vatiable}

備忘：savepoint_name為儲存點名稱，@savepoint_variable為儲存點名稱的變數。

 

事務的隔離等級

事務具有隔離性，當交易處理資料時將資料鎖定可以防止其他事務影響當前操作，這樣一來，其他事務就要排隊等待，從而影響資料庫的使用效率。有時幾個事務同事鎖定自己的資料，同時又等待其他事務釋放資料，造成死結。

SQL Server 2008中，事務層級由低到高分為5個層級：

Read Uncommitted：

Read Committed：

Repeatable Read：

Snapshot：

Serializable：

更改事務的隔離等級的文法代碼：

set transaction isolation level

{ read uncommitted

| read Committed

| Repeatable Read

| Snapshot

| Serializable

}[ ; ]

 

事務程式碼範例：

--一、編寫事務

use DB

begin tran 

 

insert S(sname,age,sex,department) values(‘張小‘,‘21‘,1,‘電腦‘)

if @@error>0

goto tranroolback

 

--這裡的性別’男‘不符合約束條件,會出錯

insert S(sname,age,sex,department) values(‘李四‘,‘21‘,‘男‘,‘電腦‘)

if @@error>0

goto tranroolback

 

tranroolback:--自訂名稱

if @@error>0 or @@rowcount<>1

rollback tran

else

commit tran

 

select * from S

----------------------------------------------------------------------------------------------------------------------

--二、嵌套事務

use DB

begin tran 

 

insert S(sname,age,sex,department) values(‘張小‘,‘21‘,1,‘電腦‘)

if @@error>0

goto tranroolback

 

insert S(sname,age,sex,department) values(‘李四‘,‘21‘,0,‘電腦‘)

if @@error>0

goto tranroolback

 

--嵌套開始

begin tran

insert S(sname,age,sex,department) values(‘王五‘,‘21‘,0,‘電腦‘)

if @@error>0 or @@rowcount<>1

rollback tran

else

commit tran

--嵌套結束

 

tranroolback:

if @@error>0

rollback tran

else

commit tran

 

select * from S

SQL Server事務