I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following:
select * from thetable select count(*) from thetable
There are roughly 2,000 records.
I can do the following:
select top 2000 * from thetable select * from thetable where ID = etc.
Going backwards to find a new record that perhaps was problematic, incrementing the count in chunks until I can finally reproduce it again:
select top 1500 * from thetable order by ID desc select top 1550.... etc. and eventually it gets locked and never finishes the query.
Query never finishes... have waited 10 minutes. Only resolution is to restart the service.
The related stored procedure that I thought caused the problem I ran manually (it interacts with this table) and the longest time it took was roughly 45 seconds. This particular procedure goes through many phases and is wrapped in a transaction with a try/catch/rollback/commit. There is no explicit locking set on the procedure.
Any direction or guidance to track down the root issue is greatly appreciated.
You want to run something like 'sp_whoisactive', 'sp_blitzwho', etc while waiting for your select.
With those store procedure, you will get the "blocking" session and you will be able to see what it is running.