Разрыв транзакций при использовании SP_ExecuteSQL

Я использую SQLServer 2014, и у меня есть простая БД с одной таблицей, которая имеет идентификатор и столбец varchar, называемый данными. Есть странное поведение, когда я запускаю следующее утверждение:

SET XACT_ABORT ON
BEGIN TRANSACTION
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

COMMIT

SSMS показывает, что произошла ошибка, потому что я попытался выполнить неправильный запрос в sp_executesql вызов. Тем не менее, это также показывает, 1 row(s) affected, Если я запускаю запрос на выборку в таблице testTable, я вижу, что было вставлено значение "b".

Если я заверну утверждения в TRY/CATCH Блок все работает как положено, и все транзакции откатывается:

BEGIN TRANSACTION
BEGIN TRY
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK
END CATCH

Не должен SET XACT_ABORT ON обеспечить откат всей транзакции, если что-то пойдет не так? Есть ли настройка, которую мне не хватает?

Спасибо

Источник

1 ответ

Решение

Это происходит потому, что синтаксическая ошибка времени выполнения, которая не заключена в TRY/CATCH не прерывает активную транзакцию, даже если XACT_ABORT установлен в ON, Точные правила для того, что делает и не прерывает и при каких обстоятельствах, не являются прямыми или очевидными. Erland Sommarskog имеет отличную информацию об обработке ошибок в целом и правилах того, что делает и не прерывает в частности.

Я не буду воспроизводить все это здесь, но вот проблема, сводящаяся к ее основам:

SET XACT_ABORT ON   -- or OFF, it makes no difference
BEGIN TRANSACTION
EXEC ('SELECT')     -- Incorrect syntax near 'SELECT'
PRINT @@TRANCOUNT   -- Prints 1, transaction is still going
COMMIT
PRINT @@TRANCOUNT   -- Prints 0, transaction succeeded

Несмотря на XACT_ABORT ONвыполнение не только не останавливается, транзакция даже не прерывается. Добавление TRY/CATCH меняет правила:

SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')              -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.' -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT            -- Prints 1, transaction is still going, but it's doomed
END CATCH
-- Error here: 
-- 'Uncommittable transaction is detected at the end of the batch. 
--  The transaction is rolled back.'

Теперь транзакция обречена, и если мы не откатим ее сами, SQL Server сделает это за нас (с ошибкой). Это обречение полностью из XACT_ABORTпотому что отключение дает еще раз что-то другое:

SET XACT_ABORT OFF
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')               -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.'  -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT             -- Prints 1, transaction is still going
END CATCH
PRINT @@TRANCOUNT                 -- Prints 1, transaction is still going!
ROLLBACK

Мораль этой истории такова: правильная обработка ошибок в T-SQL очень сложна. То, что обычно работает для меня, делает SET XACT_ABORT ON для любого нетривиального пакета операторов, а также для того, чтобы транзакции инициировались и фиксировались или полностью откатывались за пределы SQL Server (через код клиента). Это позволяет обойти многие трудности с пониманием того, что делает и не останавливает или обрекает транзакцию, потому что любая ошибка, которую SQL Server передает клиенту, в конечном итоге приводит к откату. Но, конечно, даже это не серебряная пуля.

Источник

ПОПРОБОВАТЬ... ЛОВИТЬ с ТРАНЗАКЦИЕЙ для MSSQL

BEGIN TRANSACTION;  
  
BEGIN TRY  
    -- Generate a constraint violation error.  
    SELECT 1/0
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

Referenc

Источник
Другие вопросы по тегам