How to Get Automatically Notified of Prolonged Blocking in SQL Server

lockingms accesssql server

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a certain form and is released once the user has finished scrolling through the form or closes it. Since many of our users open this form as reference, these locks stay around for a while. Any update to the table causes the blocking, and suddenly nobody can select from this table since they're all waiting on the first lock. This is quite a problem for us, since lots of apps rely on this data. I understand that this locking behavior is part of how Access works with linked tables.

I've been solving the problem from Activity Monitor, by killing whichever SELECT process is the Head Blocker whenever I find out about it. This is a problem not only because it takes me time to do it manually, but also because it's reactive. By the time I hear about it, it's already been a problem for a lot of people.

I'd like to know if there is an automatic way to check for these long-lasting blocking chains, and either be emailed or have the problem resolved automatically. The logic seems straightforward enough ("if any process matching this SELECT query has been blocking for longer than a minute, notify me/kill it") but I don't know how to implement this with SQL Server.

For what it's worth, I think the proper solution is to fix or rewrite the app. However, due to departmental politics, this is not an option for the next few months, so I'm looking for a stopgap.

Best Answer

Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:

alter database [...] set read_committed_snapshot on;

No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of applications don't notice any difference, they just get a free performance boost.

Anyway, I though to answer also the original question: how to detect (and possibly kill) a long running query. Actually, the engine already does that for you. There is an event raised when a threshold is passed: Blocked Process Report Event Class. The threshold is configured via the blocked process threshold Option. Any trace event can be turned into an Event Notification and event notifications can activate procedures. Connect the dots and you have on-demand activated code that runs when the engine detects a query that has crossed an execution time threshold. No polling, no monitoring. Note though that the notification is asynchronous, by the time you process it the query may had completed so that has to be taken into account.

Here is an example:

use msdb;
go

create queue blocked_process_report_queue;
go

create service blocked_process_report_service
    on queue blocked_process_report_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification blocked_process_report_notification
    on server
    for BLOCKED_PROCESS_REPORT
    to service N'blocked_process_report_service',
          N'current database';
go  

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;

Now in a new query set up a WAITFOR expecting a notification:

use msdb;
waitfor(
   receive cast(message_body as xml), * 
   from  blocked_process_report_queue);

And go ahead and cause some blockage. I used a process that created a table and didn't commit, and from another query windows I tried to select from the table. In 20 seconds (my configured threshold above) I got the blocking report:

<blocked-process-report>
  <blocked-process>
    <process id="process1b5a24ca8" ...>
      <executionStack>
        <frame line="1" stmtstart="-1"... />
      </executionStack>
      <inputbuf>
          select * from t   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="51" ...>
      <executionStack />
      <inputbuf>
         begin transaction
         create table t (a int)   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

I'll leave the task of wrapping this up into an automated process as an exercise to the reader. And yes, the queue/service/activated procedure must be in [msdb].

Related Question