none
SP_UpdateTaskGroupMaxMinCores generates a lot of deadlocks against HPCScheduler database RRS feed

  • Question

  • Hi all, 

    SQL2014 RTM/HPC 2012R2

    SQL database HPCScheduler reporting a lot of deadlocks on SP_UpdateTaskGroupMaxMinCores procedure.

    SPID #1 runs:

    exec SP_UpdateTaskGroupMaxMinCores 3018, 85939, 0, 1;

    SPID #2 runs:

    exec SP_UpdateTaskGroupMaxMinCores 3018, 85939, 1, 0;
    exec SP_UpdateTaskGroupMaxMinNodes 3018, 85939, 1, 0;
    exec SP_UpdateTaskGroupMaxMinSockets 3018, 85939, 1, 0;

    As you can see, 1 spid is updating same row with different values, which is causing the conflict.

    Stored procedure code is pretty simple:

    CREATE PROCEDURE SP_UpdateTaskGroupMaxMinCores(
        @parentJobId        int,
        @taskgroupId        int,
        @value              int,
        @origvalue          int
        )
    AS
    BEGIN
        UPDATE TaskGroup SET 
            MaxMinCores= 
            CASE WHEN MaxMinCores < @value THEN @value 
            ELSE CASE WHEN MaxMinCores = @origValue THEN
                (                             
                        SELECT COALESCE( MAX(TaskDetail.MinCores),0)
                         -- We can put it here because this query is executed every 30 seconds
                         -- And if we miss the task state change once we can get it the next time anyway
                        FROM Task WITH (NOLOCK) 
                        INNER JOIN TaskDetail
                        ON TaskDetail.RecordId = Task.RecordId
                        WHERE TaskDetail.GroupId = @taskgroupId
                        AND Task.ParentJobId = @parentJobId
                        AND NOT (Task.State in (0x80, 0x100, 0x200)) 
                        AND ( Task.InstanceId = 0 or Task.InstanceId = -1)
                 )       
                ELSE MaxMinCores END
            END
        WHERE ID=@taskgroupId
    END


    In [HPCScheduler].[dbo].[TaskGroup] table found the job belonging to .root and IsDefault flag is 1.

    Would you know if there are any default system jobs that could be running these procedures and how can I resolve the deadlocking issue?

    Any info appreciated, at this stage we're getting circa 50 deadlocks a day.

    Thanks, 

    Inga

    Tuesday, January 26, 2016 4:48 PM

All replies

  • Hi Inga,

    Thanks for reporting this issue.

    HPC has built in deadlock detection mechanism which will retry the transaction once deadlock happened.

    Did you see any jobs affected by the deadlock issue? What's the symptom?

    Thanks,

    Evan

    Wednesday, January 27, 2016 1:12 PM