SQL Transcation事务回滚等用法

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        ROLLBACK TRANSACTION

    END CATCH
   
END
GO

现在我们给存储过程添加了异常处理机制TRY/CATCH（注意：SQLSERVER 2005或之后的版本才支持TRY/CATCH），接下来让我们再执行一次存储过程。

看上去我们已经把问题的解决了，我们知道存储过程可以内嵌存储过程或函数，所以我们把上面的存储过程SPAddDataToUserInfo内嵌到存储过程SPMultiDataToUserInfo中，SPMultiDataToUserInfo的定义如下：

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
CREATE PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        ROLLBACK TRANSACTION

    END CATCH
    
END
GO

     我们知道存储过程SPAddDataToUserInfo会发生异常，它会回滚事务（ROLLBACK），但SPMultiDataToUserInfo是发生回滚还是继续插入数据呢？

图6 执行存储过程消息

 

图7 UserInfo表中数据

 

      在插入的过程发生了异常，检查UserInfo表数据并没有插入表中，这符合我们设计的意图，但我们发现异常不仅仅是产生于插入的数据超出了数值的范围，还包含事务计数异常。

      由于定义了两个存储过程，而且SPAddDataToUserInfo内嵌在SPMultiDataToUserInfo中，在执行这两个存储过程中，它们都发生了异常并且进行事务回滚（因为User为空）。

      由于插入的数据超出了数值的范围的异常是我们特意引起的，而事务计数异常这是预期之外的异常。

      接下来让我们看一下究竟是什么原因引起了该异常，这里我们通过输出Transactions计数来查看问题所在。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION
        PRINT ERROR_MESSAGE();
        PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION
        PRINT ERROR_MESSAGE();
        PRINT 'Rolled back successful Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

    上面我们通过输出事务的计数（@@TRANCOUNT）来查看在事务回滚时，事务计数器的变化。

图8 存储过程执行消息

 

      通过上图我们发现在执行SPMultiDataToUserInfo和SPAddDataToUserInfo时，事务计数器分别加1，当遇到SPAddDataToUserInfo中的异常时，事务回滚事务计算器置零。

      当执行SPMultiDataToUserInfo中的事务时，由于事务计算器（@@TRANCOUNT）已经置零，导致抛出异常，现在我们明白了导致事务计数异常的原因，所以我们在进行事务回滚之前必须判断事务计算器（@@TRANCOUNT）是否为0，如果为0就不回滚事务。

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Inserts data
-- =============================================
Alter PROCEDURE SPAddDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPAddDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('JKhuang', 8);
                INSERT INTO UserInfo VALUES('Jackson', 20111111);
                INSERT INTO UserInfo VALUES('JKRush', 23);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPAddDataToUserInfo]: ' + ERROR_MESSAGE();
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
        PRINT 'Rolled back successful in SPAddDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

-- =============================================
-- Author:        JKhuang
-- Create date: 12/8/2011
-- Description:    Invokes store procedure to insert data.
-- =============================================
ALTER PROCEDURE SPMultiDataToUserInfo 

AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
                PRINT 'In [SPMultiDataToUserInfo] Transactions: ' + Convert(varchar, @@TRANCOUNT);
                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                --SET NOCOUNT ON;

                -- Hard code inserted data.
                INSERT INTO UserInfo VALUES('Cris', 1);
                EXEC SPAddDataToUserInfo
                INSERT INTO UserInfo VALUES('Ada', 32);

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        PRINT 'Error in [SPMultiDataToUserInfo]: ' + ERROR_MESSAGE();
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
        PRINT 'Rolled back successful in SPMultiDataToUserInfo Transactions: ' + Convert(varchar, @@TRANCOUNT);
    END CATCH
    
END
GO

    现在我们增加了事务计数器的判断，当计数器为0时，不进行事务回滚，这样就没有了之前事务计数器异常了。

图9 存储过程执行消息

 

      现在对于事务我们有了进一步的了解，而且把以上的事务定义成为一个日常通用的模板，如下给出了一个基本的Transaction模板。

-- =============================================
-- Transaction Temp
-- =============================================
BEGIN TRY
    BEGIN TRANSACTION
 
    --
    -- You code here.
    --
 
    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0)
        -- Adds store procedure
        -- Writes the error into ErrorLog table.
        ROLLBACK TRANSACTION
    END IF
END CATCH

 

1.1.3 总结

       事务是作为单个逻辑工作单元执行的一系列操作。可以是一条SQL语句也可以是多条SQL语句。

       事务具有四个特性

       原子性：不可分隔、成则具成、败则具败。

       一致性：事务在完成时，必须使所有的数据都保持一致状态

       隔离性：独立的执行互不干扰。由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。

       持久性：务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

       应用程序主要通过指定事务启动和结束的时间来控制事务。

       启动事务：使用 API 函数和 Transact-SQL 语句，可以按显式、自动提交或隐式的方式来启动事务。

       结束事务：您可以使用 COMMIT（成功） 或 ROLLBACK（失败） 语句，或者通过 API 函数来结束事务。

       事务模式分为：显示事务模式、隐式事务模式、自动事务模式。在SQL常用的是显示模式。

       创建事务的原则：

       尽可能使事务保持简短很重要，当事务启动后，数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源、以保证事务的正确安全执行。

       特别是在大量并发的系统中， 保持事务简短以减少并发 资源锁定争夺，将先得更为重要。

1、事务处理，禁止与用户交互，在事务开始前完成用户输入。

2、在浏览数据时，尽量不要打开事务

3、尽可能使事务保持简短。

4、考虑为只读查询使用快照隔离，以减少阻塞。

5、灵活地使用更低的事务隔离级别。

6、灵活地使用更低的游标并发选项，例如开放式并发选项。

7、在事务中尽量使访问的数据量最小。