All you NOLOCK lovers better read this!

I was perusing some of Microsoft's documentation updates last night after seeing their recent blog post about a deprecation notice that will cause both joy and sorrow throughout all the nether regions of our community (click to enlarge):

Copy/paste for posterity:

The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The READ UNCOMMITTED isolation level as a session setting also looks to be on the chopping block (enlarge):

Copy/paste for posterity:

The READ UNCOMMITTED isolation level has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI), which you should have already switched to years ago, even before you started peppering all of your T-SQL queries with magic, go-faster, NOLOCK hints on every table reference. From SQL Server 2022 onward, code that sets this isolation level will actually behave under RCSI semantics when it is enabled, and the former default (READ COMMITTED) when not. Avoid using this feature in new development work, and PLEASE fix applications that currently use this setting.

You can read more about this deprecation notice on Microsoft's blog:

UPDATE

Okay, okay, now that it's past noon, this was just a fun April Fools' Day prank. But I still think you should stop using NOLOCK, and have collected a whole bunch of real links, and not one of them features Rick Astley:

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him.

16 Responses

  1. MSSQL on Linux was also an April fool's prank, Who knows what SQL 2024 will bring us …

  2. Alex Friedman says:

    HAHAHAHA love it!

  3. Gustav Swanepoel says:

    Brilliant!!!!

  4. Kevin Fries says:

    I'd love to send this to one particular vendor anyway just to watch them panic. they allow users to create ad hoc sql but there's a disturbing number of nolock statements in it.

  5. Simon Richardson says:

    Interesting the amount of knocking NOLOCK gets especially if you take a look in Microsoft's own Report Server database and you will find NOLOCK used in one of the views. "Do as I say not as I do".? Also can we trust blogs posted on April fools day 😉

  6. Sime says:

    I guess nobody clicked Microsoft Blog link 🙂

  7. David Klee says:

    Brilliant 🙂

  8. dn says:

    feature can be turned off with trace a flag -666

  9. Andrea says:

    Aaron wins the day! Well done!

  10. Eugene Meidinger says:

    I found that video very illuminating.

  11. Jeff York says:

    Ha! Great one Aaron. 🙂

  12. George Walkey says:

    Now do TEXT datatypes 🙂

  13. Might be the best new feature of SQL Server 2022.

  14. Tom says:

    Do you want to use NOLOCK? You know the rules, and so do I!
    Read committed is what I'm thinking of; you won't get this from any other guy.

  15. Jens Nilsson says:

    😉 I'll pass this information forward whit in our corp. 😀

  16. This is awesome!