代码审查：SQL存储过程事务未配套TRY异常处理问题

我目前正在对新增的SQL存储过程开展代码审查，有两点待确认的优化判断：

• 原始实现将COMMIT TRANSACTION的作用范围覆盖整个存储过程，我认为事务仅需包裹INSERT语句执行段即可，前置的重复记录查询逻辑不需要纳入事务范围
• 代码通过BEGIN TRANSACTION开启事务时，必须配套对应的异常处理机制，避免执行异常时事务未正常回滚、持续占用数据库锁资源

优化前原始代码 #

BEGIN   
DECLARE @paymentSpecificationId BIGINT ;
BEGIN TRANSACTION
    SET @paymentSpecificationId = (SELECT Id FROM [dbo].[PaymentSpecifications] 
    WHERE PaymentSpecificationGuid = @PaymentSpecificationGuid) ;

    IF(@paymentSpecificationId > 0)
    BEGIN
        SELECT @paymentSpecificationId AS Results;
        COMMIT TRANSACTION;
        RETURN;
    END
    INSERT INTO [dbo].[PaymentSpecifications] 
    (
        [CurrencyBalanceId],
        [PaymentSpecificationGuid],
        [PaymentSpecificationTypeId],
        [ExternalId],
        [Reference],
        [ExternalOriginatorId],
        [EventTimestamp],
        [Created]
    )
    VALUES 
    (
        @CurrencyBalanceId,
        @PaymentSpecificationGuid,
        @PaymentSpecificationTypeId,
        @ExternalId,
        @Reference,
        @ExternalOriginatorId,
        @EventTimestamp,
        @Created
    )
    SELECT SCOPE_IDENTITY() AS Results;
COMMIT TRANSACTION;
END

按照优化思路调整后的代码 #

BEGIN   
    
    BEGIN TRY
        DECLARE @paymentSpecificationId BIGINT ;
        
        SET @paymentSpecificationId = (SELECT Id FROM [dbo].[PaymentSpecifications] 
        WHERE PaymentSpecificationGuid = @PaymentSpecificationGuid) ;

        IF(@paymentSpecificationId > 0)
        BEGIN
            SELECT @paymentSpecificationId AS Results;
            RETURN;
        END
        
        BEGIN TRANSACTION
            INSERT INTO [dbo].[PaymentSpecifications] 
            (
                [CurrencyBalanceId],
                [PaymentSpecificationGuid],
                [PaymentSpecificationTypeId],
                [ExternalId],
                [Reference],
                [ExternalOriginatorId],
                [EventTimestamp],
                [Created]
            )
            VALUES 
            (
                @CurrencyBalanceId,
                @PaymentSpecificationGuid,
                @PaymentSpecificationTypeId,
                @ExternalId,
                @Reference,
                @ExternalOriginatorId,
                @EventTimestamp,
                @Created
            )
            SELECT SCOPE_IDENTITY() AS Results;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0              
        ROLLBACK TRANSACTION             
                
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int              
        SELECT @ErrMsg = ERROR_MESSAGE(),              
        @ErrSeverity = ERROR_SEVERITY()              
              
        RAISERROR(@ErrMsg, @ErrSeverity, 1)              

    END CATCH
    
    
END
GO

以上是我按照前述优化思路给出的调整版本，恳请各位技术同仁指正。

内容的提问来源于stack exchange，提问作者dexon