T-SQL——關於事務回滾的方式

shanzm發表於2024-09-04
SQL

目錄
  • 0. 關於事務的基礎說明
  • 1. 準備測試表
  • 2. 使用@@ERROR
  • 3. 使用XACT_ABORT
  • 4. 使用TRY……CATCH（推薦）

0. 關於事務的基礎說明


0. 事務的特點ACID

  • 原子性：事務作為一個獨立的邏輯單元，被稱為原子單元。它的執行是一次性的，要麼事務中的所有事件都完成，要麼不執行事務中的任何事件。

  • 一致性：事務提交或回滾操作成功執行後，所有資料必須保持從一種狀態到另一種狀態的一致性。

  • 隔離性：併發事務的修改必須與其他併發事務所做的修改隔離。一個事務執行時不可以看到另一個事務執行的中間階段。

  • 永續性：事務提交或回滾操作成功執行後，該結果對於系統的響應是永久的，無論之後發生任何事件。


1. SQL Server 中事務分為三種：務可以`分顯式事務`、`隱式事務`和`自動提交事務`三種型別

  • 自動提交事務。是資料庫的預設事務管理模式。每一個T-SQL語句都作為一個獨立的事務發生。如果語句成功執行，則提交該語句；如果執行遇到錯誤，則回滾該語句。只要沒有宣告顯式事務或隱式事務，那麼資料庫的實際連線就此預設模式操作。

  • 隱式事務。指不需要描述事務的開始，只需要指明何時提交或回滾事務即可。若想使用隱式事務，可以使用set implicit_transaction on語句開啟隱式事務模式，在隱式事務使用完畢後，再用set implicit_transaction off語句關閉隱式事務模式即可。當隱式事務模式開啟後，首次執行以下任何語句時，都會自動啟動一個事務：create、alter、drop、select、insert、update、delete、truncate、open、fetch、grant、revoke。在發生COMMIT或ROLLBACK語句之前，該事務將一直保持有效。在第一個事務被提交或回滾之後，下次當連線執行以上任何語句時，資料庫引擎例項都將自動啟動一個新事務。該模式將不斷的生成隱性事務鏈，直到隱式事務模式關閉為止。

  • 顯式事務。指表明了事物的開始和結束的事務。事務以beigin transaction語句開始，以commit transaction或rollback transaction語句結束。

    • 隱式事務不用過多關注，重點在於顯示事務

2. 顯式事務的主要操作流程：

  • 開始事務：begin transaction [事務名稱]

    使用該語句標識一個事務的開始，全域性變數@@transaction的值遞增1，可以用來判斷事務執行的結果狀態。允許開始事務是不指定事務名稱。transaction可簡寫為tran。

  • 提交事務：commit transaction [事務名稱]

    如果事務中的語句執行時沒有遇到錯誤，可以使用該語句成功提交事務。事務中對資料庫的操作有效執行，事務佔有的資源被釋放。可以不指定事務名稱，以提交與之配對的begin transaction開始的事務。transaction可簡寫為tran。

  • 回滾事務：rollback transaction [事務名稱]

    如果事務中的語句執行時遇到錯誤，可以使用該語句清除遇到的事務，所有資料返回到事務開始時的狀態。事務佔用的資源被釋放。可以不指定事務名稱，以回滾與之配對的begin transaction開始的事務。transaction可簡寫為tran。


1. 準備測試表

CREATE TABLE [dbo].[AMyTest](
	[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
	[Name] [NVARCHAR](50) ,----------Name不允許為Null
	[Age] [INT] NULL,
	[Gender] [INT] NULL,
	[CreateTime] [DATETIME] NULL,
 CONSTRAINT [PK_AMyTest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AMyTest] ADD  CONSTRAINT [DF_AMyTest_CreateTime]  DEFAULT (GETDATE()) FOR [CreateTime]
GO

2. 使用@@ERROR

@@ERROR是全域性變數，用來統計最後一次T-SQL語句操作是否有誤，如有返回錯誤程式碼，如無返回0。

注意1：@@ERROR僅僅是統計其上一句的SQL的執行是否有誤

注意2：@@ERROR在每一條語句執行後會被立刻重置，因此應該將它的值儲存到區域性變數中以備將來使用。

BEGIN TRANSACTION;

DECLARE @ErrorNum INT=0;--宣告該變數用於統計錯誤資訊

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('張三', 30, 1);
SET @ErrorNum += @@ERROR;

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);
SET @ErrorNum += @@ERROR;

UPDATE dbo.AMyTest SET Age=100 WHERE Name='張三';
--UPDATE dbo.AMyTest SET Age='a' WHERE Name='張三';--這裡會報錯，可以用於驗證事務的回滾（這裡可以發現，回滾後，前面兩條的插入的記錄被刪除，其的自增Id，下次插入會跳過兩個自增Id）
SET @ErrorNum += @@ERROR;

IF(@ErrorNum<>0) --判斷是否報錯如果報錯就回滾資訊否則提交事務
      ROLLBACK TRANSACTION;
ELSE COMMIT TRANSACTION;

3. 使用XACT_ABORT

SET XACT_ABORT ON ：開啟，事務一旦出問題，全部回滾

SET XACT_ABORT OFF：預設值，關閉，不檢查事務是否發生錯誤。

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('張三', 30, 1);
INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);

COMMIT TRANSACTION;

4. 使用TRY……CATCH（推薦）

  • 使用Try……Catch可以獲取異常資訊，可以將異常資訊儲存或返回
  • 關於@@TRANCOUNT

在SqlServer裡，巢狀事務的層次是由@@TranCount全域性變數反映出來的。
每一次Begin Transaction都會引起@@TranCount加1。
每一次Commit Transaction都會使@@TranCount減1，
RollBack Transaction會回滾所有的巢狀事務包括已經提交的事務和未提交的事務，
而使@@TranCount置0

如果@@TRANCOUNT>0的話 那就是有begin tran有提交。
如果有異常,就進到catch裡,然後@@TRANCOUNT又是大於0的,就是會全部回滾
沒有catch中,那 就是全部提交


1. 事務包裹Try……Catch 
BEGIN TRANSACTION;

BEGIN TRY
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('張三', 30, 1);
      INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);
END TRY
BEGIN CATCH
      IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
	  --顯示異常資訊
	  SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS LINE;
END CATCH;

IF @@TRANCOUNT>0 COMMIT TRANSACTION;

2. 使用Try……Catch 包裹 事務本事（推薦這樣寫） 
BEGIN TRY
      BEGIN TRANSACTION;

      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('張三', 30, 1);
      INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);

      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
	   --顯示異常資訊
      SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS LINENum;
END CATCH;

相關文章