事务
事务组合一系列任务为一个执行单元。每个事务以特定的任务开始,以特定的任务结束。当所有的任务成功时事务成功,当任何一个任务失败时,事务失败。所以一个事务只有两个结果:失败或成功。
用户能用下列指令组合两个以上的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