May 30, 2014 at 8:50 am
What is the "Best way" to lock or block access to a table that is being refreshed?
I do this all the time in stored procedure and SSIS packages. I truncate a table and then load it with fresh data. I assume this happens so fast that is is not an issues but should I be locking the table first?
Thanks
Dave
May 30, 2014 at 9:00 am
Fin3125 (5/30/2014)
What is the "Best way" to lock or block access to a table that is being refreshed?I do this all the time in stored procedure and SSIS packages. I truncate a table and then load it with fresh data. I assume this happens so fast that is is not an issues but should I be locking the table first?
Thanks
Dave
There is an implicit transaction running here. During the truncate there is a transaction and then again when you are inserting data. If you want to control this with more granularity you could put your code inside of your own transaction. Of course this doesn't prevent somebody from reading the table using the dreaded NOLOCK hint. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 9:21 am
wow.. I never thought of that. There are a lot of queries reading these tables using the Nolock hint. I guess I have no choice to make sure they wait for the load in another way.
May 30, 2014 at 9:33 am
Fin3125 (5/30/2014)
wow.. I never thought of that. There are a lot of queries reading these tables using the Nolock hint. I guess I have no choice to make sure they wait for the load in another way.
You could always rename the table. That would cause your other processes to raise an exception but it would prevent reading the table.
Are you aware of all the things NOLOCK brings to the table? Is missing and/or duplicate data acceptable? I cringe when I see that hint. I triple cringe when I see people say they have "a lot of queries reading these tables using the Nolock hint". :w00t:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 10:02 am
Yes I think so but didn't put two and two together. The table is loaded in the early morning. After that the other systems only read it. My worry was if I need to re-run the job later that day for some reason. I like the table renaming idea.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply