SQL Transaction uncommittable при использовании try..catch.. Почему?

Привет, полезные друзья,

Мы сталкиваемся с проблемой здесь, и я не могу понять, почему она ведет себя так, как она себя ведет. Надеюсь, вы можете мне помочь.

Учитывая следующие две (упрощенные) хранимые процедуры в TSQL (SQL Server 2008R2)

create procedure [datetransaction1] 
as
begin
    begin try
        begin transaction
        declare @a datetime
        exec datetransaction2 '2013-02-02 22:21', @a output
        select @a
        exec datetransaction2 '2013-020222:22', @a output
        select @a
        exec datetransaction2 '2013-02-02 22:23', @a output
        select @a

        commit transaction
    end try
    begin catch
        print 'Catch'
    end catch
end

create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT  
AS
BEGIN 
    BEGIN TRY
        if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
        else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
        else SET @text = replace(@text, ' ', 'T') 
        PRINT 'trydate:' + @text
        SELECT @res =convert(datetime, @text, 126)
    END TRY
    BEGIN CATCH
        PRINT ERROR_SEVERITY()
        PRINT 'errordate:' + @text
    END CATCH
END

Если вы затем выполните exec datetransaction1, мы увидим, что все 3 вызова datetransaction2 выполнены, причем первая и последняя (как ожидалось) работают правильно, а вторая - в блок CATCH внутри datetransaction2.

До сих пор так хорошо.

Но тогда мы приземляемся в блоке catch datetransaction1 с сообщением о невозможности транзакции:

Msg 266, Level 16, State 2, Procedure datetransaction1, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Что не должно произойти (я думаю). Мы обнаружили ошибки в подпрограммах, поэтому почему транзакция неожиданно стала неучтенной?

Может кто-нибудь объяснить это мне?

Обратите внимание, что мы, возможно, найдем способ обойти это, но я больше заинтригован идеей. Почему эта транзакция внезапно становится неуместной здесь?

Ответ 1

Причина в том, что Sql Server обрекает транзакцию WHENEVER, независимо от того, является ли она в блоке TRY или нет, независимо от того, сохранено ли состояние транзакции или нет, произошла ли ошибка в процедуре или нет, что бы вы ни делали.

Когда ошибка возникает в одном из вызовов процедуры, транзакция обречена. Вы можете полностью откатить его полностью (любая точка сохранения не поможет).

В конце концов, поскольку транзакция обречена, вы не можете ее совершить...

Попробуйте следующее:

SET XACT_ABORT OFF -- pityful attempt to avoid the doom
BEGIN TRANSACTION
--
-- some useful TSQL instructions could be here
--
SAVE TRANSACTION SQL_SERVER_IS_GARBAGE -- another pityful attempt to do a partial restore
PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
BEGIN TRY
  DECLARE @n int
  SELECT @n = CONVERT(int,'ABC') -- some very benign data error here (example)
  COMMIT TRANSACTION -- will never reach here
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
  PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
  IF XACT_STATE()=-1 BEGIN
    PRINT 'The transaction is doomed, say thanks to Sql Server!'
    PRINT 'CANNOT restore to the save point!'
    -- You can just cry here and abort all, you lost all the useful work
    ROLLBACK TRANSACTION
  END
  ELSE BEGIN
    -- would restore before the error if the transaction was not doomed
    ROLLBACK TRANSACTION SQL_SERVER_IS_GARBAGE -- will never reach here either!
  END  
END CATCH

Ответ 2

Так как второй вызов функции datetransaction2 вызвал ошибку уровня важности 16, SQL Server автоматически откатил вашу транзакцию. Это причина ошибки, которую вы видите.

Вот действительно хорошая статья, почему транзакции попадают в обреченное состояние при возникновении ошибки уровня важности 16.

Чтобы убедиться, что он автоматически возвращается, я добавил следующую строку в ваш datetransaction2 proc: print XACT_STATE()

  create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT  
  AS
  BEGIN 
     print 'Start'
      print XACT_STATE() 
      BEGIN TRY
          if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
          else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
          else SET @text = replace(@text, ' ', 'T') 
          PRINT 'trydate:' + @text
          SELECT @res =convert(datetime, @text, 126)
      END TRY
      BEGIN CATCH
           print XACT_STATE() 
           print 'Catch'
          PRINT ERROR_SEVERITY()
          PRINT 'errordate:' + @text
      END CATCH
      print XACT_STATE() 
      print 'End'
  END

Ответ 3

Похоже, что транзакция фиксации никогда не достигается, потому что код переходит к блоку catch. Чтобы избежать этого, вы можете добавить транзакцию возврата к вашему блоку catch так:

alter procedure [datetransaction1] 
as
begin
    begin try
        begin transaction
        declare @a datetime
        exec datetransaction2 '2013-02-02 22:21', @a output
        select @a
        exec datetransaction2 '2013-020222:22', @a output
        select @a
        exec datetransaction2 '2013-02-02 22:23', @a output
        select @a

        commit transaction
    end try
    begin catch
        print 'Catch'
         rollback transaction
    end catch
end

Ответ 4

Вкратце: оператор catch часто вызывает откат (см. 1). Это зависит от XACT_ABORT. Затем откаты не содержатся в SP, где они инициированы (см. 2).

Первая ссылка (1) дает обходное решение с использованием @@trancount, см. принятый ответ там.

Ответ 5

Я уверен, что эта конкретная ошибка может возникнуть только при использовании Try/Catch.

В конечном итоге эта ошибка означает, что транзакция была запущена и произошла ошибка, которая не вызывает автоматического отката. Есть множество возможных причин. Параметр xact_abort, который включен (по умолчанию выключен), только один. Вы обнаружили ошибку, которая не выполнялась автоматически, и вы не откатили транзакцию самостоятельно.

Вместо того, чтобы понимать, какие ошибки требуют отката, и когда лично я начинаю транзакцию самостоятельно или нет, я помещаю следующий код в КАЖДЫЙ блок catch.

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

Это определенно предотвращает проблему, в то же время гарантируя, что ваши данные будут вести себя точно так, как вы ожидаете. IOW всегда отката по ошибке. Делая это последовательно, вы гарантируете, что не имеет значения, если какой-то вызывающий вашей процедуры начал транзакцию, вы запускаете ее или если какая-то вызываемая вами процедура запускает одну и оставляет ее висячей; он всегда откатывается при обнаружении ошибки.