SqlServer 交易回復(1)

標籤：

SQL事務
 
一、事務概念
    事務是一種機制、是一種操作序列，它包含了一組資料庫操作命令，這組命令要麼全部執行，要麼全部不執行。因此事務是一個不可分割的工作邏輯單元。在資料庫系統上執行並行作業時事務是作為最小的控制單元來使用的。這特別適用於多使用者同時操作的資料通訊系統。例如：訂票、銀行、保險公司以及證券交易系統等。
 
二、事務屬性
事務4大屬性：
1   原子性(Atomicity):事務是一個完整的操作。
2   一致性（Consistency）：當事務完成時，資料必須處於一致狀態。
3   隔離性(Isolation):對資料進行修改的所有並發事務是彼此隔離的。
4   持久性(Durability):事務完成後，它對於系統的影響是永久性的。
 
三、建立事務
T-SQL中管理事務的語句：
1 開始事務: begin transaction
2 提交事務：commit transaction
3 復原事務: rollback transaction
 
事務分類:
1 明確交易:用begin transaction明確指定事務的開始。
2 隱性事務：開啟隱性事務：set implicit_transactions on，當以隱性事務模式操作時，SQL Servler將在提交或復原事務後自動啟動新事務。無法描述事務的開始，只需要提交或復原事務。
3 自動認可事務：SQL Server的預設模式，它將每條單獨的T-SQL語句視為一個事務。如果成功執行，則自動認可，否則復原。USE [TestDB] GO
/****** 對象:  Table [dbo].[Person]    指令碼日期: 11/23/2008 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
    [PersonId] [nchar](18) NOT NULL,
    [PersonName] [nchar](20) NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

預設情況下如果執行一個事務中出現錯誤，則只復原錯誤動作陳述式（就是說這句不執行了，算不上復原），錯誤處之前或之後的正確動作陳述式還是會被提交。如：

Use TestDB

Begin  TransAction
    Insert Into Person(PersonId,PersonName)
                Values(‘1‘,‘Name1‘)
    Insert Into Person(PersonId,PersonName)
                Values(‘1‘,‘Name1‘)
    Insert Into Person(PersonId,PersonName)
                Values(‘3‘,‘Name3‘)
Commit  TransAction
/*
    Select 一下  有‘1‘,‘Name1‘和‘3‘,‘Name3‘，
    說明只有第二句的錯誤被取消了
*/

全部復原的方法1：開啟 XACT_ABORT

Use TestDB
SET XACT_ABORT ON -- 開啟
Begin  TransAction
    Insert Into Person(PersonId,PersonName)
                Values(‘1‘,‘Name1‘)
    Insert Into Person(PersonId,PersonName)
                Values(‘1‘,‘Name1‘)
    Insert Into Person(PersonId,PersonName)
                Values(‘3‘,‘Name3‘)
Commit  TransAction
/*
    當 SET XACT_ABORT 為 ON 時，
    如果執行 Transact- SQL 語句產生執行階段錯誤，
    則整個事務將終止並復原。 
    預設情況下它是OFF狀態。
*/

全部復原方法2：使用Try...Catch

Use TestDB
Begin Try
    Begin  TransAction
        Insert Into Person(PersonId,PersonName)
                    Values(‘1‘,‘Name1‘)
        Insert Into Person(PersonId,PersonName)
                    Values(‘1‘,‘Name1‘)
        Insert Into Person(PersonId,PersonName)
                    Values(‘3‘,‘Name3‘)
    Commit  TransAction
End Try
Begin Catch
    Rollback  TransAction
End Catch
/*
    使用TryCatch來捕獲異常。
    如果 TRY 塊內產生的錯誤導致當前事務的狀態失效，
    則將該事務歸類為不可提交的事務。
    如果通常在 TRY 塊外中止事務的錯誤在 TRY 內發生時，
    就會導致事務進入不可提交狀態。
    不可提交的事務只能執行讀操作或 ROLLBACK  TRANSACTION。
    該事務不能執行任何可能產生寫操作或 COMMIT  TRANSACTION 的 Transact- SQL 語句。
    如果事務被分類為不可提交的事務，則 XACT_STATE 函數會傳回值 -1。
*/

全部復原方法3：自訂錯誤變數

Use TestDB
Declare @tranError int -- 定義變數
Set @tranError=0
    Begin  TransAction
        Insert Into Person(PersonId,PersonName)
                    Values(‘1‘,‘Name1‘)
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values(‘1‘,‘Name1‘)
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values(‘3‘,‘Name3‘)
            Set @tranError = @tranError + @@Error
    If @tranError = 0
        Commit  TransAction
    Else
        Rollback  TransAction
/*
    自訂一個變數來判斷最後是否發生過錯誤。
*/

最後要注意的是：如果一個事務寫了 Begin TransAction 而沒寫 Commit TransAction 或 Rollback TransAction 則相關操作的資料（也許是表，也許是列，這我還沒測試。。。）會被鎖住。。。而對於鎖住的解決辦法就是單獨執行一下Commit TransAction 或 Rollback TransAction

 

本文轉載

SqlServer 交易回復(1)