132

I'm in the business of making website and applications that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea.

So, with that massive disclaimer, is it bad using the NOLOCK hint in some Sql statement? A number of years ago, it was suggested by a fellow Sql Administrator that I should use NOLOCK if I'm happy with a "dirty read" which will give me a bit more performance out of my system because each read doesn't lock the table/row/whatever.

I was also told that it's a great solution if I'm experiencing dead-locks. So, I started following that thought for a few years until a Sql guru was helping me with some random code and noticed all the NOLOCKS in my sql code. I was politely scolded and he tried to explain it to me (why it's not a good thing) and I sorta got lost. I felt that the essence of his explanation was 'it's a band-aid solution to a more serious problem .. especially if you're experiencing deadlocking. As such, fix the root of the problem'.

I did some googling recently about it and came across this post.

So, can some sql db guru sensei's please enlighten me?

Kara
  • 6,115
  • 16
  • 50
  • 57
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

12 Answers12

112

Prior to working on Stack Overflow, I was against NOLOCK on the principal that you could potentially perform a SELECT with NOLOCK and get back results with data that may be out of date or inconsistent. A factor to think about is how many records may be inserted/updated at the same time another process may be selecting data from the same table. If this happens a lot then there's a high probability of deadlocks unless you use a database mode such as READ COMMITED SNAPSHOT.

I have since changed my perspective on the use of NOLOCK after witnessing how it can improve SELECT performance as well as eliminate deadlocks on a massively loaded SQL Server. There are times that you may not care that your data isn't exactly 100% committed and you need results back quickly even though they may be out of date.

Ask yourself a question when thinking of using NOLOCK:

Does my query include a table that has a high number of INSERT/UPDATE commands and do I care if the data returned from a query may be missing these changes at a given moment?

If the answer is no, then use NOLOCK to improve performance.

I just performed a quick search for the NOLOCK keyword within the code base for Stack Overflow and found 138 instances, so we use it in quite a few places.
Community
  • 1
  • 1
Geoff Dalgas
  • 6,116
  • 6
  • 42
  • 58
  • 9
    IMO, This is a bit simplistic. Deadlocks can be removed by using covering indexes, taking the pressure off the Clustered index. – Mitch Wheat Sep 21 '09 at 06:27
  • 11
    I do not wish to diminish the importance of good index coverage. There are times that queries using NOLOCK can add additional performance on top of gains realized by indexes on tables with a high number of inserts / updates. Query speed on Stack Overflow is paramount even at the cost of inaccurate or missing data. – Geoff Dalgas Sep 21 '09 at 06:54
  • Thanks heaps Geoff for the answer. I need to digest it but i've been hanging for some answers about how SO delt with their deadlocking issues from the early days. I never read any 'results' of their research or problem solving, for it. – Pure.Krome Sep 21 '09 at 11:50
  • Can you estimate the performance gain? There a no statistics or sample data. – Eugeniu Torica Apr 16 '10 at 11:49
  • 2
    @MitchWheat A `SELECT`, reading only from a covering index, can cause a deadlock. SPID 1) start a `SELECT` from the covering index. SPID 2) Start an `UPDATE` of the table. Update then moves onto updating the covering index. `UPDATE` reaches an index range locked by the `SELECT` and becomes blocked. SPID 1) still seeking through the covering index, finds a range locked by the `UPDATE` and becomes blocked. ***DEADLOCK***. Nothing can solve that deadlock (except catching SQL Server error 1205, and automatically retrying, or using `NOLOCK`) – Ian Boyd Apr 16 '13 at 15:04
  • @Ian: Yes, they can. But more often than not its clustered index contention. – Mitch Wheat Apr 17 '13 at 00:02
  • Doing large table sorts is a situation where a covering index wont help performance and if you must wait for SQL to finish the sort you will want to use NOLOCK so it doesn't lock the table... – emalamisura May 14 '13 at 18:15
  • 2
    I think the important thing to note about this answer is that it was appropriate _for the problem at hand_. Depending on your application, the risk of stale/uncommitted/duplicate/missing data may not be worth the tradeoff. – Holistic Developer Sep 19 '14 at 17:08
  • I would also say in cases of a data warehouse where the data does not change but once a day it can be a beneficial practice as there will be no uncommitted records. – Davin Studer Aug 10 '16 at 17:10
  • @DavinStuder if data does not change (only once a day), I believe there is no need to use `NOLOCK` anyway. – Alisson Reinaldo Silva Aug 17 '17 at 20:41
71

With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.

I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.

