The Problems With NOLOCK

Rob Farley demonstrates the downside of the READ UNCOMMITTED isolation level:

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.

Read on to see what happens when someone gets the idea of running the select query with NOLOCK.