SQL Server 事务和错误处理
4.80/5 (91投票s)
2003年7月2日
6分钟阅读
961065
2
SQL Server 事务和错误处理
引言
本文中的示例使用了 SQL Server 安装时作为示例数据库提供的 Pubs 数据库。如果需要重新构建 Pubs 数据库，请按照步骤安装一个新的副本。
- 运行 osql 命令行实用程序，并使用 sp_detach_db 系统存储过程将 Pubs 数据库从 SQL Server 中分离。
osql -U sa -P "" -Q "exec sp_detach_db 'Pubs'"
- 删除 Pubs 数据库的文件（pubs.mdf、pubs_log.ldf）。这些文件位于 \Data 目录下。
- 重新创建 Pubs 数据库需要执行 Instpubs.sql 脚本。从命令行运行脚本（如果 .sql 文件位于不同的目录，请相应调整路径）。您也可以从查询分析器运行此脚本文件。
osql -U sa -P "" -i "C:\Program Files\Microsoft SQL Server\MSSQL\Install\InstPubs.sql"（osql 实用程序使用区分大小写的选项。如果未使用 -U 或 -P 选项，SQL Server 2000 会尝试使用 Windows 身份验证模式进行连接。有关 osql 实用程序的更多信息，请参阅 SQL Server Books Online。）
事务
事务将一组任务分组为一个执行单元。每个事务以一个特定的任务开始，并在组中的所有任务成功完成时结束。如果任何任务失败，事务就会失败。因此，事务只有两种结果：成功或失败。不完整的步骤会导致事务失败。
用户可以使用以下语句将两个或多个 Transact-SQL 语句分组到一个事务中。
- 开始事务
- 回滚事务
- 提交事务
如果分组语句中的任何语句出现问题，所有更改都需要被中止。在 SQL Server 术语中，撤销更改的过程称为 **回滚**。如果单个事务中的所有语句都正常，则所有更改将一起记录到数据库中。在 SQL Server 术语中，我们说这些更改已 **提交** 到数据库。
以下是一个事务示例。
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 语句会通知 SQL Server 将以下所有操作视为一个事务。然后是两个 UPDATE 语句。如果在更新过程中没有发生错误，当 SQL Server 处理 COMMIT TRAN 语句时，所有更改都将提交到数据库，并且存储过程最终完成。如果在更新过程中发生错误，则会通过 if 语句检测到该错误，并将执行转移到 PROBLEM 标签。向用户显示消息后，SQL Server 将回滚在处理过程中发生的任何更改。注意：请确保将 BEGIN TRAN 与 COMMIT 或 ROLLBACK 配对。
嵌套事务
SQL Server 允许您嵌套事务。基本上，此功能意味着可以在前一个事务未完成的情况下开始一个新的事务。Transact-SQL 允许您通过发出嵌套的 BEGIN TRAN 命令来嵌套事务操作。可以通过查询 @@TRANCOUNT 自动变量来确定嵌套级别 - 0 表示没有嵌套，1 表示嵌套一级，依此类推。
除最外层事务外，对任何事务发出的 COMMIT 都不会将任何更改提交到磁盘 - 它只会减少 @@TRANCOUNT 自动变量的值。另一方面，ROLLBACK 的工作方式与其发出的级别无关，但会回滚所有事务，而无论嵌套级别如何。尽管这可能违反直觉，但有一个非常充分的理由。如果嵌套的 COMMIT 实际上将更改永久写入磁盘，则外部的 ROLLBACK 将无法撤销这些更改，因为它们已经永久记录。
当您显式开始一个事务时，@@TRANCOUNT 自动变量的计数从 0 增加到 1；当您 COMMIT 时，计数减少 1；当您 ROLLBACK 时，计数减少到 0。如您所见，COMMIT 和 ROLLBACK 的行为并非对称。如果您嵌套事务，COMMIT 总是将嵌套级别减少 1，如图 1 所示。另一方面，ROLLBACK 命令会回滚整个事务，如图 2 所示。COMMIT 和 ROLLBACK 之间的这种不对称性是处理嵌套事务中错误的关键。
 |
| 图 1： COMMIT 总是通过将事务计数减一来平衡 BEGIN TRANSACTION。 |
 |
