当前位置：首页> 正文

为什么当xact_abort打开时，Sql Server在raiserror之后仍继续执行？

Why does Sql Server keep executing after raiserror when xact_abort is on?

我只是对TSQL中的某些东西感到惊讶。我以为如果打开xact_abort，调用类似

1	raiserror('Something bad happened', 16, 1);

将停止执行存储过程(或任何批处理)。

但是我的ADO.NET错误消息恰恰相反。在异常消息中我同时收到了raiserror错误消息，以及在此之后发生的下一件事。

这是我的解决方法(无论如何，这是我的习惯)，但似乎没有必要：

1
2
3
4
5

IF @somethingBadHappened
BEGIN;
raiserror('Something bad happened', 16, 1);
RETURN;
END;

文档说：

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

这是否意味着我必须使用显式事务？

这是By DesignTM，您可以在Connect上看到SQL Server团队对类似问题的回答：

Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server.

是的，对于某些希望具有较高严重性的RAISERROR(例如16)与SQL执行错误相同的人来说，这是一个问题-并非如此。

解决方法只是您需要做的事情，使用显式事务对您要更改的行为没有任何影响。

如果使用try / catch块，严重性为11-19的raiserror错误号将导致执行跳转到catch块。

任何高于16的严重性都是系统错误。为了演示以下代码，我们设置了一个try / catch块并执行了一个我们认为会失败的存储过程：

假设我们有一个表[dbo]。[错误]用来保存错误
假设我们有一个存储过程[dbo]。[AssumeThisFails]当我们执行它时将失败

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

-- first lets build a temporary table to hold errors
IF (object_id('tempdb..#RAISERRORS') IS NULL)
CREATE TABLE #RAISERRORS (ErrorNumber INT, ErrorMessage VARCHAR(400), ErrorSeverity INT, ErrorState INT, ErrorLine INT, ErrorProcedure VARCHAR(128));

-- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to
DECLARE @tc AS INT;
SET @tc = @@trancount;
IF (@tc = 0)
BEGIN TRANSACTION;
ELSE
save TRANSACTION myTransaction;

-- the code in the try block will be executed
BEGIN try
DECLARE @return_value = '0';
SET @return_value = '0';
DECLARE
@ErrorNumber AS INT,
@ErrorMessage AS VARCHAR(400),
@ErrorSeverity AS INT,
@ErrorState AS INT,
@ErrorLine AS INT,
@ErrorProcedure AS VARCHAR(128);

-- assume that this procedure fails...
EXEC @return_value = [dbo].[AssumeThisFails]
IF (@return_value <> 0)
raiserror('This is my error message', 17, 1);

-- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block
IF (@tc = 0)
commit TRANSACTION;
RETURN(0);
END try

-- the code in the catch block will be executed on raiserror("message", 17, 1)
BEGIN catch
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ERROR_PROCEDURE();

INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);

-- if i started the transaction
IF (@tc = 0)
BEGIN
IF (XACT_STATE() <> 0)
BEGIN
SELECT * FROM #RAISERRORS;
ROLLBACK TRANSACTION;
INSERT INTO [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
SELECT * FROM #RAISERRORS;
INSERT [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
RETURN(1);
END
END
-- if i didn't start the transaction
IF (XACT_STATE() = 1)
BEGIN
ROLLBACK TRANSACTION myTransaction;
IF (object_id('tempdb..#RAISERRORS') IS NOT NULL)
INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
ELSE
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN(2);
END
ELSE IF (XACT_STATE() = -1)
BEGIN
ROLLBACK TRANSACTION;
IF (object_id('tempdb..#RAISERRORS') IS NOT NULL)
INSERT #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
VALUES (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure);
ELSE
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN(3);
END
END catch
END

在RAISERROR()之后立即使用RETURN，它将不再执行该过程。

如SET XACT_ABORT在文档中所指出的，应使用THROW语句而不是RAISERROR。

两者的行为略有不同。但是，当XACT_ABORT设置为ON时，则应始终使用THROW命令。

展开全文阅读