Blocked process mystery

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Stephen Brown (nospam_at_telusplanet.net)
Date: 09/20/04 

Date: Mon, 20 Sep 2004 11:03:57 -0600

This morning, we discovered that our application was timing out on several
pages. We tracked it down to a table that couldn't be read without timing
out. In Enterprise Manager, we found a table (TAB) lock on the table that
was of mode IS that was blocking other spids. The text property of the lock
showed this lock to be on a simple reporting stored procedure, which just
did a SELECT on a couple of tables that should have only taken a second or
two. We tried to debug the problem for several minutes, but to no avail.
Finally, we killed the lock and the database problem was immediately solved.
A look at SQL Profiler (which we run continuously) showed that a query that
matched the text property of the lock and had the same pid as the lock had
been started last Tuesday and ended at roughly the same time as we killed
the lock. The index name listed with the lock in Enterprise Manager also
was strange, since the index listed was not used by the stored procedure
listed in the properties.

We have had several similar problems with our database in the past, but this
is the first time we didn't resort to just a reboot. Why would a simple
stored procedure executing a select cause such problems? Why would this
procedure be allowed to run for a week? Why would we experience no problems
until days after (the table being locked was core to almost every page in
the system and was fine until this morning)? Can the index listed in the
lock information be used to debug the problem?

An even bigger question is how to handle such a problem after it occurs?
Killing the spid seems to have caused some problems in the dotNet
application and forced me to restart the app. Is there a more graceful
method of rolling back the offending process?

Relevant Pages

  • Re: Sql Server Queue?
    ... Say I lock down this stored procedure as you suggested. ... > commit transaction ... > but here you are responsable for releasing the app lock manually. ...
    (microsoft.public.sqlserver.programming)
  • Re: Locking a row...
    ... Either add a column to the row the indicate its locked which you set from the relevant stored procedures or have a last modified column that you set everytime the row is updated. ... How do I lock and check for a locked row in a stored procedure and then in turn trap that error code in my vb app so I can then provide the appropriate processing ...
    (microsoft.public.dotnet.languages.vb)
  • Re: how to detect waiting on lock
    ... Tibor Karaszi, SQL Server MVP ... Inside a stored procedure sometimes I try to do SELECT or some other DML ... instead when I hit a lock then I detect there is a lock. ...
    (microsoft.public.sqlserver.programming)
  • Re: Blocked process mystery
    ... the spid you found did not actually lock on the tab, ... that particular spid. ... and set you query govenor cost limit accordingly, ... The text property of the lock> showed this lock to be on a simple reporting stored procedure, which just> did a SELECT on a couple of tables that should have only taken a second or> two. ...
    (microsoft.public.sqlserver.server)
  • Re: Failing READU lock
    ... WRITEU MSTITEM ON MASTR1,"HOTEKEY" ... As someone else pointed out, the regular WRITE will clear the lock, ... It is definitely a timing issue. ... and 2) New vendor software that hugely increased the number of writes ...
    (comp.databases.pick)