Вложенные хранимые процедуры, содержащие шаблон TRY CATCH ROLLBACK?

Question

Меня интересуют побочные эффекты и потенциальные проблемы следующего шаблона:

CREATE PROCEDURE [Name]
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        [...Perform work, call nested procedures...]
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    END CATCH
END

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

Однако, когда одна хранимая процедура вызывает другую хранимую процедуру для выполнения некоторого подразделения (при том понимании, что меньшая процедура иногда вызывается сама по себе), я вижу проблему, связанную с откатами - информационное сообщение (Уровень 16) выдается с заявлением The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION., Я полагаю, это потому, что откат в подпроцедуре всегда откатывает самую внешнюю транзакцию, а не только транзакцию, начатую в подпроцедуре.

Я хочу, чтобы все это было откатано и прервано, если возникнет какая-либо ошибка (и ошибка будет сообщена клиенту как ошибка SQL), я просто не уверен во всех побочных эффектах, которые исходят от внешних слоев при попытке отката транзакции это уже было отменено. Возможно проверка @@TRANCOUNT перед выполнением отката на каждом слое TRY CATCH?

Наконец, есть клиентская часть (Linq2SQL), которая имеет собственный уровень транзакций:

try
{
    var context = new MyDataContext();
    using (var transaction = new TransactionScope())
    {       
            // Some Linq stuff
        context.SubmitChanges();
        context.MyStoredProcedure();
        transactionComplete();
    }
}
catch
{
    // An error occured!
}

В случае, если хранимая процедура "MySubProcedure", вызываемая внутри MyStoredProcedure, вызывает ошибку, могу ли я быть уверен, что все ранее выполненное в MyStoredProcedure будет откатано, все операции Linq, выполненные SubmitChanges, будут откатаны, и, наконец, что ошибка будет записана? Или что мне нужно изменить в моем шаблоне, чтобы гарантировать, что вся операция является атомарной, при этом позволяя дочерним частям использоваться индивидуально (т.е. подпроцессы должны иметь ту же атомную защиту)

Answer 1

Это наш шаблон (журнал ошибок удален)

Это предназначено для обработки

• Статья Пола Рэндала "Нет такой вещи, как вложенная транзакция в SQL Server"
• Ошибка 266
• Триггерные откаты

Пояснения:

• все TXN начало и фиксация / откат должны быть в паре так, чтобы @@TRANCOUNT то же самое на входе и выходе

• несоответствия @@TRANCOUNT вызвать ошибку 266, потому что

  • BEGIN TRAN приращений @@TRANCOUNT

  • COMMIT декременты @@TRANCOUNT

  • ROLLBACK возвращается @@TRANCOUNT в ноль

• Вы не можете уменьшить @@TRANCOUNT для текущей области
  Это то, что вы думаете, это "внутренняя транзакция"

• SET XACT_ABORT ON подавляет ошибку 266, вызванную несоответствием @@TRANCOUNT
  А также имеет дело с такими проблемами, как "Тайм-аут транзакций SQL Server" на dba.se

• Это позволяет использовать TXN на стороне клиента (например, LINQ). Одна хранимая процедура может быть частью распределенной транзакции или транзакции XA или просто инициированной в клиентском коде (скажем,.net TransactionScope).

Использование:

• Каждый сохраненный процесс должен соответствовать одному и тому же шаблону.

Резюме

• Так что не создавайте больше TXN, чем вам нужно

Код

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Заметки:

• Проверка отката фактически избыточна из-за SET XACT_ABORT ON, Тем не менее, это заставляет меня чувствовать себя лучше, выглядит странно без, и позволяет в ситуациях, когда вы не хотите его

• Remus Rusanu имеет похожую оболочку, которая использует точки сохранения. Я предпочитаю атомарный вызов БД и не использую частичные обновления, такие как их статья

Answer 2

Я не парень Linq (и не Эрланд), но он написал абсолютные библии об обработке ошибок. Помимо проблем, которые Linq может добавить к вашей проблеме, на все остальные ваши вопросы следует ответить здесь:

http://www.sommarskog.se/error_handling/Part1.html

(Старая ссылка: http://www.sommarskog.se/error_handling_2005.html)

Answer 3

Чтобы решить проблему возврата номера ошибки и номера строки, упомянутых @AlexKuznetsov, можно поднять ошибку следующим образом:

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
DECLARE @ErrorNumber INT

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)

Answer 4

- Метод @Amanda выше не возвращает правильный номер ошибки

DECLARE  
  @ErrorMessage   nvarchar(4000),  
  @ErrorSeverity   int,  
  @ErrorState int,  
  @ErrorLine  int,  
  @ErrorNumber   int  

BEGIN TRY  
 SELECT 1/0; -- CATCH me  
END TRY  

BEGIN CATCH  

  DECLARE @err int = @@ERROR  

  PRINT @err           -- 8134, divide by zero  
  PRINT ERROR_NUMBER() -- 8134  

  SELECT  
    @ErrorMessage  = ERROR_MESSAGE(),  
    @ErrorSeverity = ERROR_SEVERITY(),  
    @ErrorState    = ERROR_STATE(),  
    @ErrorNumber   = ERROR_NUMBER(),  
    @ErrorLine     = ERROR_LINE()  

  -- error number = 50000 :(  
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)  

END CATCH  

-- error number = 8134  
SELECT 1/0

Answer 5

Если в CATCH не требуется специальной обработки ошибок, за исключением повторной обработки и цепочки вызовов хранимых процедур, может оказаться целесообразным использовать такой простой шаблон:

create procedure someNestedSP
as
SET XACT_ABORT ON
begin transaction
-- do some work or call some other similar SP
commit transaction

Он также откатит корневую транзакцию со всеми "вложенными" в случае любой ошибки, но код будет короче и проще, чем решение @ gbn. Еще XACT_ABORT заботится о большинстве проблем, упомянутых там.

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