What is my “go BIG or flame out” trick. It’s none other than NOLOCK. It’s flames on sports cars. It’s the turbo button. It is the SQL developer fairy dust and unicorns. In somewhat recent pop culture it’s Tiger blood and Adonis DNA. It’s the Charlie Sheen of TSQL performance tuning.
It sounds great and is easy to implement. Who wouldn’t want to use that? Sure it’s a little risky, but what’s development without a bit of danger. Isn’t that why we got into the DBA business? A craving for addrenaline fueled risk
NOLOCK is the lovely table hint that doesn’t put shared locks on your tables. Great! It doesn’t stop other transactions from modifying data. Perfect! It increases concurrecy. What could be better! Like other vices it can get addictive.
Also, like other vices there are down sides. Without those locks, users could be reading data that is in the middle of being updated. Can the application handle that? Will they be reading incomplete data? Is that data being deleted and never should have existed in the first place? That’s NOLOCK for you. We don’t know!
But who am I to tell you not to use NOLOCK. These people are all far smarter and more qualified than I.
- Is it a bad habit?
- Is it Strate up wrong?
- Is it just a Little problem?
- Is it a poor idea?
- Is it something developers should know?
- Is it just dirty?
All I can do is end with a slightly altered Charlie Sheen quote, “I’m on a drug. It’s called NOLOCK. It’s not available because if you try it you will die. Your face will melt off and your children will weep over your exploded body.”