I had an SQL Server table that was locked for almost an entire day: nothing was able to insert into it or delete from it.
Eventually, I figured out what was going on by looking at Activity Monitor. There was a SELECT session from a Microsoft Office client that was blocking all the other sessions from modifying the table. I killed that session, and the inserts immediately began to occur.
I tracked down the person who had made the blocking query, and it turned out not to be the monstrous query that I was expecting: it only returned 935 rows. I have been unable to reproduce this strange locking behavior. I ran the same query using the same client (Microsoft Query) and it returned in less than a second every time.
The query was like
SELECT * FROM MyTable WHERE ID = '09100010090361017'
MyTable has an index on ID, an index on a timestamp column, and indexes on two other columns.
I am thinking the blocking might have occurred because the client left the transaction open. But even if the client had left the transaction open, that would not completely explain this behavior, because it was filtering the data on ID, which is indexed. This should not have kept new rows from being inserted, or distant records from being deleted (which was the case). Lock escalation must have happened in order for the whole table to be locked - but how? The query only returned 935 rows! Lock escalation isn't supposed to happen until 5,000 transactions have been made. 935 rows + 935 index records to be updated is way less than 5000.
Does anyone have an idea what was going on? Could it be a bug in Microsoft Query that sometimes fails to commit transactions?
Has anyone seen this before?
As @Erik Darling pointed out, my question might not be answerable as I have put it above. So perhaps a better question would be: How can I gather more information next time this happens, to aid the troubleshooting process?
@clifton_h mentioned a "plan cache". This issue occurred ~3 weeks ago, so I doubt the plan cache retains any relevant info. But would a good approach be to wait until this problem happens again, query the plan cache to get more info on the blocking query, and go from there?