Sylvain Rodrigue
  • 4,751
  • 5
  • 53
  • 67
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    Rex, please feel free to add a note about snapshot isolation. – Sam Saffron Sep 21 '09 at 06:01
  • 2
    Yeah, Sam is saying Snapshot isolation and you're suggesting Read Committed Snapshot. i'm getting so confused :P (and i've yet to delve into the articles, also!) – Pure.Krome Sep 21 '09 at 11:49
  • 2
    It is occassionally useful, but not normally for production. I use it frequently for pulling out a sample of data to test with or for generating reports where I mostly care about rough order of magnitude where a dirty read won't matter. – TimothyAWiseman May 14 '12 at 22:50
  • NOLOCK == i do not care if committed rows are missed, uncommited rows are included, in rare cases the same row is returned more than once, and in very rare cases rows are returned which do not match my query. (see http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx , found from another SO q'n on this topic) – Andrew Hill Jul 29 '15 at 01:58
21

If you don't care about dirty reads (i.e. in a predominately READ situation), then NOLOCK is fine.

BUT, be aware that the majority of locking problems are due to not having the 'correct' indexes for your query workload (assuming the hardware is up to the task).

And the guru's explanation was correct. It is usually a band-aid solution to a more serious problem.

Edit: I'm definitely not suggesting that NOLOCK should be used. I guess I should have made that obviously clear. (I would only ever use it, in extreme circumstances where I had analysed that it was OK). AS an example, a while back I worked on some TSQL that had been sprinkled with NOLOCK to try and alleviate locking problems. I removed them all, implemented the correct indexes, and ALL of the deadlocks went away.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 3
    Hmm.. I still don't get it. So it's fine, but it's also poor form .. is that what you're saying? – Pure.Krome Sep 21 '09 at 05:41
  • 1
    On the assumption that you NEVER care about dirty reads, then it won't hurt. BUT it is usually a case of treating the symptom and not the cause... – Mitch Wheat Sep 21 '09 at 05:49
  • 2
    Well I do not think its fair rexem was just downvoted, I think you did not address the arbitrary errors that just float up when you use nolock. Its not fine to get a blank error page once in a while on your website, it is really poor form. I dislike the assertion that "if you dont care about dirty reads its fine" ... it is not fine, even if you dont care about dirty reads – Sam Saffron Sep 21 '09 at 05:55
  • The blank page you get when a query generates an exception which you didn't implement retry logic for. What happens on your sites when query execution results with an exception, do you have retry logic everywhere? – Sam Saffron Sep 21 '09 at 06:00
  • Take a very well optimized query that you know is hitting proper indexes. Then add nolock hints and watch it get faster. If you don't care about dirty reads you will never hurt yourself using nolock. – Hardwareguy Apr 13 '11 at 12:08
14

Doubt it was a "guru" who'd had any experience in high traffic...

Websites are usually "dirty" by the time the person is viewing the completely loaded page. Consider a form that loads from the database and then saves the data that's edited?? It's idiotic the way people go on about dirty reads being such a no no.

That said, if you have a number of layers building on your selects, you could be building in a dangerous redundancy. If you're dealing in money or status scenarios, then you need not only transactional data read/writes, but a proper concurrency solution (something most "gurus" don't bother with).

On the other hand, if you have an advanced product search for a website (ie something that likely won't be cached and be a little intensive) and you've ever built a site with more than a few concurrent users (phenominal how many "experts" haven't), it is rediculous to bottle neck every other process behind it.

Know what it means and use it when appropriate. Your database will almost always be your main bottle neck these days and being smart about using NOLOCK can save you thousands in infrastructure.

EDIT: It's not just deadlocks it helps with, it's also how much you are going to make everybody else wait until you're finished, or vice versa.

Using NOLOCK Hint in EF4?

Community
  • 1
  • 1
Gats
  • 3,452
  • 19
  • 20
10

None of the answers is wrong, however a little confusing maybe.

  • When querying single values/rows it's always bad practise to use NOLOCK -- you probably never want to display incorrect information or maybe even take any action on incorrect data.
  • When displaying rough statistical information, NOLOCK can be very useful. Take SO as an example: It would be nonsense to take locks to read the exact number of views of a question, or the exact number of questions for a tag. Nobody cares if you incorrectly state 3360 questions tagged with "sql-server" now, and because of a transaction rollback, 3359 questions one second later.
realMarkusSchmidt
  • 4,303
  • 1
  • 29
  • 33
  • I don't agree with your first point at all. IF you are querying single values/rows, and you are specifying a unique id for that row, and you know that no other process will be accessing it, then using nolock is perfectly acceptable, and reduces blocking in a concurrent application. – tuseau May 16 '11 at 12:16
  • 1
    No, it's not. The row might change for other reasons, for example inserting another row splits the page. Proper indexing, Read Committed Snapshot, and Snapshot Isolation are almost always better ideas. – Mark Sowul Apr 26 '12 at 16:48
  • 1
    @tuseau if you "know" that no other process will be accessing the row, the the act of taking a lock won't block anything, so costs you (practically) nothing, – Andrew Hill Apr 18 '16 at 04:55
2

When app-support wanted to answer ad-hock queries from the production-server using SSMS (that weren't catered for via reporting) I requested they use nolock. That way the 'main' business is not affected.

richard101
  • 29
  • 1
2

I agree with some comments about NOLOCK hint and especially with those saying "use it when it's appropriate". If the application written poorly and is using concurrency inappropriate way – that may cause the lock escalation. Highly transactional table also are getting locked all the time due to their nature. Having good index coverage won't help with retrieving the data, but setting ISOLATION LEVEL to READ UNCOMMITTED does. Also I believe that using NOLOCK hint is safe in many cases when the nature of changes is predictable. For example – in manufacturing when jobs with travellers are going through different processes with lots of inserts of measurements, you can safely execute query against the finished job with NOLOCK hint and this way avoid collision with other sessions that put PROMOTED or EXCLUSIVE locks on the table/page. The data you access in this case is static, but it may reside in a very transactional table with hundreds of millions of records and thousands updates/inserts per minute. Cheers

user2041151
  • 21
  • 1
2

The better solutions, when possible are:

  • Replicate your data (using log-replication) to a reporting database.
  • Use SAN snapshots and mount a consistent version of the DB
  • Use a database which has a better fundamental transaction isolation level

The SNAPSHOT transaction isolation level was created because MS was losing sales to Oracle. Oracle uses undo/redo logs to avoid this problem. Postgres uses MVCC. In the future MS's Heckaton will use MVCC, but that's years away from being production ready.

patmortech
  • 10,139
  • 5
  • 38
  • 50
pwy
  • 21
  • 1
  • There's a typo above. I mean to say "better fundamental transaction isolation mechanism". – pwy Feb 07 '14 at 22:37
  • 1
    SNAPSHOT transaction isolation level is MS's invention. Basically it puts the data in a temp table in TEMPDB. That DB is shared among all DBs on the box. So, you'll want to use SSDs for TEMPDB when possible. That's probably less effort than the other options. – pwy Feb 07 '14 at 23:08
2

I believe that it is virtually never correct to use nolock.

If you are reading a single row, then the correct index means that you won't need NOLOCK as individual row actions are completed quickly.

If you are reading many rows for anything other than temporary display, and care about being able repeat the result, or defend by the number produced, then NOLOCK is not appropriate.

NOLOCK is a surrogate tag for "i don't care if this answer contains duplicate rows, rows which are deleted, or rows which were never inserted to begin with because of rollback"

Errors which are possible under NOLOCK:

  • Rows which match are not returned at all.
  • single rows are returned multiple times (including multiple instances of the same primary key)
  • Rows which do not match are returned.

Any action which can cause a page split while the noLock select is running can cause these things to occur. Almost any action (even a delete) can cause a page split.

Therefore: if you "know" that the row won't be changed while you are running, don't use nolock, as an index will allow efficient retrieval.

If you suspect the row can change while the query is running, and you care about accuracy, don't use nolock.

If you are considering NOLOCK because of deadlocks, examine the query plan structure for unexpected table scans, trace the deadlocks and see why they occur. NOLOCK around writes can mean that queries which previously deadlocked will potentially both write the wrong answer.

Andrew Hill
  • 1,921
  • 1
  • 25
  • 31
  • You are right that page splits can cause missing or duplicate rows. However, in case of read committed (default) level also Index order change (not page splits) can result in duplicates and missing rows. – variable Jul 08 '22 at 16:11
2

As a professional Developer I'd say it depends. But I definitely follow GATS and OMG Ponies advice. Know What You are doing, know when it helps and when it hurts and

read hints and other poor ideas

what might make You understand the sql server deeper. I generally follow the rule that SQL Hints are EVIL, but unfortunately I use them every now and then when I get fed up with forcing SQL server do things... But these are rare cases.

luke

luckyluke
  • 1,553
  • 9
  • 16
1

NOLOCK is often exploited as a magic way to speed up database reads, but I try to avoid using it whever possible.

The result set can contain rows that have not yet been committed, that are often later rolled back.

An error or Result set can be empty, be missing rows or display the same row multiple times.

This is because other transactions are moving data at the same time you're reading it.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
-1

In real life where you encounter systems already written and adding indexes to tables then drastically slows down the data loading of a 14gig data table, you are sometime forced to used WITH NOLOCK on your reports and end of month proessing so that the aggregate funtions (sum, count etc) do not do row, page, table locking and deteriate the overall performance. Easy to say in a new system never use WITH NOLOCK and use indexes - but adding indexes severly downgrades data loading, and when I'm then told, well, alter the code base to delete indexes, then bulk load then recreate the indexes - which is all well and good, if you are developing a new system. But Not when you have a system already in place.

user6699628
  • 11