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.
Best Answer
As mentioned by others use spwhoisactive or spblitzwho to find the behavior.
However I am suspecting you do not have sufficient index on that table. A better index will just make the lead blockers query run fast and avoid blocking the query scanning same table.
Also check for open transactions with sleeping status . Sometimes they create those blocking scenarios when application did not close the connection well