Deadlocking SSISDB SQL 2014 Multiple SSIS Packages executing at same time

  • Has anyone come across this issue before? When googling I'm seeing issues with SQL server 2012 and deadlocking in the SSISDB which were resolved in a service pack but not seeing many issues with SQL 2014.

    I have two packages stored in the Integration catalog which execute at the same time.This is causing a deadlock on [internal].[execution_parameter_values] in the SSISDB

    The work around is to set one to execute slightly later but was just wondering if anyone had seen this issue in 2014 or if there was something about how I have set up my SSIS projects/packages which might be causing the issue.

    Deadlock xml is below

    <deadlock>

    <victim-list>

    <victimProcess id="process1bc8da4e8" />

    </victim-list>

    <process-list>

    <process id="process1bc8da4e8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594042843136 (267401a7be15)" waittime="438" ownerId="49357173" transactionname="user_transaction" lasttranstarted="2016-06-08T16:00:03.460" XDES="0xc5a183b0" lockMode="X" schedulerid="4" kpid="22012" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-06-08T16:00:03.390" lastbatchcompleted="2016-06-08T16:00:03.387" lastattention="1900-01-01T00:00:00.387" clientapp=".Net SqlClient Data Provider" hostname="CAHQX-PRDSQL01" hostpid="2320" loginname="S-1-9-3-2870711563-1196531925-3037930163-1186165430" isolationlevel="serializable (4)" xactid="49357173" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    <executionStack>

    <frame procname="SSISDB.catalog.set_execution_parameter_value" line="235" stmtstart="15918" stmtend="16512" sqlhandle="0x0300070058e0ef6aaefd350180a4000001000000000000000000000000000000000000000000000000000000">

    UPDATE [internal].[execution_parameter_values]

    SET [runtime_override] = 1,

    [value_set] = 1,

    [base_data_type] = @parameter_type,

    [parameter_value] = @parameter_value

    WHERE [execution_parameter_id] = @execution_parameter_i </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 7 Object Id = 1794105432] </inputbuf>

    </process>

    <process id="process2da3ad468" taskpriority="0" logused="0" waitresource="KEY: 7:72057594042843136 (f6d22cb28365)" waittime="438" ownerId="49357169" transactionname="user_transaction" lasttranstarted="2016-06-08T16:00:03.457" XDES="0x311a34d10" lockMode="X" schedulerid="4" kpid="24356" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-06-08T16:00:03.390" lastbatchcompleted="2016-06-08T16:00:03.347" lastattention="1900-01-01T00:00:00.347" clientapp=".Net SqlClient Data Provider" hostname="CAHQX-PRDSQL01" hostpid="21396" loginname="S-1-9-3-2870711563-1196531925-3037930163-1186165430" isolationlevel="serializable (4)" xactid="49357169" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    <executionStack>

    <frame procname="SSISDB.catalog.set_execution_parameter_value" line="235" stmtstart="15918" stmtend="16512" sqlhandle="0x0300070058e0ef6aaefd350180a4000001000000000000000000000000000000000000000000000000000000">

    UPDATE [internal].[execution_parameter_values]

    SET [runtime_override] = 1,

    [value_set] = 1,

    [base_data_type] = @parameter_type,

    [parameter_value] = @parameter_value

    WHERE [execution_parameter_id] = @execution_parameter_i </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 7 Object Id = 1794105432] </inputbuf>

    </process>

    </process-list>

    <resource-list>

    <keylock hobtid="72057594042843136" dbid="7" objectname="SSISDB.internal.execution_parameter_values" indexname="PK_Execution_Parameter_value" id="lock1ebdc9680" mode="RangeS-S" associatedObjectId="72057594042843136">

    <owner-list>

    <owner id="process2da3ad468" mode="RangeS-S" />

    </owner-list>

    <waiter-list>

    <waiter id="process1bc8da4e8" mode="X" requestType="convert" />

    </waiter-list>

    </keylock>

    <keylock hobtid="72057594042843136" dbid="7" objectname="SSISDB.internal.execution_parameter_values" indexname="PK_Execution_Parameter_value" id="lockee56d300" mode="RangeS-S" associatedObjectId="72057594042843136">

    <owner-list>

    <owner id="process1bc8da4e8" mode="RangeS-S" />

    </owner-list>

    <waiter-list>

    <waiter id="process2da3ad468" mode="X" requestType="convert" />

    </waiter-list>

    </keylock>

    </resource-list>

    </deadlock>

  • we are having the same issue any fix for this. Please let us know

  • Hi,

    I noticed that index maintenance wasn't being carried out on the SSISDB so I updated the stats and rebuilt indexes and this appeared to fix the problem.

    Note that I still put a small gap between the execution time of the two packages just to be on the safe side.

  • Thank you for the details, we have pretty new deployment in production, with the same setup it is working fine in UAT and Development.

  • I'm seeing the same issue. We have one SSIS package that we call with different parameters for different client data.

    I'm attempting to have the SQL Jobs kick off at the exact same time and process the data in parallel but am getting these exact same deadlocks. Ideally I'd like all the SQL Jobs to reference the same schedule so that I can edit one schedule and adjust all the SQL jobs at once. Spacing out the jobs would negate that.

    Is this just a limitation of SSIS and the catalog?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply