SQL SERVER TRANSACTION 事物

標籤：from   ddt   try   適用於   unique   復原事務   cti   自動認可   insert   

1.事務的概念

事物是一種機制，是一種操作序列，它包含了資料庫一組操作命令，這組命令要麼全部執行，要麼都不執行。因此事物是一組不可分割的事物邏輯單元，在資料庫進行並行作業時候，事物是作為最小的控制單元來使用的，這特別適用於多使用者同時操作的資料通訊系統。例如：訂票、銀行、保險公司以及證券交易系統等。

2.事物的4大屬性

• 原子性：事物是一個完整的操作；
• 隔離性：對資料進行修改的所有並發事物都是彼此隔離的；
• 一致性：當事物完成時，事物必須處於一致的狀態；
• 持久性：事物完成後，對於系統的影響是永久的；

3.建立事物

• 開始事物：transaction begin
• 提交事物：commit transaction
• 復原事物：rollback transaction

4.事物的分類

• 顯示事物：用begin transaction 明確指定事物的開始,用commit transaction, rollback transaction來結束或者復原事務
• 隱示事物（自動認可事物）：隱含交易則在執完語句後自動認可事務

5.案例

 實現轉賬操作，轉賬人出賬和收賬人入賬是一組完整的操作序列，必須全部完成或不完成，準備一張使用者錢包表（tbUserWallet），轉賬交易記錄表（tbTransaction），簡單設計如下

 

向使用者錢包表（tbUserWallet）添加測試資料

建立轉賬儲存事物

USE [TEST]GO/****** Object:  StoredProcedure [dbo].[pAddTransaction]    Script Date: 2018/5/21 12:44:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:yangyi-- Create date: 18/05/21-- Description:轉賬-- =============================================CREATE PROCEDURE [dbo].[pAddTransaction]@InOpenID uniqueidentifier,@InTDesc nvarchar(50),@OutOpenID uniqueidentifier,@OutTDesc nvarchar(50),@TAmount decimal(10, 2),@TTID int,@Result int outputASBEGINIF((SELECT Amount FROM tbUserWallet WHERE [email protected])>[email protected])BEGINBEGIN TRANSACTIONBEGIN TRYUPDATE tbUserWallet SET [email protected] WHERE [email protected]UPDATE tbUserWallet SET [email protected] WHERE [email protected]--SELECT 1+‘A‘INSERT INTO [dbo].[tbTransaction]([TID],[OpenID],[TAmount],[TTID],[TDesc],[CreateDT])VALUES(NEWID(),@InOpenID,@TAmount,@TTID,@InTDesc,GETDATE())INSERT INTO [dbo].[tbTransaction]([TID],[OpenID],[TAmount],[TTID],[TDesc],[CreateDT])VALUES(NEWID(),@OutOpenID,[email protected],@TTID,@OutTDesc,GETDATE())END TRYBEGIN  CATCHIF(@@TRANCOUNT>0)BEGINSET @Result=-1PRINT ‘事物執行出錯，復原‘ROLLBACK TRANSACTIONENDEND CATCHIF(@@TRANCOUNT>0)BEGINSET @Result=1PRINT ‘一切按預期計劃執行‘COMMIT TRANSACTIONENDENDELSEBEGINPRINT ‘轉賬人金額不足‘SET @Result=0ENDENDGO

測試1>：轉賬人金額不足測試

USE [TEST]GODECLARE@return_value int,@Result intEXEC@return_value = [dbo].[pAddTransaction]@InOpenID = ‘1ccd524d-de62-47ca-87d3-38787b040ba3‘,@InTDesc = N‘收到A的轉賬100‘,@OutOpenID = ‘2ccd524d-de62-47ca-87d3-38787b040ba3‘,@OutTDesc = N‘轉賬給A100‘,@TAmount = 100,@TTID = 1,@Result = @Result OUTPUTSELECT@Result as N‘@Result‘GO

測試2>：類比事物出現錯誤，進行復原

取消儲存事物中的：SELECT 1+‘A‘ 注釋（類比事物中發生錯誤）

USE [TEST]GODECLARE@return_value int,@Result intEXEC@return_value = [dbo].[pAddTransaction]@InOpenID = ‘2ccd524d-de62-47ca-87d3-38787b040ba3‘,@InTDesc = N‘收到A的轉賬100‘,@OutOpenID = ‘1ccd524d-de62-47ca-87d3-38787b040ba3‘,@OutTDesc = N‘轉賬給A100‘,@TAmount = 100,@TTID = 1,@Result = @Result OUTPUTSELECT@Result as N‘@Result‘GO

　　

測試3.>執行成功測試，注釋 SELECT 1+‘A‘ 

USE [TEST]GODECLARE@return_value int,@Result intEXEC@return_value = [dbo].[pAddTransaction]@InOpenID = ‘2ccd524d-de62-47ca-87d3-38787b040ba3‘,@InTDesc = N‘收到A的轉賬100‘,@OutOpenID = ‘1ccd524d-de62-47ca-87d3-38787b040ba3‘,@OutTDesc = N‘轉賬給A100‘,@TAmount = 100,@TTID = 1,@Result = @Result OUTPUTSELECT@Result as N‘@Result‘GO

 

SQL SERVER TRANSACTION 事物