question

Jen-6712 avatar image
0 Votes"
Jen-6712 asked Criszhan-msft edited

SSIS Fails on Update Task - Deadlock

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-generalsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Jen-6712,

It seems that your question has nothing to do with SSIS. It is pure T-SQL.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jen-6712 avatar image
0 Votes"
Jen-6712 answered YitzhakKhabinsky-0887 commented

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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Jen-6712,

I added a comment just below you initial question.
Please update your initail question and add ##1-4.

0 Votes 0 ·
Jen-6712 avatar image
0 Votes"
Jen-6712 answered RyanAbbey-0701 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQL believes there is another process. Therefore somebody is creating a connection and locking your table, hence your error. My gut instinct is that your query is running in parallel within your SSIS package (without knowing where this is actually being called) and you're using a cursor which is going to really make it easy to deadlock. I'm not a cursor expert but I'm pretty sure it'll end up locking your table.

The other thing that registers a red flag to me is your use of select * given that SQL doesn't guarantee ordering. So I also wonder if your select isn't always selecting the first column you expect so your @PK value is wrong during some runs causing an error.

I would recommend you run your SSIS package while your DBA monitors the tables so you can see the deadlock and get the query(ies) that are causing it.

0 Votes 0 ·

@Jen-6712,

When I saw SET TEXTSIZE 0, and the cursors, it reminded me Oracle right away.

What is your underlying database?

0 Votes 0 ·

Yikes! How big a table are you expecting to rifle through? Anything more than a few rows and you may find incredibly slow.

Something along the lines of...

 with x as (
    select PK , date1 , date2 , date3 ,date4 , unitpk ,date5 , result, row_number() over (partition by PK order by ProductId, LastUpdateDate desc) as rn
    from newproducts
 )
 update a
 set a.date1 = x.date1, a.date2 = x.date2 .... etc etc
 from a, x
 where a.approvalid = x.pk and x.rn = 1
0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.