SSIS Fails on Update Task - Deadlock

Jen 61 Reputation points
2021-08-02T16:53:33.277+00:00

My SSIS fails most of the time with the following error; words sometimes without any issues.
How do I get it work each time without errors?

Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  8:51:11 AM
Error: 2021-07-29 09:08:51.01
   Code: 0xC002F210
   Source: Update ProductTable Execute SQL Task
   Description: Executing the query "
SET TEXTSIZE 0 
DECLARE @PK char(50), @date1 date..." failed with the following error: "Transaction (Process ID 62) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  8:51:11 AM
Finished: 9:08:51 AM
Elapsed:  1059.86 seconds
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
{count} votes

Accepted answer
  1. Cris Zhan-MSFT 6,606 Reputation points
    2021-08-03T08:43:06.33+00:00

    Hi,

    >Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft......

    Please note that there are few patches/updates installed on this SQL Server instance. It is recommended to apply the latest updates (SP2 and CU 17 for SP2) for this instance to avoid any problems that have been fixed.

    I saw a fix for a deadlock error when running SSIS packages in parallel.
    https://support.microsoft.com/en-us/topic/kb4338773-fix-deadlock-errors-when-you-run-an-ssis-package-in-parallel-in-sql-server-d9a7f7f1-d605-6b57-8b64-7b9ca20b0892

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Michael Taylor 49,071 Reputation points
    2021-08-02T17:26:22.173+00:00

    This is a standard SQL error and may or may not be because of SSIS. The underlying problem is that your SSIS package is attempting to update the ProductTable table. However while it is running somebody else is trying to do an update to the same table. Depending upon your locking level that you're using in your query (and theirs) they could also be attempting to read the data. Irrelevant you are waiting on them and they cannot continue without you releasing your lock so you both deadlock. In this case SQL gets to determine who loses and, in this case, it was your SSIS process. The error is telling you that your query failed because you were randomly chosen to lose in this particular scenario. The next time you run the package you may have no locking issues or you may have locking issues but your process wins. It is very much temporal.

    The correct approach here is to ensure that you are not accidentally doing something crazy like running a parallel loop in SSIS trying to update the same table's rows at the same time. Additionally ensure you are not trying to update the table while some other process (inside or outside SSIS) might be updating the same table. Given that it is deadlocking long enough for SQL to notice that tends to indicate you have a lock on the table for longer than you probably should. This could be a sign of a transaction that is taking too long or perhaps a query that is just slow after you've started updating the table. Your DBA should be able to work with you to narrow this scenario down.

    To fix it you'll need to ensure you (and whoever is also deadlocking) are not fighting for the same rows (or the entire table if applicable) at the same time. This could be something as simple as a scheduled job change or perhaps you need to look at your locking strategy, transaction behavior and other things related to updating this table.

    0 comments No comments

  2. Jen 61 Reputation points
    2021-08-02T17:36:31.157+00:00

    No other processes are using/updating the same table; here is my full update query:

    update projectlog set CurrentProcess = 'Getting latest product data...'
    where Project = 'NewProducts'
    go

    SET TEXTSIZE 0
    DECLARE @PK char(50), @date1 datetime, @date2 datetime , @date3 datetime ,@date4 datetime , @unitpk char(50) ,@date5 datetime , @result char(50)
    DECLARE @PK_OLD char(50)
    DECLARE def CURSOR FOR
    select * from NewProducts order by ProductID , LastUpdateDate desc
    OPEN def
    SET @PK_OLD = ''
    FETCH NEXT FROM def INTO @PK , @date1 , @date2 , @date3 ,@date4 , @unitpk ,@date5 , @result
    WHILE @@Fetch _STATUS =0
    BEGIN
    IF (@PK <> @PK_OLD)
    BEGIN
    UPDATE AllNewProducts SET LastAnnualUpdate = @date1 , LastApproveddate = @date2 , NextApprovalDate = @date3 ,
    InitialApprovalDate = @date4 , UnitPK = @unitpk , Approvaldate = @date5 , Approvalresults = @result where ApprovalID = @PK
    SET @PK_OLD = @PK
    END
    FETCH NEXT FROM def INTO @PK , @date1 , @date2 , @date3 ,@date4 , @unitpk ,@date5 , @result
    END
    CLOSE def
    DEALLOCATE def


  3. Jen 61 Reputation points
    2021-08-02T20:06:49.03+00:00

    It is SQL Server 2016

    Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)