SQL Server 2016 -在创建不存在的记录时锁定存储过程

Question

我的数据库中有以下表:

------------------------------------------
| EventDefinitions                       |
------------------------------------------
| EventDefinitionId:    uniqueidentifier |
| Code:                 varchar(63)      |
| EventSystem:          int              |
------------------------------------------
------------------------------------------
| Event                                  |
------------------------------------------
| EventId:              uniqueidentifier |
| EventDateTime:        datetime2        |
| EventDefinitionId:    uniqueidentifier |
------------------------------------------

EventDefinitions (1) <- Event (n)
EventDefinition对EventSystem和Code有一个独特的约束。

我正在研究一个存储过程，它应该创建一个事件记录并将其映射到正确的EventDefinition记录，基于指定的代码和EventSystem。
如果没有匹配的EventDefinition存在，过程应该创建一个新的EventDefinition。
此过程将由多个应用程序并发执行。

我的目标:

• 程序必须是并行可执行的。
  使用尽可能少的锁，以便同时写入多个事件记录。
• 当EventDefinition不存在时，它应该只由一个进程创建。
  该过程应该创建某种锁，以确保带有指定代码和EventSystem的EventDefinition记录只创建一次(否则DB将抛出唯一的约束违反异常)。其他进程应该映射事件记录到新创建的EventDefinition记录。

以下是我到目前为止所做的尝试:

CREATE PROCEDURE dbo.procWriteEvent @eventCode varchar(63), @eventSystem int
AS
    DECLARE @eventDefinitionId uniqueidentifier
    DECLARE @insertedEventDefinition table(EventDefinitionId uniqueidentifier)
    DECLARE @currentLevel varchar(255)
    BEGIN TRANSACTION
        -- Try read event definition
        SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM EventDefinitions WHERE EventSystem = @eventSystem AND Code = @eventCode )
        -- Create event definition if not exists
        IF @eventDefinitionId IS NULL
            BEGIN
                BEGIN TRANSACTION
                    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
                    -- Check if event definition was created by an other process
                    SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM EventDefinitions WHERE EventSystem = @eventSystem AND Code = @eventCode )
                    -- Create event definition
                    IF @eventDefinitionId IS NULL
                        BEGIN
                            INSERT INTO EventDefinitions (Code, EventSystem, Severity)
                                OUTPUT Inserted.EventDefinitionId INTO @insertedEventDefinition
                                VALUES (@eventCode, @eventSystem, 0)
                            SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM @insertedEventDefinition)
                        END
                COMMIT TRANSACTION
                SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            END
        INSERT INTO Events (EventDefinitionId, EventDateTime)
            VALUES (@eventDefinitionId, GETDATE())
    COMMIT TRANSACTION

问题是，我当前的SQL代码在并行执行过程时会产生死锁。

Answer 1

你的代码太复杂了。Sql server自己做最多的检查和阻塞。只要尝试/捕获插入，如果由于UNIQUE约束违反而失败，则重新读取EventDefinitions。的

CREATE PROCEDURE dbo.procWriteEvent @eventCode varchar(63), @eventSystem int
AS
    DECLARE @eventDefinitionId uniqueidentifier
    DECLARE @insertedEventDefinition table(EventDefinitionId uniqueidentifier)
    DECLARE @currentLevel varchar(255)
    -- Try read event definition
    SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM EventDefinitions WHERE EventSystem = @eventSystem AND Code = @eventCode )
    -- Create event definition if not exists
    IF @eventDefinitionId IS NULL
    BEGIN TRY
        INSERT INTO EventDefinitions (Code, EventSystem, Severity)
        OUTPUT Inserted.EventDefinitionId INTO @insertedEventDefinition
        VALUES (@eventCode, @eventSystem, 0);
        SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM @insertedEventDefinition)
    END TRY
    BEGIN CATCH
        SET @eventDefinitionId = (SELECT TOP 1 EventDefinitionId FROM EventDefinitions WHERE EventSystem = @eventSystem AND Code = @eventCode )
    END CATCH
    -- proceed with @eventDefinitionId value