TSQL Try / Catch в транзакции или наоборот?

Question

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

Это краткое изложение сценария.

BEGIN TRANSACTION SCHEDULEDELETE
    BEGIN TRY
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
    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

            ROLLBACK TRANSACTION SCHEDULEDELETE
            PRINT 'Error detected, all changes reversed.'
    END CATCH

--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.

--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.

Это моя первая запись транзакции. Правильно ли / лучше, чтобы блок TRY/CATCH был внутри транзакции или транзакция должна быть внутри блока TRY?

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

Answer 1

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

Если что-то пойдет не так, пока вы находитесь в TRY блок, и вы открыли транзакцию, элемент управления перейдет к CATCH блок. Просто откатите вашу транзакцию там и сделайте другую обработку ошибок, как требуется.

Я добавил небольшой чек для любой открытой транзакции, используя @@TRANCOUNT функция, прежде чем фактически откат транзакции. Это не имеет особого смысла в этом сценарии. Это более полезно, когда вы делаете некоторые проверки в вашем TRY перед открытием транзакции, например, проверки значений параметров и прочего, а также выдачи ошибки в TRY заблокировать, если какая-либо из проверок не пройдена. В этом случае элемент управления перейдет к CATCH блокировать даже без открытия транзакции. Там вы можете проверить любую открытую транзакцию и выполнить откат, если есть открытые. В вашем случае вам действительно не нужно проверять открытые транзакции, так как вы не введете CATCH заблокировать, если что-то идет не так в вашей транзакции.

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

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    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
END CATCH

Answer 2

В дополнение к полезному совету М.Али и Дина, приведенному выше, небольшая помощь для тех, кто хочет использовать новую парадигму TRY CATCH THROW в SQL SERVER:

(Я не мог легко найти полный синтаксис, поэтому добавляю его сюда)

ГИСТ: ЗДЕСЬ

Пример кода хранимой процедуры здесь (из моей сущности):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[pr_ins_test]
@CompanyID INT
AS

SET NOCOUNT ON

BEGIN

    DECLARE @PreviousConfigID INT

    BEGIN TRY
        BEGIN TRANSACTION MYTRAN; -- Give the transaction a name
        SELECT 1/0  -- Generates divide by zero error causing control to jump into catch

        PRINT '>> COMMITING'
        COMMIT TRANSACTION MYTRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN 
            PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MYTRAN; -- The semi-colon is required (at least in SQL 2012)


        END
        THROW
    END CATCH
END

Answer 3

Никогда не ждите, пока конечный пользователь совершит транзакцию, если это не база данных в однопользовательском режиме.

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

Лучше сначала выполнить запрос на выборку, чтобы определить количество подходящих строк, представить его конечному пользователю, а после подтверждения выполнить фактическое удаление.