當許多 MBAM 用戶端連線到 MBAM 的復原資料庫的 SQL 死結

USE [MBAM Recovery and Hardware]GO
/****** Object:  StoredProcedure [RecoveryAndHardwareCore].[GetDomainId]    Script Date: 05/09/2014 07:58:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Kirill Tropin>
-- Create date: <6/18/2010>
-- Description: <Returns DomainId for provided Domain Name. If domain isn't saved - will add it.>
-- =============================================
ALTER PROCEDURE [RecoveryAndHardwareCore].[GetDomainId]
    @DomainName nvarchar(255)
WITH EXECUTE AS OWNER
AS
BEGIN 
    -- Validating input parameters
    IF (@DomainName IS NULL)
    BEGIN
        RETURN -1
    END
    -- Adding domain if needed and returning DomainId
    DECLARE @OrigTranCount int
    SET @OrigTranCount = @@TRANCOUNT
    IF @OrigTranCount > 0
        SAVE TRAN myTran
    ELSE
        BEGIN TRAN
    BEGIN TRY
        DECLARE @DomainId int
        SET @DomainId = (
                        SELECT Id
                        FROM Domains
                        WITH (READPAST)     -- If a committed domain exists then get it, otherwise returns NULL
                        WHERE (Domains.DomainName = @DomainName)
                        )
        -- Inserting Domain since it wasn't there
        IF (@DomainId IS NULL)
        BEGIN
            /*
            In the unlikely event that two clients simultaneously insert the same new domain,
            we can end up with a race condition as they both attempt to insert the domain.
            One of them will get an exception (error code 2627) due to the unique constraint
            and should use this to trigger a re-read of the domain.
            */
            WHILE @DomainId IS NULL
            BEGIN
                BEGIN TRY
                    INSERT INTO Domains WITH (ROWLOCK, UPDLOCK)
                                (DomainName)
                    VALUES      (@DomainName)
                    SET @DomainId = @@IDENTITY
                END TRY
                BEGIN CATCH
                    DECLARE @ErrorNumber INT
                    DECLARE @ErrorSeverity INT
                    DECLARE @ErrorState INT
                    SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
                    IF @ErrorNumber = 2627
                    BEGIN
                        SET @DomainId = (
                            SELECT Id
                            FROM Domains
                            WITH (READCOMMITTED)
                            WHERE (Domains.DomainName = @DomainName)
                            )
                    END
                    ELSE
                    BEGIN
                        RAISERROR (@ErrorNumber, @ErrorSeverity, @ErrorState)
                    END
                END CATCH
            END
        END

        IF @OrigTranCount = 0
            COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @OrigTranCount = 0
            ROLLBACK TRAN
        ELSE 
            IF XACT_STATE() <> -1
                ROLLBACK TRAN myTran
        DECLARE @ErrorMessage1 NVARCHAR(4000);
        DECLARE @ErrorSeverity1 INT;
        DECLARE @ErrorState1 INT;
        SELECT @ErrorMessage1 = ERROR_MESSAGE();
        SELECT @ErrorSeverity1 = ERROR_SEVERITY();
        SELECT @ErrorState1 = ERROR_STATE();
        RAISERROR (@ErrorMessage1, -- Message text.
                   @ErrorSeverity1, -- Severity.
                   @ErrorState1 -- State.
                   );
    END CATCH
    RETURN @DomainId
END 

USE [MBAM Recovery and Hardware]GO
/****** Object:  StoredProcedure [RecoveryAndHardwareCore].[GetDomainId]    Script Date: 05/09/2014 14:06:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Kirill Tropin>
-- Create date: <6/18/2010>
-- Description: <Returns DomainId for provided Domain Name. If domain isn't saved - will add it.>
-- =============================================
ALTER PROCEDURE [RecoveryAndHardwareCore].[GetDomainId]
    @DomainName nvarchar(255)
WITH EXECUTE AS OWNER
AS
BEGIN 
    -- Validating input parameters
    IF (@DomainName IS NULL)
    BEGIN
        RETURN -1
    END
    -- Adding domain if needed and returning DomainId
    DECLARE @OrigTranCount int
    SET @OrigTranCount = @@TRANCOUNT
    IF @OrigTranCount > 0
        SAVE TRAN myTran
    ELSE
        BEGIN TRAN
    BEGIN TRY
        SET NOCOUNT ON
        -- Use a merge statement to guarantee that the domain will be in the table
        -- when the SELECT statement is called to get it.
        MERGE Domains WITH (HOLDLOCK)
        USING (SELECT @DomainName as DomainName) AS NewDomain
        ON Domains.DomainName = NewDomain.DomainName
        WHEN NOT MATCHED THEN
            INSERT (DomainName)
                VALUES (NewDomain.DomainName)
        ;
        DECLARE @DomainId int
        SET @DomainId = (
                        SELECT Id
                        FROM Domains
                        WHERE Domains.DomainName = @DomainName
                        )
        IF @OrigTranCount = 0
            COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @OrigTranCount = 0
            ROLLBACK TRAN
        ELSE 
            IF XACT_STATE() <> -1
                ROLLBACK TRAN myTran
        DECLARE @ErrorMessage1 NVARCHAR(4000);
        DECLARE @ErrorSeverity1 INT;
        DECLARE @ErrorState1 INT;
        SELECT @ErrorMessage1 = ERROR_MESSAGE();
        SELECT @ErrorSeverity1 = ERROR_SEVERITY();
        SELECT @ErrorState1 = ERROR_STATE();
        RAISERROR (@ErrorMessage1, -- Message text.
                   @ErrorSeverity1, -- Severity.
                   @ErrorState1 -- State.
                   );
    END CATCH
    RETURN @DomainId
END
GO