SQL Server NOLOCK Hint & other poor ideas.

Frequently I see production code, created by professional development teams, peppered with NOLOCK & other TSQL Hints.

While totally understandable, as it is a common recommendation by many internet posts & often found in their sample code, this is a really bad practice.
It often results in very obscure, hard to reproduce bugs and can cause data to get corrupted.

Thought 1: TSQL HINTs in General

As a general rule TSQL HINTs should only be used as a last resort. Both those responsible for the ANSI SQL standard & the Microsoft SQL Development Team have given a lot of thought about what is the safest, most desirable default values for transactions & query execution. It would seem logical that your default Coding Standard should be to follow those defaults and not some code snippet you found on the internet.

Recommendation 1: Do not use any HINTs until your testing proves that you have an issue that can’t be solved any other way than by using a HINT. 

• Be aware that any testing you do will be unique to that Specific Edition, Specific Version & Service Pack. The optimiser is constantly being enhanced, in a future release it might change to better handle whatever it is that you are hinting.
• On more than one occasion I’ve had customers request a switch to turn off the Optimiser HINTs generated by some s/w package they’ve purchased. They’ve discovered that the ISV’s queries actually run much faster without the hints, perhaps the hints were useful 10 years ago is say, SQL 6.5, but are now a hindrance in a later release of SQL.
• Often you can rewrite your query &/or modify schema to get a much better result.

Recommendation 2: If you use a HINT, prove it via testing & document it.

If you use a HINT, document why. I’d expect at least :-

1. The issue or performance problem you encountered, How it worked without HINTs & how it worked with the Hint.
2. The Version, Edition, Patch Level you tested it on. (Enterprise Edition runs many more operations in parallel than Standard, this can make a difference)
3. Also nice if your app provides a configuration option to remove it your hints OR maybe give the customer the ability to edit your stored proc to remove it.

More than once I’ve looked at a Schema or TSQL Query & thought “Either this designer was brilliant & had such foresight to anticipate some obscure issue I’ve not even considered OR they have no clue about databases”. Unfortunately it is almost impossible to know with total certainty. So please tell the poor mongrels who maintain your code, what you were thinking. Preferably put the comments into the TSQL Code, as nearly any other place the documentation will become separated from the code.

Thought 2: TSQL NOLOCK / READ UNCOMMITTED HINT

This Hint is much more dangerous than its name suggests. And that it why most people who don’t understand the problem, recommend it. It creates “incredibly hard to reproduce” bugs. The type that often destroy your end-users confidence in your product & your company.

But it does make nasty warnings/errors go away without the need to really fix the problem. Similar to short sighted “tips” from other disciplines are :-

1. Turning off the Compiler Warnings about Implicit Type conversion can speed development. NB: Comparing an INT to a SHORT is classic cause of an infinite loop.
2. Turning up your car stereo can drown out the grinding noise of an engine with no oil.
3. Turning up the volume of your MP3 player can save you from the terror & desperate leap to safety when you walk onto the road with your back to the oncoming traffic.
4. Folding your arms on the “seatback in front of you” will make a big difference when you fly into the ground upside down at 900 kph. OK there may be some merit to this one, stops you annoying the person next to you.

What many people think NOLOCK is doing

Most people think the NOLOCK hint just reads rows & doesn’t have to wait till others have committed their updates or selects. If someone is updating, that is OK. If they’ve changed a value then 99.999% of the time they will commit, so it’s OK to read it before they commit. If they haven’t changed the record yet then it saves me waiting, its like my transaction happened before theirs did.

The Problem

The issue is that transactions do more than just update the row. Often they require an index to be updated OR they run out of space on the data page. This may require new pages to be allocated & existing rows on that page to be moved, called a PageSplit. It is possible for your select to completely miss a number of rows &/or count other rows twice. This has been well documented by a number of highly reputable sources, including the SQL Server Development team. So I wont repeat it here. For the details visit the links below.

SQL CAT: Previously committed rows might be missed if NOLOCK hint is used

Tony Rogerson's post, Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

