Why would this SELECT statement lock up on SQL Server?

Question

I have a simple query like this

SELECT * FROM MY_TABLE;

When I run it, SQL Server Management Studio hangs.

Other tables and views are working fine.

What can cause this? I've had locks while running UPDATE statements before, and I know how to approach those. But what could cause a SELECT to lock?

I have run the "All Blocking Transactions" report, and it says there are none.

Answer 1

It is probably not the select that is locking up, but some other process that is editing (udpate/delete/insert) the table that is causing the locks.

You can view which process is blocking by runing exec sp_who2 on your SQL Server.

Alternatively, if you are OK with dirty reads, you can do one of two things

SELECT * FROM Table WITH (NOLOCK)

OR

SET Transaction Isolation Level Read Uncommitted
SELECT * FROM Table