SQL Server的事务和错误处理

事务

事务组合一系列任务为一个执行单元。每个事务以特定的任务开始，以特定的任务结束。当所有的任务成功时事务成功，当任何一个任务失败时，事务失败。所以一个事务只有两个结果：失败或成功。
用户能用下列指令组合两个以上的T-SQL语句到一个事务中：
Begin Transaction
Rollback Transaction
Commit Transaction
一组指令中出现任何错误，就需要使用Rollback撤销执行。如果一个事务中的所有语句都按顺序执行成功，那么就需要把所有的改变记录到数据库中：Commit到数据库。
USE pubs  
 
DECLARE @intErrorCode INT 
 
BEGIN TRAN  
    UPDATE Authors  
    SET Phone = '415 354-9866' 
    WHERE au_id = '724-80-9391' 
 
    SELECT @intErrorCode = @@ERROR  
    IF (@intErrorCode <> 0) GOTO PROBLEM  
 
    UPDATE Publishers  
    SET city = 'Calcutta', country = 'India' 
    WHERE pub_id = '9999' 
 
    SELECT @intErrorCode = @@ERROR  
    IF (@intErrorCode <> 0) GOTO PROBLEM  
COMMIT TRAN  
 
PROBLEM:  
IF (@intErrorCode <> 0) BEGIN 
PRINT 'Unexpected error occurred!' 
    ROLLBACK TRAN  
END 

在真正处理开始之前，使用BEGIN TRAN标记：下面所有的动作作为一个事务。里边包含两个update语句。如果没有出错，当执行到COMMIT TRAN所有的改变被提交到数据库，存储过程完成。如果在执行update出错，通过检测@@ERROR，跳转到PROBLEM标签，显示错误信息，回滚这期间所有的修改。
注意：确保BEGIN TRAN 和COMMIT 或 ROLLBACK成对出现。
嵌套事务

SQL Server 容许你嵌套事务，这个特性意味着，即使上一个事务没有完成，也能开启一个新事务。T-SQL容许你通过嵌套BEGIN TRAN嵌套事务。 内置变量@@TRANCOUNT可显示事务的嵌套层级。0表示没有嵌套，1表示嵌套一层，依此类推。

COMMIT提交本层事务，但是直到最外层的事务提交了，所有的改变才保存到磁盘。它仅仅时减小@@TRANCOUNT的值。ROLLBACK不论在那层都能回滚所有的事务。
当你开始一个事务，@@TRANCOUNT变量自动从0增加到1；当提交了数量减少1个。当回滚了，则数量减为0。正如你所见：COMMIT和ROLLBACK并不对称。如果你嵌套事务，COMMIT一层较少1，如图1。ROLLBACK命令回滚所有事务如图2。COMMIT 和 ROLLBACK不同在于嵌套错误处理。

 

 图1:一个commit对应一个BeginTransaction，把@@Trancount数量减1。

 

图2：Rollback一直回滚整个事务

从图1和图2可以看出：你可以嵌套事务，使用@@TRANCOUNT检测嵌套的级别。也能了解COMMIT 和ROLLBACK不对称的特性。COMMIT减少@@TRANCOUNT的值， ROLLBACK让值变为0。只有最后一个COMMIT才真正提交事务。不管你嵌套多少事务，只有最后一个COMMIT起作用。
USE pubs  
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  --  @@TRANCOUNT ：0  
 
BEGIN TRAN  
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：1  
 
    DELETE sales  
    BEGIN TRAN nested  
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT  
                   -- @@TRANCOUNT ：2  
 
        DELETE titleauthor  
    COMMIT TRAN nested  
                   -- 出了减少@@TRANCOUNT的值，不做任何事情
 
 
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT  
                   -- @@TRANCOUNT ：1  
 
ROLLBACK TRAN  
 
SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：0   
 
-- 因为ROLLBACK TRAN 回滚整个事务。 @@TRANCOUNT 被设置为 0.  
 
 
SELECT TOP 5 au_id FROM titleauthor  
USE pubs  
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：0  
 
