Tom Mollerus' Weblog

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.