sqlsql-servertransactionsnolock

Simple select not returning all rows and sp_who returns no blocks

I'm running a simple select on a table with a small amount of rows, and it's running for forever. I found that it returns fine if I run:

SELECT TOP 23 * FROM MyTable ORDER BY 1

However, it hangs forever if I run:

SELECT TOP 24 * FROM MyTable ORDER BY 1

If I run sp_who or sp_who2, there is no one blocking. Why does this happen, and how can I fix it?

Solution

  • If you run with nolock on (ignoring uncommitted transactions), you should be able to select all rows:

    SELECT * FROM MyTable WITH (NOLOCK)

    There's a transaction somewhere that has not been committed or rolled back (I'm not sure why it doesn't show up in sp_who). You can run the following to force all transactions to rollback, and then selecting should work fine without nolock:

    USE master
ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DbName SET MULTI_USER