BEGIN TRAN  
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- @@TRANCOUNT ：1  
 
    DELETE sales  
    BEGIN TRAN nested  
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT  
               -- @@TRANCOUNT ：2  
 
        DELETE titleauthor  
    ROLLBACK TRAN  
    
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT  
    -- @@TRANCOUNT ：0 因为ROLLBACK TRAN 回滚整个事务。 @@TRANCOUNT 被设置为 0.  
 
 
IF (@@TRANCOUNT > 0) BEGIN 
    COMMIT TRAN -- 永远不会执行到这里，因为事务被回滚了。  
 
    SELECT 'After COMMIT TRAN', @@TRANCOUNT  
END 
 
SELECT TOP 5 au_id FROM titleauthor  

在这个例子中，永远不会执行到COMMIT TRAN，因为ROLLBACK TRAN回滚了所有的事务。除非ROLLBACK TRAN带有一个保存点调用，否则ROLLBACK TRAN一直回滚所有事务，把@@TRANCOUNT设置为0。
SAVE TRAN 和保存点

保存点提供了事务部分回滚的机制。用户能在事务内部设置保存点或标记。保存点定义一个当事务被撤销时，回滚的位置。SQL Server容许用

户通过SAVE TRAN语句定义保存点。申明保存点不影响@@TRANCOUNT的值，回滚到保存点也不影响@@TRANCOUNT。回滚必须ROLLBACK TRAN带有保存点的名称，否则回滚整个事务。
USE pubs  
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT  
   -- @@TRANCOUNT :0  
 
 
BEGIN TRAN main  
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT  
   -- @@TRANCOUNT :1  
 
    DELETE sales  
    SAVE TRAN sales  -- 设置一个保存点  
 
    SELECT 'After SAVE TRAN sales', @@TRANCOUNT  
   -- @@TRANCOUNT 值仍然是 1  
 
 
    BEGIN TRAN nested  
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT  
        -- @@TRANCOUNT :2  
 
        DELETE titleauthor  
        SAVE TRAN titleauthor  -- Mark a save point  
 
        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT  
        -- @@TRANCOUNT 仍然是 2  
 
    ROLLBACK TRAN sales  
 
    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT  
     -- @@TRANCOUNT 仍然是 2  
 
 
    SELECT TOP 5 au_id FROM titleauthor  
 
IF (@@TRANCOUNT > 0) BEGIN 
    ROLLBACK TRAN  
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT  
    -- @@TRANCOUNT ：0 因为ROLLBACK TRAN 回滚整个事务。 @@TRANCOUNT 被设置为 0.  
 
END 
      
SELECT TOP 5 au_id FROM titleauthor  
错误处理

下面的例子是一个和数据库交互的很理想化的存储过程。当一个错误出现时，你所能做的是：终止代码执行顺序。要么把代码路由到别的代码

，或者返回终止程序。@@ERROR这个系统变量常用来实现错误处理。它包含SQL语句最后执行错误的代码。当代码执行成功， @@ERROR的值是0
当代码执行完成，立即使用IF语句判断@@ERROR的值。这是因为当下一个语句执行成功时，@@ERROR的值会被重置为0。所以必须立即捕获。经常在执行完NSERT, UPDATE, 或 DELETE语句后，你需要立即测试@@ERROR值的变化。

这种解决方案有很多重复的处理，特别是如果你的业务逻辑需要2个以上的T-SQL语句时。比较优雅的解决方案是组合所有的代码，使用一个通用的错误处理。
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),   
                          @title VARCHAR(20), @title_type CHAR(12))  
AS 
 
BEGIN TRAN  
    INSERT titles(title_id, title, type)  
    VALUES (@title_id, @title, @title_type)  
 
    IF (@@ERROR <> 0) BEGIN 
        PRINT 'Unexpected error occurred!' 
        ROLLBACK TRAN  
        RETURN 1  
    END 
 
    INSERT titleauthor(au_id, title_id)  
    VALUES (@au_id, @title_id)  
 
    IF (@@ERROR <> 0) BEGIN 
        PRINT 'Unexpected error occurred!' 
        ROLLBACK TRAN  
        RETURN 1  
    END 
 
COMMIT TRAN  
 
RETURN 0