« Doh! Using this.mappings in Application.cfc | Main | CF8 tabbed-form layout gotcha in Firefox, Safari »

Using MS-SQL's NOLOCK for faster queries

A nice little coincidence just happened for me: right after my boss had emailed me asking if a certain click-tracking report in our site's admin section could run any faster, the developer next to me, who was looking through some of our existing code, asked me what this "NOLOCK" mention in an MS-SQL query meant. Talk about perfect timing! I probably wouldn't have remembered NOLOCK if he hadn't brought it up.

Used correctly, NOLOCK is a handy-dandy little feature. You may not know that your database puts locks on tables just like ColdFusion's cflock statement can lock variables in memory. Locks can either be exclusive, meaning that no other process can read or write the table because the first process is writing to it, or they can be read-only, meaning that other processes can read the table at the same time the first process is reading it also (but no process can write to the table, or else the reads might become corrupted).

So, locks are used by databases to ensure data integrity. But, every once in a while, if two processes are trying to access the same data at around the same time, locks mean that queries might run a little slower than usual because one process will have to wait for the other to finish before it can run. That's where NOLOCK can come in handy. If you're running a SQL statement for, say, a report, and you know it's going to take a long time to run and be reading lots of records, you can use NOLOCK to speed it up. Just add the phrase "WITH (NOLOCK)" in your FROM clause after any table which you want to read without locking. So, for example, in my click-tracking report code, which reads thousands upon thousands of click records while the clicks table is being updated with new click data:


SELECT clicks.entryURL, clicks.entryTime, sessions.userID
FROM sessions, clicks WITH (NOLOCK)
WHERE sessions.sessionID = clicks.sessionID
     AND clicks.entryTime > DATEADD(day, -1, GETDATE())

Now remember, locking is a good thing. Databases do it automatically so that your data doesn't become corrupted. So don't use NOLOCK to do any writing of data, or even for quick reads. But if you've got a long-running query which needs to read a large number of records, you won't hurt anything by reading without a lock. Plus, any other transactions won't have to wait to run, which could speed up the application for everyone else while you're running your report.

Note that MySQL also locks tables automatically like MS-SQL does, but doesn't appear to allow any way to prevent locking at all. You can, however, lock and unlock tables via SQL code.

Comments (19)

Good article. Thank you.

Thanx it cleared my concept.

MySQL doesn't lock tables like MS-SQL does. The locking mechanisms that MySQL uses differ depending on the storage engine. InnoDB in particular generally does only row-level locking: http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

MyISAM and some of the other storage engines do full table-level locking: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

It sounds like SQL Server behaves more like MyISAM in this case. MyISAM is popular for read-heavy scenarios, but it's locking mechanism is among the reasons InnoDB is preferable in many others.

Excellent article on NOLOCK.

with tranactSQL you can set it with the following one time command per the procedure, save some typing:

SET transaction isolation level read uncommitted


Nice. Very useful. Thanks a lot.

good and clear article. thanks.

This is a very usefull, thanks now i know what is my problem

Excellent article.....explained the concept in a simple and clear way....Thanks

Excellent article...its helped me a lot...

Thanks
Raja

really it hepled me alot
thanks for such a good article.
Expecting more articles........

It's well worth reading the MSDN blog post 'SQL Server NOLOCK Hint & other poor ideas.':

"NOLOCK / READ UNCOMMITTED HINT
This Hint is much more dangerous than its name suggests. And that it why most people who don’t understand the problem, recommend it. It creates 'incredibly hard to reproduce' bugs. The type that often destroy your end-users confidence in your product & your company."

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Very good article.
Thank you.

I've loaded your blog in 3 totally different internet browsers and I must say this website loads a lot quicker then most. Would you mind e-mailing me the company name of your web hosting company? My personal email is: kitdupont@gmail.com. I'll even sign up through your own affiliate link if you'd like. Kudos

トリーバーチといえば、靴。その中でも、ペタンコなシューズをファッションの一つとして定着させた業績はすごいの一言です。

If some one wishes to be updated with most recent technologies after that he must be pay a quick visit this web site and be up to date everyday.

There is a greater tendency to over compensate when
you are looking to compensate for a perceived fault in any given relationship.
He is going to come to realize you are doing well even without him.
If your ex boyfriend perceives in your voice that you're thriving
as well as even better after the relationship ended,
it will feel like a fast kick to their personality.

whoah this blog is fantastic i like studying your posts.
Stay up the great work! You understand, many persons are hunting round for this information,
you could help them greatly.

It's an remarkable post in support of all the web visitors; they will get advantage from it I am sure.

Post a comment