Consider the following scenario:
-
You install Microsoft SQL Server 2012.
-
You schedule a Microsoft SQL Server Integration Services (SSIS) package to start by using the SSIS.catalog.create_execution stored procedure.
-
You call the SSIS package again or schedule a second SSIS package to start at the same time by using the SSIS.catalog.create_execution stored procedure.
In this scenario, you may experience a deadlock condition. An error that resembles the following is logged in the SQL Server Agent Job History:
Description: Transaction (Process ID spid) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Notes
-
This error may also be displayed in the Query window. However, the error may not be displayed in the SSIS logs or in the Integration Services dashboard report in Management Studio.
-
This issue does not occur when you use Dtexec.exe to start SSIS packages that are stored in the file system or in the MSDB database.
Symptoms
This issue occurs because of a problem in the [SSISDB].[catalog].[create_execution] stored procedure. When multiple calls are made to the [SSISDB].[catalog].[create_execution] stored procedure at the same time, the requested locks may form a deadlock.
Cause
Service pack information for SQL Server 2012
To resolve this problem, obtain the latest service pack for SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012
Resolution
To work around this issue, stagger the timing when you run SSIS packages so that the calls to the [SSISDB].[catalog].[create_execution] stored procedure do not overlap.
Workaround
The issue that is described in the "Symptoms" section can occur when one or more of the following conditions are true:
-
You schedule multiple SQL Server Agent jobs that run SSIS packages at the same time or at close time intervals.
-
You call multiple instances of the [SSISDB].[catalog].[create_execution] stored procedure to start SSIS packages at the same time or at close time intervals.
-
You start multiple SSIS packages at the same time or at close time intervals by using the SSIS API in a custom application.
Note Typically, the issue that is described in the "Symptoms" section occurs when two or more SSIS packages are started within a 100 millisecond (ms) time window. However, the number of packages and time interval varies, depending on computer performance.
The deadlock occurs when multiple sessions try to lock the following resources:
-
A key lock on the index of the SSIDB.internal.executions table
-
A schema modifications lock on a symmetric key. For example, a schema modifications lock on Class ID: symmetric_key_id=101(##MS_DatabaseMasterKey##)
More Information
For more information about the SSIDB.catalog.create_execution stored procedure, visit the following MSDN website:
http://msdn.microsoft.com/en-us/library/ff878034(SQL.110).aspx
