Last year, I had an assignment to create some reports using SQL Server Reporting Services (SSRS). The database team at this company had created a reporting database, using data cubes, with information rolled up from a number of production systems.

In some cases, the queries were complex, and the reports were created using stored procedures as data sources.

The company had well-defined standards for the structure of the procedures, the naming conventions, etc.

All very normal, so far. Right?

The odd thing in their standard was the directive that the (nolock) instruction be used on any selects from permanent database tables. So queries had to look like,

Let’s take a look at what (nolock) does.

In the normal course of selecting data from a table, the select statement will put a SHARED lock on a table. This type of lock will allow other SHARED locks to run at the same time, but it will block EXCLUSIVE lock transactions from happening before that lock is removed.

In simpler terms, you can run as many select statements on data as you would like. They won’t stop each other. But they will prevent updates, inserts, or deletes from occurring while they are in progress.

Conversely, if the data in a table is being modified, the lock is EXCLUSIVE. Select statements won’t be able to run until those updates are complete.

The (nolock) hint tells SQL Server to go ahead and run the select anyway. During the course of the select, information may be modified.

When a statement modifies data, it also modifies indexes. Data may be moved from page to page. It may be broken by the server in unpredictable ways. While the EXCLUSIVE lock is on, the information may be stored in two places at once. There may be moments where it isn’t in the table at all. This is well documented. SQL Server Books Online says, “When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.”

So if you need accurate, detailed information, you should never, never use nolock. I can’t really ever think of a case where I would recommend it.

The kicker in this case is that the database is just used for reporting. Updates occur in off-hours, so by just using regular select statements, with their regular shared locks, there will be no blocking.