主页 / dba / 问题 / 123178
Accepted
krystah
Asked: 2015-12-09 03:03:48 +0800 CST

显式事务在尝试/捕获后保持打开状态

  • 772

我最近编写了一个 T-SQL 脚本来对 3 个不同的表执行一些更新和插入操作。我希望它在单个事务中完成，因此我阅读了有关如何使用 try/catch 的显式事务的 Microsoft 文档。

根据文档，可以这样做：

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY

BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

所以我实现了这个模式并将我所有的更新和插入都放在了 TRY 子句中。问题是，代码在查询完成后设法留下了一个打开的事务，我不知道这是怎么发生的。不管在 TRY 子句中做了什么工作，有哪些可能的场景会导致这样的查询使事务保持打开状态？

sql-server t-sql

3 个回答

  1. Best Answer

    有几种类型的错误没有被 T-SQL TRY/CATCH结构捕获（取自TRY...CATCH的 MSDN 页面，为清楚起见稍作编辑）：

    • 严重性为 20 或更高的错误会停止会话的 SQL Server 数据库引擎任务处理。如果发生严重程度为 20 或更高的错误并且数据库连接未中断，则 TRY...CATCH 将处理该错误。
    • 注意，例如客户端中断请求或断开的客户端连接。
    • 当系统管理员使用 KILL 语句结束会话时。
    • 在语句级重新编译期间与 TRY…CATCH 构造在同一执行级别发生的错误，例如由于延迟名称解析而在编译后发生的对象名称解析错误。

    对于问题中提供的示例代码（即单个 DML 语句），有一个非常简单的解决方法：删除显式事务。对于单个 DML 语句，显式事务是不必要的，除非有额外的逻辑对某些条件进行检查，并且可以选择通过ROLLBACKmain 中的 doneTRY而不是作为一部分来撤消该更改，CATCH因为它不是数据库引擎错误。在该特定场景之外，我看不出有理由（至少是一个好理由）为单个 DML 语句使用显式事务。但我仍然会保留TRY/CATCH结构以进行一般错误处理。

    当涉及到多个 DML 语句时（这是问题的真实上下文，如示例代码上方的措辞中所述），那么您显然确实需要显式事务，因此这里有一些想法：

    • @usr 对问题的评论建议完全跳过数据库层的错误处理，而是在应用层处理它。虽然应用程序代码可以处理事务和错误处理，但如果您没有专门使用像实体框架这样的 ORM （这意味着您有由 SQL 代理作业和/或支持人员通过 SSMS 调用的存储过程），那么这些存储过程需要包括事务和错误处理，因为应用程序层并不总是执行的发起者，但您仍然希望在其他情况下进行事务和错误处理。我在以下答案中对此进行了更多讨论：我们是否需要在 C# 代码以及存储过程中处理事务
    • 连接结束时，未提交的事务会自动回滚。这是从 SQL Server 的角度，而不是客户端的角度。意思是，连接不再列在sys.dm_exec_connections.

    • 连接池使自动回滚处理变得复杂，因为它在设计上保持连接打开，即使在客户端“关闭”它之后也是如此。当连接保持打开以便另一个“连接”尝试可以简单地重新使用它时，会话本身不会“清理”，直到打开新连接并执行查询批处理！在应用程序代码在实际重用池中的连接的“新”连接上提交的第一次执行时sp_reset_connection，将调用一个标记为的内部进程，除其他外，该进程将回滚该会话中的未提交事务，即现在被重复使用。这里的问题是当使用连接池时，进程终止（可能是命令超时），并且没有新的连接被请求，也没有新的查询被提交。在这种情况下，连接只是位于池中，会话仍然存在，并且没有请求清理操作。但这不会永远持续下去（尽管在这些情况下仍然比你想要的长）。根据SQL Server Connection Pooling (ADO.NET)的 MSDN 页面（在删除连接部分）：

      连接池在空闲大约 4-8 分钟后，或者如果池检测到与服务器的连接已被切断，则从池中删除连接。

    • 取消存储过程执行不仅仅是您的应用程序代码获取 CommandTimeout 或调用显式“取消”的问题。SQL Server Management Studio (SSMS) 或任何 IDE 也是客户端代码。并且可以取消 SSMS 中正在运行的存储过程。这样做会产生与跳过CATCH块相同的效果。但是在 SSMS 中取消执行仍然会让您保持在同一个会话中，因此事务仍然处于活动状态，直到您手动调用COMMITor ROLLBACK，或者直到您关闭该查询选项卡（此时它会告诉您有一个未提交的事务并询问是否你想提交还是不提交；我的测试表明回答“不”会回滚）。

    那么如何处理使用连接池的应用程序代码呢？我不是使用SET XACT_ABORT ON;. 我首先要尝试的是：

    • Max Pool SizeConnections 可以通过连接字符串关键字设置连接池的最大大小。如果池非常大，那么任何特定连接都不太可能被快速重用。默认池大小似乎是 100。将值设置为略低于当前值（但不要太低以免无法有效利用连接池）将有助于确保更快地重用连接将意味着清理进程将被更快地调用，这将回滚任何打开的事务。

    • 在您正在执行查询/存储过程的应用程序代码中，catch您可以在块中调用SqlConnection.ClearPool，它应该关闭 SQL Server 级别的实际连接，这反过来应该允许自动回滚未提交的事务。您甚至可以通过检查异常以查看它是否是命令超时或其他几个会在跳过 T-SQLCATCH块时终止进程的场景之一，如果是，则调用ClearPool.

    • 从技术上讲，您可以Pooling=false;通过在连接字符串中指定来完全禁用连接池。但是，我不认为这是必要的，并且不推荐它，除非绝对必要或拥有一个不会产生大量连接的应用程序。

    • 2

  2. 不管在 TRY 子句中做了什么工作，有哪些可能的场景会导致这样的查询使事务保持打开状态？

    导致事务保持打开的常见情况是命令超时。命令超时发生在客户端，而不是服务器上。当查询运行时间超过指定时间CommandTimeout时，客户端 API 会向服务器发送一条注意命令以取消批处理。这可以防止批处理中的任何后续代码（包括CATCH块）被执行。SET XACT_ABORT ON除非指定，否则事务将保持打开状态。

    SET XACT_ABORT ON设置会导致事务在出现 T-SQL 错误后回滚，因此我建议您对所有显式事务都养成这种习惯。以下是我推荐的一般模式示例。THROW;在 SQL Server 2012 及更高版本中使用CATCH块而不是RAISERROR丑陋。

    SET XACT_ABORT ON;

BEGIN TRY

    -- Generate a constraint violation error.
    BEGIN TRANSACTION;

    DELETE FROM Production.Product
    WHERE ProductID = 980;

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE
         @ErrorNumber int
        ,@ErrorMessage nvarchar(2048)
        ,@ErrorSeverity int
        ,@ErrorState int
        ,@ErrorLine int;

    SELECT
         @ErrorNumber =ERROR_NUMBER()
        ,@ErrorMessage =ERROR_MESSAGE()
        ,@ErrorSeverity = ERROR_SEVERITY()
        ,@ErrorState =ERROR_STATE()
        ,@ErrorLine =ERROR_LINE();

    RAISERROR('Error %d caught at line %d: %s'
        ,@ErrorSeverity
        ,@ErrorState
        ,@ErrorNumber
        ,@ErrorLine
        ,@ErrorMessage);

END CATCH;

    请注意，同一范围内的编译错误不会受到影响SET XACT_ABORT，也无法被捕获。

    • 1

  3. 可能的场景：

    • 解析（语法和语义）

    • 编译（无效的对象引用，如果语句被缓存和标记但不是第一次，错误将命中 catch 块）

    • 命令超时

    • 0

相关问题