The NOLOCK indexing hint gets used way, way to frequently. The place that I hate seeing it the most is in financial applications, where I see it way to often.
Developers who are working on financial applications need to understand just how important not using NOLOCK is. Using NOLOCK isn’t just a go faster button, it changes the way that SQL Server lets the user read the data which they are trying to access. With the NOLOCK hint in place the user is allowed to read pages which other users already have locked for changes. This allows the` users query to get incorrect data from the query.
If the user is running a long running query that is accessing lots of rows which are in the process of being accessed, the user could get duplicate rows, or missing rows. This can obviously cause all sorts of problems with the users report as the data won’t actually be accurate. In reports that internal staff are running this is not good, if this your external users which are getting incorrect data, such as account debits and credits being processed while the user is requesting data they could suddenly get all sorts of invalid data.
If you are working with a financial application and you are seeing NOLOCK hints in there you’ll want to work on getting rid of them, and for the ones which must remain for some reason to make sure that the business users understand exactly how the data that they are looking at is totally incorrect and shouldn’t be trusted.
If the application is using the NOLOCK hint to solve performance problems so problems need to be resolved in other ways. Typically by fixing indexing problems that exist on the tables which are causing some sort of index or table scans.
Denny
