0

One of my colleagues came to me with this statement:

Having a SELECT on a table that fetch 80% of the rows while having a WHERE clause on a column with an index. So to avoid that add a WITH (NOLOCK) in your FROM clause.

His only argument was: Believe me I've experienced it myself. I cannot find a proper documentation for this.

I far has I know WITH (NOLOCK) only affects the table by letting UPDATE and INSERT occur while selecting and that can lead us to dirty read.

Is my colleague's assumption correct?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Muffun
  • 726
  • 1
  • 9
  • 27
  • 1
    Possible duplicate of [SQL Server - when should you use "with (nolock)"](http://stackoverflow.com/questions/686724/sql-server-when-should-you-use-with-nolock) – Igor Sep 21 '16 at 19:23
  • 2
    Be very careful if you only read the accepted answer in the possible duplicate. NOLOCK can be FAR more sinister than simply dirty reads. It can sometime miss entire pages of data. Or it can read pages multiple times. This article covers a number of the bad things that can happen with NOLOCK. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ Make sure you read some of the deeper articles referenced as they go really deep into oftem misunderstood query hint. – Sean Lange Sep 21 '16 at 19:33
  • Here is a [free PDF](http://www.sqlservercentral.com/articles/books/93946/) on SQL Server Concurrency to give you some information. – BJones Sep 21 '16 at 19:36
  • In short, "WITH (NOLOCK)" returns result from uncommitted rows and does not worry about other locks on the respective rows. Your college assumption is correct, but be careful of the uncommitted rows. – ash Sep 21 '16 at 23:39
  • 1
    @marc_s I edited the question. I really meant colleague, a guy who work with me. – Muffun Sep 22 '16 at 13:15
  • Was the original problem that if you put a WHERE clause on a SQL statement that hit a table's index, that it wouldn't return all of the rows that it ought to? That's completely incorrect if that's the case. Adding `WITH (NOLOCK)` to solve a problem that doesn't exist, seems odd. It also seems like a great way to introduce actual real problems. – JNevill Sep 22 '16 at 13:24
  • @Muffun:is your question `will select cause locking ` – TheGameiswar Sep 22 '16 at 13:25
  • @TheGameiswar Yes, if you consider the assumption of my colleague. I want to find arguments that can prove or disprove is theory, – Muffun Sep 22 '16 at 13:29

2 Answers2

0

I think you're referring to lock escalation, https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx , combined with a table scan caused by an index with bad selectivity, and some possibilities for blocking.

If the statistics on a non clustered index show that the number of rows returned from a table for a specific value exceed some threshold, then the optimizer will choose to use a table scan to find the corresponding rows instead of an index seek with corresponding bookmark lookups, because they are slow in quantity.

I typically tell people that you want that percentage to be 5% or lower, but sometimes it will still index seek up to 10% or so. At 80%, it's definitely going to table scan.

Also, since the query is doing a table scan, the query has to be able to acquire some kind of lock on every single row in the table. If there are any other queries that are running performing updates, or otherwise preventing locks from being acquired on even a single row, the query will have to wait.

With lock escalation, it's not a percentage, but instead a specific magic number of 5,000. A query generally starts reading rows using row locks. If a single query reads 5,000 or more rows, it will escalate the locks that it is using against the table from row and/or page locks to full table locks.

This is when deadlocks happen, because another query may be trying to do the same thing.

These locks don't necessarily have anything to do with inserts/updates.

This is an actual thing. No, this does not mean that you should use NOLOCK. You'd be much better off looking at READPAST, TABLOCK, or TABLOCKX, https://msdn.microsoft.com/en-us/library/ms187373.aspx , if you're having issues with deadlocks.

Do not do any of these things just out of habit and only look into them for specific instances with highly transactional tables that are experiencing actual problems.

Bruce Dunwiddie
  • 2,888
  • 1
  • 15
  • 20
0

By default writers have priority and readers will wait on writers to finish. WITH(NOLOCK) will allow readers to read uncommitted data, avoiding waits on writers to finish. For read only queries against very large tables, this is ok if you are querying data such as an old partition of data or pulling back data that is not going to change often and changes are not critical in the presentation of data. This is the same as using the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED directive in SP's.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55