• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

SQL Schema modification lock issues on tables

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?

1 Solution
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.
CraigLazarAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now