0

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?

======================================================================

EDIT:

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?

  • 5
    It could have been an implicit transaction? Without you capturing more data, it's probably not an answerable question. – Erik Darling Jan 5 at 0:38
  • 2
    Also, just because a query only returns 935 rows does not mean it did not need a full table scan to retrieve those rows. If this is recent enough, what does the plan cache say about the query? – clifton_h Jan 5 at 14:31
  • 1
    For better analysis of what's currently happening on your system, try sp_WhoIsActive. – Erik Darling Jan 6 at 22:16
  • I had similar issue some years ago with a MS-Excel app and it was solved by changing the way of refreshing the table. We did a download for the selection into the spreadsheet and closed the connection. Otherwise, the table was remaining blocked for long time. – Angel M. Jan 7 at 7:27

Your Answer

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

Browse other questions tagged or ask your own question.