This month’s writing assignment is to describe code I wouldn’t want to live without, and Bert (thanks for hosting, by the way!) actually says “that you’ve written”. Well, that last point made me think, because the examples I’m most likely to wish I had with me are all ones that I can find easily enough online, and are very unlikely to have been written by me.
The most obvious one that I think of that I wrote and have used multiple times and was really frustrated when I couldn’t find it when I needed it was one that I’ve used remarkably rarely in comparison – being a warehouse date table with a bunch of computed columns (in fact, every column except one called ActualDate was calculated, and persisted of course). I’d taken the time to work out all the nuances of nondeterminism to make sure that it worked correctly, handling financial years and English month&day names, and variations around the start of the week, and all kinds of things.
…but it felt very multidimensional-centric, and I haven’t used it in quite a while.
It was hard to find another example, but I’m going to go with the example that I use to demonstrate the pain of NOLOCK. Because I’ve typed this fresh lots of times, and I find it’s still very compelling. So I’ll write it for you again.
A case against NOLOCK
I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs.
|
1 2 |
CREATE TABLE dbo.demoNOLOCK (someguid uniqueidentifier NOT NULL PRIMARY KEY); INSERT dbo.demoNOLOCK (someguid) SELECT TOP (1000000) NEWID() FROM sys.all_columns t1, sys.all_columns t2; |
Next I prove that there a million rows.
|
1 |
SELECT COUNT(*) FROM dbo.demoNOLOCK; |
Now without inserting or deleting any rows, I’m going to shuffle them.
|
1 |
UPDATE dbo.demoNOLOCK SET someguid = NEWID(); |
And if while this is happening, I count the rows in a different session, I have to wait for that query to finish.
But what if I use NOLOCK?
|
1 |
SELECT COUNT(*) FROM dbo.demoNOLOCK (NOLOCK); |
I run this over and over, to demonstrate the results that come out.
I don’t have to wait for the answer, but the number it gives me is…
…WRONG!
That’s right, I won’t get the correct answer until the first query is finished. At least, I’m very unlikely to.
Of course it’s exaggerating what happens in real life, but I find it makes the point.
This Post Has 2 Comments
Pingback: The Problems With NOLOCK – Curated SQL
Pingback: T-SQL Tuesday #104 Roundup – SQL with Bert