SQL Server with Mr. Denny

Jul 17 2013   7:00AM GMT

Nolock and your financial application



Posted by: Denny Cherry
Tags:
Index Performance
Indexing
Nonclustered Index
Query tuning
SQL Server

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: