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

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

Это сводка script.

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?

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

Ответ 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

Ответ 2

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

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

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

Ответ 3

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

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

GIST: ЗДЕСЬ

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

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)

            THROW
        END
    END CATCH
END