This has been reported a few times but no resolutions reported and I wondered if anyone had got to the bottom of it?
We occasionally have database connections where the client application has been disconnected but the connection is still active on the server and cannot be dropped. Attempting to drop the connection either through Sybase Central or using DROP CONNECTION has no effect (and no error is reported). It makes no difference when the client machine is rebooted.
The connection appears in the list of active connection in Sybase Central and is consuming CPU time (slightly less than a full processor's worth - though it varies). The statement reported as the last statement is a fairly straightforward and is being executed by the system for other connections thousands of times per hour without problem. The connection is not shown as being blocked.
The server version is 10.0.1.4239
We have seen customer reports in the past (known to exist 11.0.1) of the bug CR #594568 leaving 'dead connections' around that can no longer be dropped. (e.g. We started a HashFilter/parallelized intra-query task and 'accidentally' left around some worker threads afterwards).
Notably, CR #594568 was only the first fix for a class of 'HashFilter' problems - there were additional issues (in particular, CR #620095 ) fixed on later builds/versions (including 10.0.1/12.0.1). I would recommend seeing if this 'zombie connection' behaviour still occurs after these fixes/builds:
We have now diagnosed more instances of a 'hung connection' problem from other customer reports. The updated list for parallelized HashFilter problems (that may leave Exchange worker tasks 'abandoned' and 'undroppable') now includes the following issues/builds:
The list of associated parallelized 'HashFilter' issues (server hang/crashes/incorrect results) include:
Note: CR #702733 was identified after the End-of-Life date for SQL Anywhere 10.0.x. As such, this fix was not backported to this codeline and it remains a possibility for 10.0.x builds to encounter undroppable internal connections in very rare circumstances. If this is a concern for you, please upgrade to a later version of SQL Anywhere (11.0.1 / 12.0.1), which includes this fix. Setting the connection option 'MAX_QUERY_TASKS' to '1' or restarting the database server is a workaround for the issue in 10.0.1.
My guess would be that the connection has a huge transaction to rollback. The communications link may be gone but the server must actually rollback all uncommitted operations performed by that connection before the connection will disappear from the list reported by the server.
answered 06 Jul '11, 09:46