Is the NOLOCK (Sql Server hint) bad practice?

by

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.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Split function equivalent in T-SQL?
  7. Convert varchar into datetime in SQL Server
  8. Generate Dates between date ranges
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. How to print VARCHAR(MAX) using Print Statement?
  11. Where value in column containing comma delimited values
  12. Check if a row exists, otherwise insert
  13. How to update Identity Column in SQL Server?
  14. SQL query to split column data into rows
  15. Common Table Expression, why semicolon?
  16. T-SQL split string based on delimiter
  17. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  18. Concatenate values based on ID
  19. Splitting delimited values in a SQL column into multiple rows
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Using PIVOT in SQL Server 2008
  23. Stored procedure that exports data into csv files only exports to one file
  24. How do I prevent a database trigger from recursing?
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. How to combine results of two queries into a single dataset
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. SQL take just the numeric values from a varchar
  30. COALESCE Function in TSQL

Leave a Comment