Skip to main content

Deals You Can't Miss

1 Year Subscription

NOLOCK in SQL Server ain't your friend

If you are in a team that is obsessed with NOLOCK hints to your SQL queries, because you think it is faster and an all weather reliable friend, this post is for you to help you understand why you should avoid it (almost always) for it is not your friend. 

NOLOCK in SQL Server is often abused as if it is the magical way to speed up read queries. It is unfortunate that teams forget that "there is NO Free Lunch". Every action is trading off one thing for the other and so when you take an action, be aware of what you are trading to give to get something.

The Exceptional Circumstances You Can Use NOLOCK

  • When you are querying live DB for some Reporting, where some bad data doesn't alter the overall aggregate numbers that you compute.
  • When you are using WITH (NOLOCK) for SELECT query in reporting database where data are already written and committed.
  • When you are querying a live production DB to take a peak into it and not use the result as a source of truth, knowing that it might have bad data in it.
Unravelling NOLOCK
  • NOLOCK is a Table Hint in T-SQL, that tells the Query Optimizer (which can generally pick the best optimization method without any hints being given to it) on what is the better way to execute the said query, as its author is an expert DB specialist who is fully aware that the data could well be bad and that it is alright.
  • NOLOCK is synonymous to READUNCOMMITTED isolation level. Reading uncommitted data implies that you are potentially reading data before it is being committed. 
  • In an OLTP database where there are a lot of transactions happening, the database is intelligent to consider INSERT, UPDATE and DELETE statements with higher priority over SELECT query. Write has higher preference over Read. Why bother changing this status quo of inherent intelligence?
  • In a highly transactional table NOLOCK hint yields you faster READs having UNCOMMITTED data that could potentially turn out to be bad if it is uncommitted.
  • When you are employing WITH(NOLOCK) hint, you are trading rare Dead-Lock occurrence possibility and with Bad Data. 
Conclusion

Always use the NOLOCK hint with great caution and don't be fooled into believing that the results are absolutely true.

My Popular Posts

Ten Commandments of Egoless Programming

We are nothing but the values we carry. All through my life thus far, I tried to influence people around me with the virtues I value. Thanks to some good reading habits I had inculcated, and the fortune of being in good community of peers and mentors alike, I managed to have read some real good books. This post is about the 10 commands of egoless programming in Weinberg's book. I shall explain the commandments based on my experience here. So very many decades ago, Gerald M. Weinberg authored  The Psychology of Computer Programming . In it, he listed The Ten Commandments of  Egoless Programming , which remains relevant even today for us as not just programmers but as team-members. Weinberg is regarded as a pioneer in taking a people-centric approach to computing, and his work endures as a good guide to intelligence, skill, teamwork, and problem-solving power of a developer. When they appear to inspire and instruct, we find that they can apply to just about every business area, and e

Should I buy refurbished laptop from Amazon?

This post is based on my experience with amazon.in and guess it to be true on all other platforms as well. At least you can check out and verify for these pointers before you make that decision to buy renewed/refurbished laptop on Amazon with your hard earned money. I see this question propping up in several forums and on many different occasions. In the recent past, I had my 5 year old dell laptop that gave up because its motherboard failed. One of the options that I had in my mind was to re-use the HDD and the 16GB DDR4 RAM of that old laptop in the one that I purchase next as secondary.  I had come to a conclusion that it is not worth buying a refurbished/renewed laptop at all. Why? For the following reasons, most of which I see as BIG #RedFlags: You got to remember that Amazon provides a platform for 3rd party sellers to sell their products as well. So in your search for refurbished laptops you wouldn’t want to choose some random 3rd party seller who Amazon doesn’t endorse. You cou

Multi-tenant Architectures

  Multi-tenancy Application Deployment Architecture could be modeled in 4 broad ways: Separate Apps & Separate Databases Shared Apps & Shared Databases Separate Apps & Shared Databases Shared Apps & Separate Databases There is no right or wrong here. It's about choice and consequence that you should consider taking into your business context and constraints. In this post I intend to jot down a some key points to keep in mind for each of these multi-tenant architecture. These are more of quick notes for my quick reference, a cheat-sheet of sorts when I have to make choices. And I guess this can come handy to you too in your wise decision making. Separate Apps & Separate Databases Easiest to implement from development and deployment stand-point. Just automate the deployment infrastructure for every tenant for quick set-up. Most expensive of all the models from infrastructure cost stand-point. Relatively longer deployment t