CraigLazar
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER