September 3, 2010 at 7:34 am
Hi there,
I have a strange problem that I reproduce only on one of my test servers but on none of the others. I have 2 SSIS packages that execute concurrently at times. In the first one, a query is performed that joins against a particular table, table A. This is done in a Data Flow Task flagged with a Required Transaction Attribute.
In the other package, one of Table A's extended property is updated. This is done in a container flagged as Supported for Transaction Attributes.
Package #1 starts and package #2 starts right after, I end up with Package #1 having a Schema Stability Lock on that table, while Package #2 triggers a Schema Modification Lock on the same table. What's strange is that on my other environments, I don't see the Schema Stability Lock on the table, only the Modification Lock from the second package.
So at that time, Package #2 waits for Package #1, but Package #1 is waiting for Package #2 to complete the Modification. So I end up with a DTC deadlock where Package #1 is blocked by Package #2 (spid match in SQL) and Package #2 is blocked by Package #1 (spid -2 in SQL, which is the DTC transaction for Package #1). The two packages end up in deadlock and because it's a DTC deadlock, SQL does not terminate any connections nor does it detect a SQL deadlock and resolves it.
Any ideas as to how to either reproduce this issue at all times on a different setup or avoid it altogether would be very appreciated.
Thanks,
Greg
September 3, 2010 at 7:43 am
Here's my 3 cents worth.
Assuming you cannot find a way to prevent the blocking, here's a possible workaround.
One option is for each package to set a flag when they start and reset when they're finished. Just before settting this flag, however, each package would check if the other package's flag set already set. If it is, wait until the flag is reset before continuing.
Assuming you're running each package from SQL Server Agent, you might be able to do this with what's already there without having to setup "flags".
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 3, 2010 at 7:56 am
What is package 2 doing that requires a Sch-M lock? Those are very restrictive locks
All queries require and take a Sch-S lock while they're running, to prevent the table from being modified while a query against it is executing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2010 at 3:20 pm
Hi,
Thanks for the answers. What I was doing was that at the end of my package I call sp_updateextendedproperty to update the table's last ETL load. I just liked having that information packaged in the table's metadata. But it seems that it's causing a Sch-M lock (I can understand that though). Thing is that I could not always reproduce it by executing the packages manually, there's some sort of timing issue, even with breakpoints, or some sort of optimizer decision regarding the duration of the Sch-S/M locks (I tried to flush the server's cache to reproduce it more easily but without success).
In any case, my workaround for now is to store that metadata in a separate table. This way I don't have this locking issue. I just had never encountered that kind of issue (but I don't update metadata frequently neither mind you...).
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply