CODEWITHSUNDEEP
sql-server-2008
user3200957
user3200957

Reputation: 23

MS SQL SELECT WITH NO LOCK

Is it safe to use SELECT with (NOLOCK) on a table that is never updated?

Upvotes: 1

Views: 140

Answers (1)

usr
usr

Reputation: 171178

Nolock is safe under a very specific set of circumstances. Also, safety is not all or nothing. You might not care about some safety properties.

Nolock scans will sometimes fail if data in the tables moves around physically. Scans also can see rows twice or not at all. If your DML does not cause row movement this shouldn't happen. I'm saying "shouldn't" because this is not formally guaranteed by the product. For example, shrinking a file or migrating it empty should also cause row movement.

Updates can cause insert/delete pairs. Inserts and deletes can cause row movement.

Some specific forms of DML cannot cause row movement in the current implementation of the product although I doubt this is formally guaranteed either. For example, inserts that append to the b-tree being scanned don't cause row movement although the newly inserted row might be missed (I think).

Most of the time, when you use NOLOCK you should expect to very rarely see slightly broken data and very rarely see scans of b-trees fail. If that's alright with you then go ahead.

Upvotes: 1

Related Questions