| 图 2： 单个 ROLLBACK 总是回滚整个事务。 |
从图 1 和图 2 可以看出，您可以嵌套事务并使用 @@TRANCOUNT 自动变量来检测级别。您还了解到 COMMIT 和 ROLLBACK 的行为并不对称；COMMIT 仅减少 @@TRANCOUNT 的值，而 ROLLBACK 则将其重置为 0。这意味着，直到发出最后一个 COMMIT，事务才算完全提交。无论您将一组事务嵌套得多深，只有最后一个 COMMIT 才有效。
以下是一个嵌套事务示例。
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
DELETE sales
BEGIN TRAN nested
SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 2
DELETE titleauthor
COMMIT TRAN nested
-- Does nothing except decrement the value of @@TRANCOUNT
SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 1
ROLLBACK TRAN
SELECT 'After ROLLBACK TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
-- because ROLLBACK TRAN always rolls back all transactions and sets
-- @@TRANCOUNT to 0.
SELECT TOP 5 au_id FROM titleauthor
在此示例中，尽管有嵌套的 COMMIT TRAN，但外部的 ROLLBACK 仍然撤销了 DELETE titleauthor 命令的效果。
以下是另一个类似的嵌套事务示例。
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
DELETE sales
BEGIN TRAN nested
SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 2
DELETE titleauthor
ROLLBACK TRAN
SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 0 because
-- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
-- to 0.
IF (@@TRANCOUNT > 0) BEGIN
COMMIT TRAN -- Never makes it here cause of the ROLLBACK
SELECT 'After COMMIT TRAN', @@TRANCOUNT
END
SELECT TOP 5 au_id FROM titleauthor
在此示例中，执行从未到达外部 COMMIT TRAN，因为 ROLLBACK TRAN 撤销了所有正在进行的事务并将 @@TRANCOUNT 设置为 0。除非 ROLLBACK TRAN 是带有保存点的，否则 ROLLBACK TRAN 总是会回滚所有事务并将 @@TRANCOUNT 设置为 0，而无论其在何种上下文中调用。
SAVE TRAN 和保存点
保存点提供了一种回滚事务部分的方法。用户可以在事务中设置一个保存点，或标记。保存点定义了一个位置，当事务的某一部分被有条件地取消时，事务可以返回到该位置。SQL Server 允许您通过 SAVE TRAN 语句使用保存点，该语句不会影响 @@TRANCOUNT 的值。回滚到保存点（而不是事务）也不会影响 @@TRANCOUNT 返回的值。但是，回滚必须显式命名保存点：使用不带特定名称的 ROLLBACK TRAN 将始终回滚整个事务。
以下脚本演示了如何使用保存点。
USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
-- The value of @@TRANCOUNT is 0
BEGIN TRAN main
SELECT 'After BEGIN TRAN main', @@TRANCOUNT
-- The value of @@TRANCOUNT is 1
DELETE sales
SAVE TRAN sales -- Mark a save point
SELECT 'After SAVE TRAN sales', @@TRANCOUNT
-- The value of @@TRANCOUNT is still 1
BEGIN TRAN nested
SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
-- The value of @@TRANCOUNT is 2
DELETE titleauthor
SAVE TRAN titleauthor -- Mark a save point
SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
-- The value of @@TRANCOUNT is still 2
ROLLBACK TRAN sales
SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
-- The value of @@TRANCOUNT is still 2
SELECT TOP 5 au_id FROM titleauthor
IF (@@TRANCOUNT > 0) BEGIN
ROLLBACK TRAN
SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
-- The value of @@TRANCOUNT is 0 because
-- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
-- to 0.
END
SELECT TOP 5 au_id FROM titleauthor
错误处理
此处提供的示例特定于存储过程，因为它们是与数据库交互的首选方法。在存储过程中遇到错误时，您所能做的最好的事情就是停止代码的顺序处理，然后分支到过程中的另一个代码段，或将处理返回给调用应用程序。@@ERROR 自动变量用于实现错误处理代码。它包含在客户端连接期间最后执行的 SQL 语句生成的错误 ID。当语句成功执行时，@@ERROR 包含 0。要确定语句是否成功执行，请使用 IF 语句在目标语句执行后立即检查 @@ERROR 的值。务必在目标语句执行后立即检查 @@ERROR，因为当下一条语句成功执行时，其值会被重置为 0。如果发生可捕获的错误，@@ERROR 的值将大于 0。SQL Server 在每次成功执行命令后都会重置 @@ERROR 值，因此您必须立即捕获 @@ERROR 的值。大多数情况下，您会在任何 INSERT、UPDATE 或 DELETE 语句执行后立即测试 @@ERROR 的变化。
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
这种解决方案包含大量重复，特别是如果您的业务逻辑需要实现两个以上的 Transact-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) GOTO ERR_HANDLER
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1