[SQL SERVER][Memo]使用XACT_STATE確認失敗交易

[SQL SERVER][Memo]使用XACT_STATE確認失敗交易

交易處理作業應該每個人都會碰到,就好比提款作業,

當提款機正確吐鈔後就必須將金額從相關帳戶中減去,

這整個過程不是全部成功就是全部失敗,但難免會有交易失敗的情況發生(提款機現金不足),

這時我們可以使用XACT_STATE來確認交易失敗就還原整個交易(rolled back the transaction),

下面就來測試並記錄整個過程。

 

交易正常

 

 

		--當Transact-SQL 陳述式產生執行階段錯誤時,
		--SQL Server 是否自動回復目前的交易。
		--當 SET XACT_ABORT 是 ON 時,
		--如果 Transact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。
		--當 SET XACT_ABORT 是 OFF 時,在某些情況下,
		--只會回復產生錯誤的 Transact-SQL 陳述式,
		--交易會繼續進行。隨著錯誤嚴重性而不同,即使 SET XACT_ABORT 是 OFF
		--也有可能回復整個交易。OFF 是預設值。
		SET XACT_ABORT ON;
		BEGIN TRANSACTION ATM
		BEGIN TRY    
		DECLARE @money INT;
		SET @money =1000;   
		END TRY
		BEGIN CATCH 
		PRINT ERROR_MESSAGE();
		END CATCH   
		IF (XACT_STATE()) = 0
		BEGIN
		COMMIT TRANSACTION ATM       
		END
		ELSE 
		BEGIN       
		ROLLBACK TRANSACTION ATM  
		END     

image

執行成功。

 

當交易失敗但並未使用XACT_STATE判斷

		SET XACT_ABORT OFF;
		BEGIN TRANSACTION ATM
		BEGIN TRY    
		DECLARE @money INT;
		SET @money ='ricoisme';   
		END TRY
		BEGIN CATCH 
		PRINT ERROR_MESSAGE();
		END CATCH   
		COMMIT TRANSACTION ATM 

 

 

 

image

由於交易失敗所以不應該commit transaction,故產生無法認可目前的交易錯誤訊息。

 

利用XACT_STATE判斷

		SET XACT_ABORT OFF;
		BEGIN TRANSACTION ATM
		BEGIN TRY    
		DECLARE @money INT;
		SET @money ='ricoisme';   
		END TRY
		BEGIN CATCH 
		PRINT ERROR_MESSAGE();
		END CATCH   
		IF (XACT_STATE()) = 0
		BEGIN
		COMMIT TRANSACTION ATM       
		END
		ELSE 
		BEGIN       
		ROLLBACK TRANSACTION ATM  
		END   

 

 

image

因為使用XACT_STATE判斷交易是否失敗,在交易失敗後應該要還原而非認可該交易,而且也不會跳出相關錯誤訊息。

 

參考

XACT_STATE (Transact-SQL)

SET XACT_ABORT (Transact-SQL)