SQL Server事务性编程总结

数据库开发
目录

为了保证数据的一致性，事务在数据库编程中经常用到，这里对T-SQL中事务的写法做一下总结与解读。

T-SQL中三种事务写法

  • SQL Sever 2000时代的写法，每条语句后都加@@Error判断
/* 如果只在最后一句加上IF @@ERROR <> 0 的判断，是不理想的，因为@@ERROR只对最后执行的那条SQL有效，
这样中间出现的错误就判断不了 */
BEGIN TRAN;
	INSERT INTO dual VALUES(-1); 
	IF @@ERROR <> 0 
	GOTO ErrM;
	
	-- 除数为0，出现运行时错误
	INSERT INTO dual VALUES (1/0);
	IF @@ERROR <> 0 
	GOTO ErrM;
	
	INSERT INTO dual VALUES (1/1);
	IF @@ERROR <> 0
	GOTO ErrM;
	    
COMMIT TRAN;
ErrM:
	ROLLBACK TRAN;
  • SQL Server 2005新增写法之一，开头加上 Set xAct_Abort On
SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO dual VALUES(-1); 
	INSERT INTO dual VALUES (1/0);
	INSERT INTO dual VALUES (1/1);
COMMIT TRAN;
  • SQL Server 2005新增写法之二，Try..Catch
BEGIN TRY
	BEGIN TRAN;
		INSERT INTO dual VALUES(-1); 
		INSERT INTO dual VALUES (1/0);
		INSERT INTO dual VALUES (1/1);
	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
END CATCH

关于Try..Catch

  • Try..Catch并不能捕获所有异常
    例如以下两种异常，Try..Catch并不能捕获：
    • 编译错误，如语法错误等
    • 语句级重新编译，如存储过程中某个表名不存在等

例如，如下存储过程，引用不存在的表：

Create PROC sp_MyTest AS
BEGIN TRY
	BEGIN TRAN;
		INSERT INTO TableExist VALUES(1);
		INSERT INTO TableNotExist VALUES (2);
	COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
        ROLLBACK TRAN;
END CATCH;

以上SP在新建时没有问题，但在运行时就会报错：

Msg 208, Level 16, State 1, Procedure sp_MyTest, Line 6 [Batch Start Line 41]
Invalid object name 'TableNotExist'.
Msg 266, Level 16, State 2, Procedure sp_MyTest, Line 6 [Batch Start Line 41]
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

而且CATCH中内容并没有执行，所以==事务也没有回滚==。

  • 嵌套事务回滚的问题
    如下为嵌套事务示例代码，实际更多出现在主SP调用子SP，两个SP都有事务的情形下。
    结果出现了报错：The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
BEGIN TRY
	BEGIN TRANSACTION;
		INSERT INTO dual VALUES(1);
			-- nested try catch & transaction begin
			BEGIN TRY
				BEGIN TRANSACTION;
					INSERT INTO dual VALUES(1/0);
				COMMIT TRANSACTION;
			END TRY
			BEGIN CATCH
				ROLLBACK TRANSACTION;
			END CATCH 
			-- nested try catch & transaction end
	COMMIT TRANSACTION;
END TRY
BEGIN CATCH		
	ROLLBACK TRANSACTION;
END CATCH

问题原因在于ROLLBACK默认是回滚所有事务，即直到最外面的事务。
可以通过在CATCH模块的RollBack前加一个IF @@TRANSCOUNT > 0解决，或者开始事务前，设一个 SAVE TRANSACTION。

关于Set xAct_Abort On

这是从SQL Server 2005开始出现的一个选项，默认为OFF，遇到异常时，根据异常的种类，可能只会回滚引发异常的那条语句（比如主键冲突），而继续执行后续语句，并提交事务。若设为ON，则从异常语句开始回滚整个事务，不再继续执行后续语句（这点很重要，类似于shell脚本的set -e选择）。另外，如果错误非常严重，即便xAct_Abort选项为OFF，也可能会回滚整个事务。如果加上Set xAct_Abort on，即使try catch无法捕获异常，最终也会回滚事务。