My SELECT statement has hung!

One of the first roadblocks many developers run into when using SQL Server for the first time is firing off a query that simply selects data, and having the query never return a result. After spending several minutes of their precious life-force watching nothing happen, they get aggravated, cancel the query, and try again, and again, and again. The query just sits there and does nothing but mock the query writer. This is especially frustrating when the table only contains a couple hundred rows and you know that one of the undeniable constants of the universe is that SQL Server should return that result set immediately, but here it is 4:02 later, 4:03 later, 4:04 later and still no results. Report writers also often run into this problem.

The cold hard truth is that you probably haven’t done anything wrong. It’s a shocking realization, but here we go: SQL Server will place locks on data even when you are simply SELECTing data!

OK, now that you have fallen out of your chair, whimpered, and returned to your former place, let’s dive into this. There is actually a logical reason. Basically, SQL Server does stuff super fast, and it has to make sure that multiple users aren’t trying to read and update the same data at the same time. This ensures that what you see is really what is on the disk. These locks that are put on the data when you select are known as read locks. These locks keep other processes from coming along and changing the data that is in your result set before your process finishes selecting the data. Imagine if you got back a result set with half the records having been updated by another process, and the other half not updated by that process. If you work with financial data, particularly highly volatile data (think stocks) this would be very, very, very bad. SQL Server takes the conservative road and ensures this won’t happen.

If your not running a highly volatile system and don’t need to worry about some records being updating during your select, then I have a simple answer for you. Actually, I have two:
1) Check out Books Online for “SET TRANSACTION ISOLATION LEVEL” and considering setting this value to the “READ UNCOMMITTED” setting. After issueing that command, your queries will no longer worry about running into locks they find on other records, and they will come back to you, or
2) In your FROM clause, add the “WITH (NOLOCK) hint. This will also tell the server to ignore if a record has a lock on it, but to simply take whatever value happens to be there. For example: SELECT * FROM CUSTOMER_DATA WITH (NOLOCK)

For a little further reading, try searching for “SQL Server” and “Dirty Reads.” A dirty read is the short-hand name for ignoring locks and simply reading the data from disk, whatever it may be.