Wednesday, June 9, 2010

SQL Query Hint With (NOLOCK)

SQL is an atomic transactional database by default, which means that each change either finishes completely or doesn't change anything.


This forces SQL server to use locks to perform its tasks, but what if we don't have to have the most updated data? or we don't care about records that are being modified right now and we're willing to accept some errors?


Example? 
Some statistics, it might change right now, we might not even care if lets say a user logged in once today or not at all, perhaps a refresh is being done every 5 minutes to update the UI and if the user won't show up on the screen now it will show up later?


But what am I saying?


If for every write to the statistics the table is being locked, but you need to get the current statistics without being delayed every update, what should you do? well, you can ask SQL to ignore the locks and give you whatever it has right now, consistent or not, you don't care as long as its close enough and fast.


For that we have nolock query hint.


select ... from statistics with (nolock)



No comments:

Post a Comment