|
|
Posted by Erland Sommarskog on 05/23/06 00:48
Marco van de Voort (marcov@stack.nl) 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@sommarskog.se
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
Navigation:
[Reply to this message]
|