令人郁闷的“事务中的变量赋值错误”

 

         事务中的变量（包括表变量）的操作是不受事务控制的。但是反过来，事务中的变量操作失败，却会导致事务提交失败，这个有点让人郁闷。

         下面的脚本演示这个问题。示例演示分拆以逗号分隔的 @ids 中的每个 id， 如果这个 id 是数字（int型），则做后面的处理；如果不是数字（赋值失败，进入CATCH块），则跳过这个id，处理下一个。整个处理在一个事务中进行。

DECLARE

    @ids varchar(1000);

SET @ids = '1,a,3,';

 

BEGIN TRY

    BEGIN TRAN;

   

    -- 循环分拆@ids 中以逗号分隔的每个id

    WHILE CHARINDEX(',', @ids) > 0

    BEGIN

       DECLARE

           @id int;

      

       BEGIN TRY

           -- 获取当前的id

           SET @id =LEFT(@ids, CHARINDEX(',', @ids) - 1);

       END TRY

       BEGIN CATCH

           -- 从@ids 中去掉当前的id

           SET @ids = STUFF(@ids, 1, CHARINDEX(',', @ids), '');

           CONTINUE;

       END CATCH

       -- 从@ids 中去掉当前的id

       SET @ids = STUFF(@ids, 1, CHARINDEX(',', @ids), '');

 

       -- 处理id

       RAISERROR('current id: %d', 10, 1, @id) WITH NOWAIT;

    END

   

    COMMIT TRAN;

END TRY

BEGIN CATCH

    IF XACT_STATE() <> 0

       ROLLBACK TRAN;

   

    SELECT

       err_line = ERROR_LINE(),

       err_message = ERROR_MESSAGE();

END CATCH

 

         这个脚本执行后，输出消息如下：

current id: 1

current id: 3

   err_line err_message

----------- -------------------------------------------------------

         30 当前事务无法提交，而且无法支持写入日志文件的操作。请回滚该事务。

 

(1 行受影响)

 

这个结果表明，@ids 中的所有 id 确实在循环中处理过，但第二个id由于不是数字，所以跳过了。但最终提交事务失败了，因为第二个id不是数字，导致赋值失败，从而导致事务不可提交。

发现这个问题是因为公司在用Service Broker传递业务数据，由于传递的xml数据有问题，导致类型不是期望有类型，从而使处理失败。

个人觉得这个问题有点令人讨厌，按照我的理解，既然不受事务控制，那么它也不应该反过来影响事务，可是结果与理解的不一样，值得注意。

最后补充一下：写这个的重点在于提醒大家，在事务处理中，不要忽略那些看起来与事务无关的处理，它们出错一样会影响事务的最终提交。当然，解决的办法是把这与事务无关的操作放在事务外（不过如果是存储过程嵌套，就不好控制）。另外一个，只要处理可能会在事务中，则出错时都抛出错误，而不试图忽略错误，这样可以解决问题。