SSIS Fails on Update Task - Deadlock

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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)