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
![1460734370861.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/f3a10ea2c9a646d0297f5909bc405b52.png)
![1460736223442.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/999eb17b3ad29f78547ee57afad0cb74.png)
如果想关闭当前 查询1窗口,弹出信息:
![1460734736433.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/9c1c09c6b8cff988f6f7dbeb68815cc0.png)
在查询2窗口查询T1
和[AdventureWorks2012].[dbo].[T2]
--查询T1
SELECT * FROM T1;
表T1
一直被查询1的事务占用:
![1460734465672.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/d3985c459c5096983f89ef85cad645ce.png)
--查询T2
SELECT * FROM [AdventureWorks2012].[dbo].[T2]
T2
没有创建:
![1460734518647.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/77114beba5be935a75613b24229a2593.png)
解决方案
首先使用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;
![1460735835769.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/484572d337969b3054b3a870d1e5e4f6.png)
![1460736150990.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/48902881618859be41a1489905d41a25.png)
![1460736007326.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/bb4ecec26f7d7943b6d345727fa32cc9.png)
@@TRANCOUNT
为0,
T1
表还是只有3条纪录,说明事务顺利的终止并且回滚了。
我们在AdventureWorks2012
数据库中创建t2
CREATE TABLE t2 (col1 int)
在查询1中先运行一遍将第4条数据插入,再运行第二遍。
EXEC test_sp
![1460736881949.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/973afd983f2d1f083ca4ebc1945ca929.png)
由于主键约束,抛出异常,我们可以检查当前连接的事务连接数也是 0:
SELECT @@TRANCOUNT AS Trancount
![1460736950117.jpg enter description here](https://i-blog.csdnimg.cn/blog_migrate/0151f671996bbdd5aeb4a6b856c86600.png)
结论
设置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/
返回顶部