What is with nolock in SQL Server

Question

Can someone explain the implications of using with  nolock  on queries  when you should shouldn t use it   For example  if you have a banking application with high transaction rates and a lot of data in certain tables  in what types of queries would nolock be okay   Are there cases when you should always use it never use it

User · Answer

Simple answer - whenever your SQL is not altering data, and you have a query that might interfere with other activity (via locking).

It's worth considering for any queries used for reports, especially if the query takes more than, say, 1 second.

It's especially useful if you have OLAP-type reports you're running against an OLTP database.

The first question to ask, though, is "why am I worrying about this?" ln my experience, fudging the default locking behavior often takes place when someone is in "try anything" mode and this is one case where unexpected consequences are not unlikely. Too often it's a case of premature optimization and can too easily get left embedded in an application "just in case." It's important to understand why you're doing it, what problem it solves, and whether you actually have the problem.

User · Answer

WITH  NOLOCK  is the equivalent of using READ UNCOMMITED as a transaction isolation level  So  you stand the risk of reading an uncommitted row that is subsequently rolled back  i e  data that never made it into the database  So  while it can prevent reads being deadlocked by other operations  it comes with a risk  In a banking application with high transaction rates  it s probably not going to be the right solution to whatever problem you re trying to solve with it IMHO

User · Answer

The text book example for legitimate usage of the nolock hint is report sampling against a high update OLTP database   To take a topical example  If a large US high street bank wanted to run an hourly report looking for the first signs of a city level run on the bank  a nolock query could scan transaction tables summing cash deposits and cash withdrawals per city  For such a report the tiny percentage of error caused by rolled back update transactions would not reduce the value of the report

User · Answer

I use with  nolock  hint particularly in SQLServer 2000 databases with high activity   I am not certain that it is needed in SQL Server 2005 however  I recently added that hint in a SQL Server 2000 at the request of the client s DBA  because he was noticing a lot of SPID record locks     All I can say is that using the hint has NOT hurt us and appears to have made the locking problem solve itself   The DBA at that particular client basically insisted that we use the hint   By the way  the databases I deal with are back-ends to enterprise medical claims systems  so we are talking about millions of records and 20  tables in many joins   I typically add a WITH  nolock  hint for each table in the join  unless it is a derived table  in which case you can t use that particular hint

User · Answer

Not sure why you are not wrapping financial transactions in database transactions  as when you transfer funds from one account to another - you don t commit one side of the transaction at-a-time - this is why explicit transactions exist   Even if your code is braindead to business transactions as it sounds like it is  all transactional databases have the potential to do implicit rollbacks in the event of errors or failure  I think this discussion is way over your head   If you are having locking problems  implement versioning and clean up your code    No lock not only returns wrong values it returns phantom records and duplicates    It is a common misconception that it always makes queries run faster  If there are no write locks on a table  it does not make any difference  If there are locks on the table  it may make the query faster  but there is a reason locks were invented in the first place    In fairness  here are two special scenarios where a nolock hint may provide utility  1  Pre-2005 sql server database that needs to run long query against live OLTP database this may be the only way  2  Poorly written application that locks records and returns control to the UI and readers are indefinitely blocked  Nolock can be helpful here if application cannot be fixed  third party etc  and database is either pre-2005 or versioning cannot be turned on

User · Answer

Another case where it s usually okay is in a reporting database  where data is perhaps already aged and writes just don t happen   In this case  though  the option should be set at the database or table level by the administrator by changing the default isolation level   In the general case  you can use it when you are very sure that it s okay to read old data   The important thing to remember is that its very easy to get that wrong   For example  even if it s okay at the time you write the query  are you sure something won t change in the database in the future to make these updates more important     I ll also 2nd the notion that it s probably not a good idea in banking app  Or inventory app   Or anywhere you re thinking about transactions

User · Answer

You can use it when you re only reading data  and you don t really care about whether or not you might be getting back data that is not committed yet   It can be faster on a read operation  but I cannot really say by how much   In general  I recommend against using it - reading uncommitted data can be a bit confusing at best

User · Answer

Use nolock when you are okay with the  dirty  data  Which means nolock can also read data which is in the process of being modified and or uncommitted data    It s generally not a good idea to use it in high transaction environment and that is why it is not a default option on query

User · Answer

My 2 cents - it makes sense to use WITH  NOLOCK  when you need to generate reports  At this point  the data wouldn t change much  amp  you wouldn t want to lock those records

User · Answer

Short answer    Only use WITH  NOLOCK  in SELECT statement on tables that have a clustered index   Long answer   WITH NOLOCK  is often exploited as a magic way to speed up database reads   The result set can contain rows that have not yet been committed  that are often later rolled back   If WITH NOLOCK  is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table  This means that the result-set can be missing rows or display the same row multiple times    READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously

User · Answer

The question is what is worse    a deadlock  or  a wrong value    For financial databases  deadlocks are far worse than wrong values  I know that sounds backwards  but hear me out  The traditional example of DB transactions is you update two rows  subtracting from one and adding to another  That is wrong   In a financial database you use business transactions  That means adding one row to each account  It is of utmost importance that these transactions complete and the rows are successfully written   Getting the account balance temporarily wrong isn t a big deal  that is what the end of day reconciliation is for  And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database   That said  SQL Server 2005 fixed most of the bugs that made NOLOCK necessary  So unless you are using SQL Server 2000 or earlier  you shouldn t need it   Further Reading Row-Level Versioning

User · Answer

The simplest answer is a simple question - do you need your results to be repeatable  If yes then NOLOCKS is not appropriate under any circumstances  If you don t need repeatability then nolocks may be useful  especially if you don t have control over all processes connecting to the target database

User · Answer

If you are handling finance transactions then you will never want to use nolock   nolock is best used to select from large tables that have lots updates and you don t care if the record you get could possibly be out of date   For financial records  and almost all other records in most applications  nolock would wreak havoc as you could potentially read data back from a record that was being written to and not get the correct data

User · Answer

NOLOCK is equivalent to READ UNCOMMITTED  however Microsoft says you should not use it for UPDATE or DELETE statements      For UPDATE or DELETE statements  This feature will be removed in a future version of Microsoft SQL Server  Avoid using this feature in new development work  and plan to modify applications that currently use this feature    http   msdn microsoft com en-us library ms187373 aspx  This article applies to SQL Server 2005  so the support for NOLOCK exists if you are using that version  In order to future-proof you code  assuming you ve decided to use dirty reads  you could use this in your stored procedures   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

User · Answer

Unfortunately it s not just about reading uncommitted data  In the background you may end up reading pages twice  in the case of a page split   or you may miss the pages altogether  So your results may be grossly skewed   Check out Itzik Ben-Gan s article  Here s an excerpt        With the NOLOCK hint  or setting the   isolation level of the session to READ   UNCOMMITTED  you tell SQL Server that   you don t expect consistency  so there   are no guarantees  Bear in mind though   that  inconsistent data  does not only   mean that you might see uncommitted   changes that were later rolled back    or data changes in an intermediate   state of the transaction  It also   means that in a simple query that   scans all table index data SQL Server   may lose the scan position  or you   might end up getting the same row   twice

User · Answer

I ve used to retrieve a  next batch  for things to do  It doesn t matter in this case which exact item  and I have a lot of users running this same query

[sql-server] What is "with (nolock)" in SQL Server?

Examples related to sql-server

Examples related to nolock