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

Question

标记事务
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 MARKServer: Msg 3920, Level 16, State 1, Line 3
WITH MARK option only applies to the first BEGIN TRAN WITH MARK.
The option is ignored.

Answer 1

是不是在proc_tmp中调用了commit?

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

你改为：begin tran exec aa
if @@TRANCOUNT>0
  commit tran

Answer 6

我向你说一下我的实际问题:
我是用PB调用存储过程，PB应用程序在执行存储过程之前会自行启动一个内部事务，我必须在报错时回滚整个存储过程做的修改，如果不启用标记事务就会报如下的错服务器: 消息 6401，级别 16，状态 1，过程 proc_tmp，行 25
无法回滚 aaa。没有找到任何该名称的事务或保存点如果启用标记事务又会报如下的错误EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 1，当前计数 = 0。你能不能给我想一个解决办法，只在存储过程中回滚

Answer 7

存储过程中，rollback使得事务跳出了最高层，但是程序确继续执行，没有跳出来。因此，在存储过程中，在任何rollback之前，都要加上“if @@TRANCOUNT>0”。在触发器中不必如此，因为程序的控制结构与rollback的结构一致。

Answer 8

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

Answer 9

错误 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