Itzik Ben-Gan's SQL Server Mag Article on Allocation Order scans

NOLOCK Optimizer Hint May Cause Transient Corruption Errors in the SQL Server Error Log

Excuses

• We always do that / It was just in the sample code I read. => Now you know better.
• But I keep getting these Deadlock messages. => Solve the problem (see below)  don’t just ignore the warnings.
• I only use it on small tables which heaps of people read & it rarely changes. => Shared Locks don’t block other shared locks. Maybe you could have your Updates change 1 row per transaction.
  

The solution

Unfortunately there is no 1 line recommendation I can give you that will make your Deadlock &/or other perf issues go away. People have written entire books on the subject. But a few things to consider are:-

• Use Stored Procedures for everything. They provide a level of encapsulation or code isolation that will allow someone to change your schema & fix perf issues without needing to understand your code. Note: Creating 3 stored procs for every table; p_Insert, p_Update & p_Delete is OK but not really what I’m talking about. I prefer procs that do a unit of work that may involve multiple tables, eg: p_CreateNewCustomer() or p_NewOrder()
• Improve your schema
  Keep your rows short – avoid adding additional audit columns (ie: LastUpdatedBy) if there is no business plan to read them.
  Ensure your Many to Many tables use both foreign keys as a the unique composite key (Primary key), ie: an Identity Column as a Primary Key is not appropriate for these tables.
• Keep your transactions short
  Avoid having a huge Selects sandwiched between two updates in the same transaction. (this is like Loop optimisation by taking the invariant statements out of the loop).
  Avoid SELECTing a row & then changing it, eg: Instead of SELECT VALUE, UPDATE VALUE = OLD VALUE+1. Just Update the “Original value +1” as a single statement.
• Avoid using cursors.
  Cursors are not bad but often you can find a much more efficient way to complete a task. The optimiser is forced to do “Row by Row” changes which prevents most forms of optimisation, parallelism & multi-buffered operations.
• Try to acquire locks in the same sequence for all your transactions. eg: have all your stored procedures Lock the InvoiceHeader before the InvoiceDetails. Don’t write half one way & the other half the opposite.
• Use Snapshot Isolation.
  But test it, your TEMPDB config might need attention.
• Think about using a READPAST hint. If a row or page is locked you just don’t read it. Often that is OK as those “Rows” were being used anyway. eg: in any ticketing system; Airline seats, Theatre, Hotel, where you have many customers competing for finite resources, you often know the total resources anyway. In one system we drew all the seats in a concert hall & coloured them orange. Then Selected all the seats on that night with a READPAST hint. Those that were RESERVED or AVAILABLE were returned. We coloured them appropriately. Any seat not returned was possibly being locked by another reservations clerk, so remained in Orange. If they hit refresh again, these seats typically turned Red (booked)
• SQL Broker
  Use SQL Service Broker to break your transactions up into an async component. These smaller transactions might not block for as long OR you may be able to block the queue in heavy load periods & have that part of the transaction processed in the evening.

Update to this Post

1. If NOLOCK is so bad why have it at all?

    Because sometimes accuracy is not so important. If you are plotting something on a line chart & the line is out by 1-2% you possibly wouldn’t notice, what most people would look at is the trend. Similarly, if you are putting a number on a screen & refreshing it periodically (ie: %CPU or “Orders received in the past 10 mins”, most people will only look at the 1st 2-3 significant digits. eg: If a traffic counter measured 154,218 cars went thru an intersection between 6-7am & 572,621 from 8-9am. Your brain possibly rounds it to ~150K & ~575K respectively. Unless you are charging a toll for each car, the thing most interesting is “how does that compare to other time periods”

2. How does it cause data corruption?

   Mostly when you use the SELECT .. WITH NOLOCK as the basis of some other Update or Insert. Some rows will miss out on whatever changes you thought should be applied to them.

I hope this assists you to improve you design Or at least avoid creating issues that will be impossible to debug in production.

All feedback & comments welcome.

Dave.

Thought for the day, (which sums up my feelings about Cursors & Hints): There is no Right or Wrong, only outcome.