SQL deadlocks when many MBAM clients connect to the MBAM recovery database

Applies To
Microsoft BitLocker Administration and Monitoring 2.5

Symptoms

When many clients connect to the Microsoft BitLocker Administration and Monitoring 2.5 recovery databases, SQL deadlocks may occur in the database. Therefore, keys cannot be recovered from the Helpdesk Portal or Self Service Portal. New clients receive errors when they try to encrypt as the MBAM service becomes unreachable. This causes timeouts and other errors. 

Additionally, the following errors occur in the MBAM svc trace logs under c:\inetpub\Microsoft BitLocker Management Solution\Logs\Recovery and Hardware Service\*.svclogs:

Note

Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Resolution

To resolve the issue, update the stored procedures associated with the MBAM recovery database. To do this, run the following Transact-SQL scripts:

PatchGetDomainIdProc.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 

      

RestoreGetDomainIdPRoc.sql
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