About SQLServer Transaction

1. 一个存储过程最好只有一个事务；多个事务既消耗资源，也容易产生错误；

2. 创建事务和结束事务的时候最好有这样的判断：

IF @@TRANCOUNT = 0
BEGIN -- only if @@TRANCOUNT is 0, we do BEGIN TRAN
    BEGIN TRANSACTION
    SET @TransactionIsOurs = 1
END        
IF @TransactionIsOurs = 1
BEGIN
    COMMIT TRANSACTION
    -- SET @TransactionIsOurs = 0
END
IF @TransactionIsOurs = 1 AND @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

 

3.  如果事务很长，可以在适当的位置多加几个错误判断：

IF @@Error <> 0
BEGIN
   RAISERROR (50002, 16, 1, N'ApplyCheckedObjectsStage', N'spApplyCheckedObjectsStage', 10, N'Checked Agence Stage', @StrErrorParm) WITH LOG
   SET @ReturnCode = -1000
   GOTO exit_label
END

 

     Note：@@Error必须直接跟在需要判断的sql后面，中间不能间隔其他语句，因为@@Error总是取最新执行的结果，所以最好还是使用Try...Catch...

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

 

4.  据说在嵌套事务中，通过给内部事务指定名称的方式可以实现局部rollback；也有人证明说是胡扯；需要自己验证一下。

IF @@TRANCOUNT = 0
BEGIN -- only if @@TRANCOUNT is 0, we do BEGIN TRAN
    BEGIN TRANSACTION
    SET @TransactionIsOurs = 1
END    
IF @TransactionIsOurs = 1 --确认有且只有一个事务
BEGIN
    COMMIT TRANSACTION
    -- SET @TransactionIsOurs = 0
END
IF @TransactionIsOurs = 1 AND @@TRANCOUNT > 0
        ROLLBACK TRANSACTION