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

use SELECT * FROM MY_TABLE with (NOLOCK)

Answer 2

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

Answer 3

Two possibilities:

1. Its a really massive table, and you're trying to return 500m rows.
2. Some other process has a lock on the table, preventing your select from going through until that lock is released.

Answer 4

If there's a lot of other activity going on, something else might be causing locks, and your SELECT might be the deadlock victim. if you run the following

SELECT * FROM my_table WITH(nolock)

you're telling the database that you're ok to read dirty (uncomitted) data, and that locks caused by other activity can be safely ignored.

Also, if a query like that causes management studio to hang, your table might use some optimization

Answer 5

MY_TABLE could be also locked up by some uncommitted transaction -- i.e. script/stored procedure running (of fails when run) in another MSMM window.