事务
事务组合一系列任务为一个执行单元。每个事务以特定的任务开始，以特定的任务结束。当所有的任务成功时事务成功，当任何一个任务失败时，事务失败。所以一个事务只有两个结果：失败或成功。
用户能用下列指令组合两个以上的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
关于多个存储过程和事物的处理
假如我有叁个存储过程，每个存储过程中都显示申明了事物，当我依次调用这叁个存储过程，如果其中有一个存储过程中发生了错误，那么其它两个存储过程中的事物都要回滚，这样的如何做呢？
下面我把代码贴出来让大家看看。
-
SQL code
-
-- --------------第一个存储过程----------------- CREATE PROCEDURE TransProc_1 @PriKey INT , @CharCol CHAR ( 3 ), @error_out int output AS BEGIN TRANSACTION InProc_1 INSERT INTO TestTrans VALUES ( @PriKey , @CharCol ) INSERT INTO TestTrans VALUES ( @PriKey + 1 , @CharCol ) set @error_out = @@error COMMIT TRANSACTION InProc_1; GO -- ------------第二个存储过程------------------- CREATE PROCEDURE TransProc_2 @PriKey INT , @CharCol CHAR ( 3 ), @error_out int output AS BEGIN TRANSACTION InProc INSERT INTO TestTrans VALUES ( @PriKey , @CharCol ) INSERT INTO TestTrans VALUES ( @PriKey + 1 , @CharCol ) set @error_out = @@error COMMIT TRANSACTION InProc; GO -- ------------第叁个存储过程------------------- CREATE PROCEDURE TransProc_3 @PriKey INT , @CharCol CHAR ( 3 ), @error_out int output AS BEGIN TRANSACTION InProc INSERT INTO TestTrans VALUES ( @PriKey , @CharCol ) INSERT INTO TestTrans VALUES ( @PriKey + 1 , @CharCol ) set @error_out = @@error COMMIT TRANSACTION InProc; GO -- ------------------------------- BEGIN TRANSACTION OutOfProc; DECLARE @ERROR INT ; -- 依次调用这些存储过程 EXEC TransProc_1 10 , ' aaa ' , @error_out = @ERROR output EXEC TransProc_2 20 , ' ccc ' , @error_out = @ERROR output EXEC TransProc_3 30 , ' ccc ' , @error_out = @ERROR output -- 打印错误号 print ( @ERROR ) IF ( @ERROR <= 0 ) BEGIN PRINT ( ' 提交事物 ' ) COMMIT TRANSACTION OutOfProc; END ELSE BEGIN PRINT ( ' 事物回滚 ' ) ROLLBACK TRANSACTION OutOfProc; END GO上面的代码运行肯定是不行的，不能达到我想要的效果，请问有什么方法可以解决我现在的问题呢？
-
============================================================================
-
问题答案： -
BEGIN TRANSACTION OutOfProc; -
DECLARE @ERROR INT,@ERROR_2 INT; set @ERROR_2=0 set @ERROR=0 -
--依次调用这些存储过程 EXEC TransProc_1 12, 'aaa',@error_out=@ERROR output set @ERROR_2=@ERROR_2+@ERROR EXEC TransProc_2 22, 'bbb',@error_out=@ERROR output set @ERROR_2=@ERROR_2+@ERROR EXEC TransProc_3 30, 'ccc',@error_out=@ERROR output set @ERROR_2=@ERROR_2+@ERROR --打印错误号 print(@ERROR_2) -
IF ( @ERROR_2 <= 0 ) BEGIN PRINT('提交事物') COMMIT TRANSACTION OutOfProc; END ELSE BEGIN PRINT('事物回滚') ROLLBACK TRANSACTION OutOfProc; END -
GO 至于错误处理, 那是你在每个可能出错的语句后需要添加代码的(sql 2005 可以直接用try ... catch)
437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
