CODEWITHSUNDEEP
sql-server-2005locking
JosephStyons
JosephStyons

Reputation: 58685

Why would this SELECT statement lock up on SQL Server?

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.

Upvotes: 5

Views: 12798

Answers (5)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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.

Upvotes: 1

Mikhail
Mikhail

Reputation: 1613

Use this:

SELECT * FROM MY_TABLE with (NOLOCK)

Upvotes: 3

David Hedlund
David Hedlund

Reputation: 129792

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

Upvotes: 3

BradC
BradC

Reputation: 39946

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.

Upvotes: 1

Raj More
Raj More

Reputation: 48016

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

Upvotes: 11

Related Questions