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.

Solution 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 

Solution 2

Use this:

SELECT * FROM MY_TABLE with (NOLOCK)

Solution 3

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

Solution 4

MY_TABLE could be also locked up by some uncommitted transaction -- i.e. script/stored procedure running (or failed while running) in another MSMM window.

Solution 5

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.

View more solutions

Comments

[JosephStyons]

No to #1. Yes to #2 but who/what?

[Mikhail]

See answer from BradC: Some other process has a lock on the table, preventing your select from going through until that lock is released

[JosephStyons]

exec sp_who2 gave me an ALTER INDEX that is in progress. Seems to be the culprit. Thanks.

[robert]

@BradC in #1 is 500m means 500 million, or is it something else.

[topski]

@robert, yes I meant 500 million, or some other absurdly high number.