musium musium - 3 months ago 27
SQL Question

SQL Server 2016 - stored procedure lock when creating not existing records

I’ve the following tables in my database:

------------------------------------------
| EventDefinitions |
------------------------------------------
| EventDefinitionId: uniqueidentifier |
| Code: varchar(63) |
| EventSystem: int |
------------------------------------------

------------------------------------------
| Event |
------------------------------------------
| EventId: uniqueidentifier |
| EventDateTime: datetime2 |
| EventDefinitionId: uniqueidentifier |
------------------------------------------


EventDefinitions (1) <- Event (n)

EventDefinition has a unique constraint on EventSystem and Code.

I’m working on a stored procedure which should create an Event record and map it to the correct EventDefinition record, based on the specified Code and EventSystem.

If no matching EventDefinition exists, the procedure should create a new EventDefinition.

The procedure will be executed by multiple applications concurrently.

My goals:


  • The procedure must be parallel executable.

    With as few locks as possible, so that multiple Event records can be written simultaneously.

  • When a EventDefinition does not exist, it should be created by only one process.

    The procedure should create some kind of lock to make sure that the EventDefinition record with the specified Code and EventSystem is only created once (otherwise the DB will throw a unique constrain violation exception).
    The other processes should map the Event record to the newly created EventDefinition record.



Here is what I’ve tried so far:

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


The problem, my current SQL code generates deadlocks when the procedure is executed parallel.

Answer

Your code is overcomplicated. Sql server does the most checks and blocks itself. Just try/catch insert and if it fails due to UNIQUE constraint violation, reread EventDefinitions. Kind of

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