December 8, 2015 at 1:03 pm
Today I had an issue with a SSIS package that loads from a staging database to a target database (same instance). I am using the fast load option and had checked the Table Lock box. When running this package, it got into a deadlock (not detected by SQL Server). SSIS launched two sessions. The first one (the read from staging) was hung on ASYNC NETWORK IO (???????). The second one (the writer) was waiting for LOCK_M_X. The reader and writer reference different databases, so there is no possibility that the the reader was blocking the writer on the target table.
After some head-scratching, talking to myself, foaming at the mouth etc. I unchecked the Table Lock option. Voila! No locks, full speed operation.
Although I'm glad I got it to work, I'm stumped trying to understand the root cause.
1. Why would the table lock option cause this kind of virtual deadlock? (no other session is accessing the destination table at the time)
2. Why is the reader waiting on net i/o? Both reader and writer are accessing the same instance.
PS, it shouldn't matter I think, but the source and destination tables have the same schema and table names. Only the database names differ.
Gerald Britton, Pluralsight courses
December 9, 2015 at 11:03 am
Maybe try setting the connection manager property RetainSameConnection to true.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 9, 2015 at 12:52 pm
That's interesting!
In this case, there are two connection managers -- one for each database. Would you suggest using one connection manager and three-part naming on the SQL statements in the data flow?
Gerald Britton, Pluralsight courses
December 10, 2015 at 7:12 am
g.britton (12/9/2015)
That's interesting!In this case, there are two connection managers -- one for each database. Would you suggest using one connection manager and three-part naming on the SQL statements in the data flow?
No, not really.
I'd expect no locking, since you have
* two different connection managers
* both tables in different databases
So it's a bit of a mystery where there is deadlocking. Maybe the fast load is attempting parallel inserts in the table, which fails because of the table lock?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2015 at 7:53 am
Koen Verbeeck (12/10/2015)
g.britton (12/9/2015)
That's interesting!In this case, there are two connection managers -- one for each database. Would you suggest using one connection manager and three-part naming on the SQL statements in the data flow?
No, not really.
I'd expect no locking, since you have
* two different connection managers
* both tables in different databases
So it's a bit of a mystery where there is deadlocking. Maybe the fast load is attempting parallel inserts in the table, which fails because of the table lock?
well, that would a bug, wouldn't it?
Gerald Britton, Pluralsight courses
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply