Isolation levels, NOLOCK, row versioning, RCSI, and lock behavior.
36 associated posts36 primary posts
Normally when I tell people about SQL Server's optimistic concurrency isolation levels, Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI), I have to give them a little speech about how they need to test their queries because the results can change. Recently, though, I was working with a client who was getting the wrong query…
It's an overcast afternoon at Atlantis Paradise Island in the Bahamas, so since I can't go into the water, might as well go through your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/1c0K-WJ-itk
I have never seen a T-SQL feature that people love as much as NOLOCK.
I keep thinking I've written enough blog posts about it, but a client came up with a new one:
We're heading towards graduation time.
In just a few short months, your high school kids are going to be packing up their bags and heading off to college. It's a time of adventure and discovery, and the first time they'll be away from your parenting and guiding influences.
While in beautiful Haines, Alaska along the Chilkoot River, I stopped to take your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/esrXsRZo_gU
As my time in Hong Kong came to an end, I sat inside on a foggy morning and hit your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=jylCG2l6YSA
The first day back after a long vacation is always rough, and today's no exception. I took your questions from https://pollgab.com/room/brento in an effort to turn things around and get back in the groove:
https://youtu.be/hvIXTnwh4Sg
My Black Friday sale is in its last days, so most of my time at the moment is spent keeping an eye on the site and answering customer questions. I'm happy to say it's our best year so far, too! Y'all really like the new access-for-life options.
I took a break from the online frenzy to check in on the questions you posted at https://pollgab.com/room/brento and answer the highest-voted ones:
When you find yourself using NOLOCK, consider NOSQL instead.
NOLOCK has 4 big problems:
When you have a hammer, everything looks like a nail. Unfortunately, a lot of people don't even know how to use a hammer correctly.
You really shouldn't be doing queues in the database because:
When you put NOLOCK in your query, SQL Server will:
Read rows twice
Skip rows altogether
Show you changes that never actually got committed
Let your query fail with an error
Good evening, party people! I finally have my new San Diego home base all set up and unpacked, so I took a champagne break to review your top-rated questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=ESHC8NZKf5E
I sit on the porch and discuss your highly voted questions from https://pollgab.com/room/brento.
https://youtu.be/YJ92WDOwOMo
If you've got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That's great for rowstore indexes, but...columnstore indexes are different.
To demo this technique, I'm going to use the setup from my Fundamentals of Columnstore class:
Some of y'all, bless your hearts, are really, really, really in love with NOLOCK.
I've shown you how you get incorrect results when someone's updating the rows, and I've shown how you get wrong-o results when someone's updating unrelated rows. It doesn't matter - there's always one of you out there who believes NOLOCK is okay in their special situation.
Say for some reason you can't use identity columns or sequences, or maybe your application was built a long, long time ago - and you've got a table with a list of IDs to use next:
[crayon-6a6eb905ae05f307794826/]
Let's finish up Research Paper Week with something we're all going to need to read over the next year or two. I know, it says Azure SQL DB, but you boxed-product folks will be interested in this one too: Constant Time Recovery in Azure SQL DB by Panagiotis Antonopoulos, Peter Byrne, Wayne Chen, Cristian Diaconu, Raghavendra Thallam Kodandaramaih, Hanuma Kodavalla, Prashanth Purnananda, Adrian-Leonard Radu, Chaitanya Sreenivas Ravella, and Girish Mittur Venkataramanappa (2019).
If you've been doing performance tuning for several years, or graduated from my Mastering Server Tuning class, you've come across Read Committed Snapshot Isolation, aka RCSI, aka multi-version concurrency control, aka MVCC, aka optimistic concurrency. It's not the way SQL Server ships by default, although it is the default for Azure SQL DB, and it's part of the magic in how you can query an Availability Group readable secondary even when it's applying updates to the tables behind the scenes.
I've already covered how NOLOCK gives you random results when you're querying data that's changing, and that's a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I've gotten a question from several users: But I'm querying data that isn't changing - sure, OTHER rows in the table…
Implicit transactions are a hell of a bad idea in SQL Server: they require you to micromanage your transactions, staying on top of every single thing in code. If you miss just one little DELETE/UPDATE/INSERT operation and don't commit it quickly enough, you can have a blocking firestorm.
The ideal answer is to stop using implicit transactions, and only ask for transactions when you truly need 'em. (Odds are, you don't really need 'em.)
