100分 存储过程中"事务回滚"问题

[tm728a]

我写了一段存储过程proc_tmp，流程：（其中启动了一个事务aaa，在调用完另一个存储过程后按返回值做rollback和commit操作)

我又在查询分析器里写了如下一段代码：
begin transaction www
exec proc_tmp
rollback transaction www

报如下错误:
服务器: 消息 6401，级别 16，状态 1，过程 proc_tmp，行 25
无法回滚 aaa。没有找到任何该名称的事务或保存点。
但我明明在存储过程proc_tmp中启动了aaa事务，晕！！！！！

如果在查询分析器中只写：
exec proc_tmp
就不会报错


这是什么原因我实在搞不明白了，请教各路高手

[txlicenhe]

是不是在proc_tmp中调用了commit?

[pengdali]

标记事务
WITH MARK 选项使事务名置于事务日志中。将数据库还原到早期状态时，可使用标记事务替代日期和时间。有关更多信息，请参见将数据库还原到前一个状态、恢复到命名事务和 RESTORE。

另外，若要将一组相关数据库恢复到逻辑上一致的状态，必须使用事务日志标记。标记可由分布式事务置于相关数据库的事务日志中。将这组相关数据库恢复到这些标记将产生一组在事务上一致的数据库。在相关数据库中放置标记需要特殊的过程。有关更多信息，请参见"相关数据库的备份和恢复"。

只有当数据库由标记事务更新时，才在事务日志中放置标记。不修改数据的事务不被标记。

在已存在的未标记事务中可以嵌套 BEGIN TRAN new_name WITH MARK。嵌套后，new_name 便成为事务的标记名，不论是否已为事务提供了该名称。在下例中，M2 是标记名。

BEGIN TRAN T1
UPDATE table1 ...
BEGIN TRAN M2 WITH MARK
UPDATE table2 ...
SELECT * from table1
COMMIT TRAN M2
UPDATE table3 ...
COMMIT TRAN T1
尝试标记已标记的事务将产生警告（非错误）消息：

BEGIN TRAN T1 WITH MARK
UPDATE table1 ...
BEGIN TRAN M2 WITH MARK

Server: Msg 3920, Level 16, State 1, Line 3
WITH MARK option only applies to the first BEGIN TRAN WITH MARK.
The option is ignored.

[tm728a]

但为什么我在查询分析器中只写
exec proc_tmp
就不会报那种错误呢

[tm728a]

我在proc_tmp中调用了commit，但是是有条件调用，如果执行了commit的话就不会报那个错误

[pengdali]

嵌套事务时，该语句将所有内层事务回滚到最远的 BEGIN TRANSACTION 语句。

[pengdali]

你改为：

begin tran

exec aa


if @@TRANCOUNT>0
commit tran

[tm728a]

我向你说一下我的实际问题:
我是用PB调用存储过程，PB应用程序在执行存储过程之前会自行启动一个内部事务，我必须在报错时回滚整个存储过程做的修改，如果不启用标记事务就会报如下的错

服务器: 消息 6401，级别 16，状态 1，过程 proc_tmp，行 25
无法回滚 aaa。没有找到任何该名称的事务或保存点

如果启用标记事务又会报如下的错误

EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 1，当前计数 = 0。

你能不能给我想一个解决办法，只在存储过程中回滚

[pengdali]

你的代码。

[chmj718]

对啊，代码放上来看看，这样空说说不清楚啊

[HardThink]

关注

[shuiniu]

在一系列嵌套的事务中用一个事务名给多个事务命名对该事务没有什么影响。
系统仅登记第一个（最外部的）事务名。
回滚到其它任何名字（有效的保存点名除外）都会产生错误

[shuiniu]

try：
begin transaction aaa
/*
一定要用同proc_tmp中事务的名称一致
否则proc_tmp内部 rollback tran aaa 就会出错
*/
exec proc_tmp
if @@TRANCOUNT>0
commit tran aaa

[shuiniu]

错误 266
严重级别 16
消息正文
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = %1!，当前计数 = %2!。

解释
如果某存储过程退出时其 @@TRANCOUNT 值与进入该存储过程时不同，则 Microsoft® SQL Server™ 返回错误 266。



说明 该错误可忽略，因为它只将消息发送到客户端而不影响执行。


下例重新产生该问题：

CREATE PROCEDURE test
AS
SELECT @@TRANCOUNT
ROLLBACK TRANSACTION
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE test
GO

因为 @@TRANCOUNT 在两个 SELECT 语句中不相同，所以从存储过程返回时产生错误 266。

这是预期的行为，但它不表示事务不可以在一个存储过程内启动、完成或终止。而必须注意以便 @@TRANSACTION 函数在存储过程的入口和出口处匹配。有关更多信息，请参见 ROLLBACK TRANSACTION。

当编写嵌套存储过程时该问题发生的可能性更大。

对策
存在可以使存储过程不出现该错误的解决方案。以下是一组解决方案，每种方案都带有示例代码：

从相同的存储过程嵌套级（事务开始处）执行最终的 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句，如下例所示：
-- Example 1.a
CREATE PROCEDURE test1a
AS
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE test1a
ROLLBACK TRANSACTION
GO
-- Example 1.b
CREATE PROCEDURE test1c
AS
SELECT @@TRANCOUNT
GO
CREATE PROCEDURE test1b
AS
BEGIN TRANSACTION
EXEC test1c
COMMIT TRANSACTION
GO
EXECUTE test1b
GO

如果嵌套事务用于存储过程中，则执行匹配的提交。


说明 在 @@TRANCOUNT 等于 0（零）之前不提交事务。

-- Example 2
CREATE PROCEDURE test2b
AS
SELECT @@TRANCOUNT
BEGIN TRANSACTION
SELECT @@TRANCOUNT
COMMIT TRANSACTION
SELECT @@TRANCOUNT
GO
CREATE PROCEDURE test2a
AS
BEGIN TRANSACTION
EXECUTE test2b
COMMIT TRANSACTION
GO
EXECUTE test2a
GO

如果需要回滚，并且存储过程嵌套级与事务开始处不同，则以有效的用户定义错误使用 RAISERROR，并在 EXECUTE 语句之后检查 @@ERROR 函数。
-- Example 3
USE master
EXECUTE sp_addmessage 50001, 16, 'Rollback of transaction in test3'
GO
CREATE PROCEDURE test3
AS
RAISERROR (50001,16,1)
GO
BEGIN TRANSACTION
EXEC test3
IF @@error <> 50001
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO

该规则的例外是，如果某触发器执行回滚，则 @@TRANCOUNT 不需要匹配其起始值，因为批处理已终止。但是，如果由触发器调用的存储过程终止了事务，则可能导致该问题。
-- Example 4
CREATE TABLE x (col1 int)
GO
CREATE TRIGGER xins
ON x
FOR INSERT AS
ROLLBACK TRANSACTION
GO
CREATE PROCEDURE sp_xinsert
AS
SELECT @@TRANCOUNT
INSERT x (col1) VALUES (1)
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE sp_xinsert
IF @@error <> 0
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO
SELECT *
FROM x

[zjcxc]

将两个存储过程都贴出来.

[suiyun]

aaa那里用save行不行？

[w_rose]

存储过程中，rollback使得事务跳出了最高层，但是程序确继续执行，没有跳出来。因此，在存储过程中，在任何rollback之前，都要加上“if @@TRANCOUNT>0”。

在触发器中不必如此，因为程序的控制结构与rollback的结构一致。