Re: eternal lock?

Marco van de Voort (marcov@xxxxxxxx) writes:
The problem is that sometimes, after a cascade of query timeouts
(recorded by the apps in the eventlog, cause is the commandtime set on
all components), the whole applications seems to stop responding.
Restarting the apps doesn't solve the problem, rebooting the application
server does, which leads me to believe the problem is in MDAC on the app
server? The app server has an own unused sql server instance (used in
migrations) btw.

Have you examined blocking?

With this superficial information about the system it is difficult to
say for sure, but it does smell of a well-known gotcha (been there, done
that myself).

To wit, if a query times out, and there is a transaction in progress,
the transaction is not rolled back automatically. It is irrelevant
whether the transaction was started prior to the submission of the
query batch, or started within the query batch that timed out.

The application must handle this by submitting

IF @@trancount > 0 ROLLBACK TRANSACTION

in case of a query timeout.

If the application fails to observe this, the result is chaos.
Transactions never commits, which means that processes keeps on
acquiring more and more locks, and you get blocking galore. And
when you finally restart something, you lose all the updates...

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: SQLEXEC(liHandle, "BEGIN TRANSACTION") makes my apps hang.
    ... BEGIN TRANSACTION should never block. ... should tell you if the app connection is blocked behind some other ... the apps would go hang. ... > tried to trace the SQL Server, but the log entry is like French to me. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Windows Completely Locks Up!! FREEZE
    ... failing to start/run the Distributed Transaction Coordinator. ... Error Specifics: ... The Business Contact Manager SQL Server Startup Service service terminated ... The service did not respond to the start or control request in a timely ...
    (microsoft.public.windowsxp.general)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... transaction could still be doomed at some point, ... With the current functionality in SQL Server ... you should roll back the transaction in your error handler. ... global cursor in a stored procedure. ...
    (microsoft.public.sqlserver.programming)