Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
开始想写一个过程，根据一个项目的数据库的一个表的导数据到另一个数据库中去，本来在本地测试时蛮好的，但是将过程升级到生产服务器之后运行之后，根据下面类似的错误信息发现是生产服务器上引用的数据库名不对。
Msg 208, Level 16, State 1, Procedure test_sp, Line 10
Invalid object name 'test.dbo.col1'.
Msg 266, Level 16, State 2, Procedure test_sp, Line 10
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
出现了这个错之后就去查了下需要插入的表，发现锁表了，所以猜测是不是刚刚运行的过程的事务没有回滚（或提交）。就关闭当前查询窗口，出现如下信息证实了猜测：
但是过程中的TRY CATCH语句块中已经写有ROLLBACK了。此时，不得不怀疑是不是自己写的TRY CATCH有问题，首先在过程中添加SELECT 1/0，对于这一错误ROLLBACK是起作用的。后来就去MSDN看TRY CATCH帮助文档，发现有这么一段说明：
这个完全符合我遇到的情况，所以原来就是引用表的数据库名写错，造成致命性错误，使得CATCH捕捉不到异常，所以事务没能ROLLBACK。
演示
我就改写我的过程来演示遇到的问题和解决这个问题的方案。T1表中只有3条数据
CREATE PROCEDURE [dbo].[test_sp] AS
BEGIN TRY
BEGIN TRAN
SET NOCOUNT ON;
INSERT INTO T1( col1, col2 )VALUES( 3,3);
SELECT * FROM T1;
SELECT * FROM [AdventureWorks2012].[dbo].[T2]
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
THROW;
END CATCH
在查询1窗口执行test_sp过程
EXEC test_sp
--连接上执行的 BEGIN TRANSACTION 语句的数目
SELECT @@TRANCOUNT AS Trancount
如果想关闭当前查询1窗口，弹出信息：
在查询2窗口查询T1和[AdventureWorks2012].[dbo].[T2]
--查询T1
SELECT * FROM T1;
表T1一直被查询1的事务占用：
--查询T2
SELECT * FROM [AdventureWorks2012].[dbo].[T2]
T2没有创建：
解决方案
首先使用KILL命令将查询1的连接给断开，然后在test_sp中设置XACT_ABORT为ON。如果XACT_ABORT为ON时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚；为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。 如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。 OFF 是默认设置。
SET XACT_ABORT ON;
我们在查询1窗口中重新运行，顺带尝试关闭窗口：
EXEC test_sp
SELECT @@TRANCOUNT AS Trancount
--查询T1
SELECT * FROM T1;
@@TRANCOUNT为0，T1表还是只有3条纪录，说明事务顺利的终止并且回滚了。
我们在AdventureWorks2012数据库中创建t2
CREATE TABLE t2 (col1 int)
在查询1中先运行一遍将第4条数据插入，再运行第二遍。
EXEC test_sp
由于主键约束，抛出异常，我们可以检查当前连接的事务连接数也是0：
SELECT @@TRANCOUNT AS Trancount
结论
设置XACT_ABORT为ON时，即使过程中没有TRY CATCH，如果语句导致任何异常才会发生回滚。所以我们要灵活使用XACT_ABORT，如果使用TRY CATCH能够捕捉所有需要解决的错误时，我们就不必开启XACT_ABORT，只有错误比较严重时才考虑XACT_ABORT为ON。
所以，我应该滚回去修改引用的数据库名就好了。
参考：
http://www.cnblogs.com/chenxizhang/archive/2008/07/29/1255737.html
https://technet.microsoft.com/zh-cn/library/ms164086.aspx
https://msdn.microsoft.com/zh-cn/library/ms188792(v=sql.120).aspx
https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/
返回顶部
