SQL Schema modification lock issues on tables

Hi
We have a setup where we load data for a given day into a staging table and then perform a partition switch to get the data into the corresponding final table. The final table needs to hold a ‘sliding window’ history of 5 days of data and we have a partition switch stored procedure that manages this for us. This process is working nicely but we’re now experiencing deadlocking issues. From SQL Server profiler I can see that a SCH-M lock is taken out on the table when our partition switch stored procedure runs, which makes sense. The deadlock occurs when other processes are trying to read from some reference tables (currency,  country etc.) which are referenced via foreign key constraints on the table on which the partition switch is being done. So what it looks like to me is that the schema modification lock also affects and tables referenced by foreign keys on that table. After some time on Google I haven’t been able to confirm that this is in fact the case.

Can someone perhaps explain what locking would happen in the above scenario?

I read on Technet the SCH-M lock prevents any concurrent access to the table so even using the NOLOCK hint when reading from the related reference tables won’t work.
Does anyone have any ideas as to how I can get around this problem?

Thanks!
LVL 4
CraigLazarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
There are various workarounds but not the real solution that will make both thing happen simulaneoulsy.
1. You can set the maxdop value to 1.
2. You can change the execution of delay if it is a scheduled one.

The doubt you have is correct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CraigLazarAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.