0

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.

1

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

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.