Deadlock during SSIS with resources that should have been freed up by successful Execute-SQL task

Question

Deadlock during SSIS with resources that should have been freed up by successful Execute-SQL task

duane_wynia

SSC Enthusiast

Points: 131
More actions
May 18, 2018 at 11:33 am

#411201

I am running into an intermittent deadlock situation with a resource that according to the SSIS Precedence constraints should be freed up (finished successfully) but according to the log is not. Any ideas?

Deadlock during SSIS with resources that should have been freed up by successful Execute-SQL task (the executed stored procedure had only started, not stopped, before it moved on to the next task in the sequence container).

SSIS setup:

Sequence Container:     Process Fact Table
FailParentOnFailure: True
MaximumErrorCount: 1
Transactions:IsolationLevel: Serializable (default)
Transactions:TransactionOption: Supported
Execute-SQL-Task one (one causing the problem):
(properties same as Sequence Container)
Executes a stored procedure with four input parameters – no output parameters. This is executing dynamic SQL (ALTER INDEX) which disables two non-clustered indexes in the table about to be loaded. According to the logging, this Task starts (and doesn’t stop until later) and then it continues to the next sequence. Since this is an Execute SQL task, I don’t think it should be returning success to the Precedence Constraint or continuing outside of this task.
Constraint between this and next task: Precedence Constraint option: Constraint, Value: Success.
Execute-SQL-Task two:
(properties same as Sequence Container)
Executes a stored procedure with six input parameters – no output parameters. This SP is executing dynamic SQL that drops Foreign Key constraints for the table about to be loaded.
Constraint between this and next task: Precedence Constraint option: Constraint, Value: Success.
Execute Package task one:
This package executes several Execute_SQL tasks in sequence. It is on the second Execute-SQL (Get initial row count) task inside this executed package when the deadlock occurs (accessing sys.partitions and sys.tables views for the table to be loaded which are still in use by the first Execute-SQL task above).
Log:
·         Process Fact Table (sequence container)                2018-05-16 07:06:20.0016429 -05:00
·         Disable Fact Table NC indexes – start*                   2018-05-16 07:06:20.0016429 -05:00
·         Disable Fact Table FC Constraints – start **           2018-05-16 07:06:20.0016429 -05:00
·         Disable Fact Table FC Constraints – finished**    2018-05-16 07:06:20.3454021 -05:00
·         Execute Fact load package                                           2018-05-16 07:06:20.4108924 -05:00
o   Process Audit Setup                                        2018-05-16 07:06:20.4108924 -05:00
o   Process Get Initial Row Count                    2018-05-16 07:06:20.4108924 -05:00

Disable Fact Table FC Constraints – finished*                     2018-05-16 07:06:22.7702158 -05:00
My expected behavior of this SSIS setup would be that the Disable Fact Table NC Indexes would both start and finish prior to the task container setting the constraint value to Success (green). Obviously, this isn’t happening and I am at a loss why the Execute SQL task would perform in this manner. Does anyone have some insight as to why SSIS would do this and how to prevent it without putting in a WAITFOR DELAY hack into the stored procedure?

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

How about at least using SET NOCOUNT ON to prevent additional result sets from occurring that might not be doing you any favors? The other thing to check is what constitutes success for that element in your package. Not sure there's an easy way to control that...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

duane_wynia SSC Enthusiast Points: 131 More actions · Answer 2

Steve, thanks for your reply - SET NOCOUNT ON; is already enabled in all my stored procedures.

Martin Schoombee SSCoach Points: 19116 More actions · Answer 3

Interesting scenario, and I'm wondering whether there isn't some background task that's still trying to complete when your second execute SQL task begins. Here's a few initial thoughts in troubleshooting:

Run a trace to see what's really running on the SQL Server (and when). You may see another background task (transaction) pop up as a result of the disabling of the indexes.
Instead of disabling the indexes, drop them and see if you encounter the same deadlock.
Set the DelayValidation property on all your tasks and see if that changes the reported start/end times of the tasks in the log. Pre or Post Validation of tasks and/or SQL statements may be affecting what you see in the logs.
Are you querying sys.tables and sys.partitions directly, or is this just where the deadlock is occurring? Have you tried running those with nolock?

That's what I have for now. Would be interesting to hear of the outcome of the above.

duane_wynia SSC Enthusiast Points: 131 More actions · Answer 4

I fixed this issue by creating two different connection managers where I originally was using a single one for all the tasks described in the post, I created One straight ADO.NET:System.Data.SqlClient.SqlConnection a second ADO.NET:System.Data.SqlClient.SqlConnection_Retain same connection. I used the retain same connection manager for all the tasks in the sequence container that the precedence constraint was reporting success too early. For the following sequence container, I used the straight ADO.Net connection manager. This forced Execute-SQL-Task one to completely finish before executing Execute-SQL-Task two in my description. It has worked properly since.