Subject Re: [firebird-support] "Hanging connections" on FB/1.5 SS
Author Helen Borrie
At 10:19 PM 4/10/2005 +0200, you wrote:

>This is Fb/1.5.2 SS running on Win2000 server (WI-V6.3.0.4306 Firebird
>1.5). The application is developed with Delphi/6 and IBO/4.2. The size
>of the db is approx. 1GB.
>We have observed connections to the database that stay there long after the
>users have disconnected

Disconnecting is one thing...

>(or lost the connection).

Losing the connection is another

>What do we do to get rid of them?

In the case of a user-initiated disconnect, the actual detach won't occur
until all outstanding transactions complete. In the IBO case, your
transaction object's destructor will try to do this if your disconnect
handler doesn't do it explicitly. In cases where you have a SELECT
statement with a large number of unfetched records, it might take quite a
long time to finish fetching the records right up to EOF, before the
various destructors can kick in.

In the case of lost connections, there is no application intitiating the
wind-down, so there is a timeout period that is conditioned by the
keep-alive setting for network sockets. If I recall correctly, the default
on Win2K is 2 hours (7,200,000 milliseconds). Unfortunately, Win2K does
store this default in the Registry so, if you want to reduce it, you'll
need to ADD a key to the Registry. Here are the details:

System Key:
Value Name: KeepAliveTime, KeepAliveInterval
Data Type: REG_DWORD (DWORD Value)

The KeepAliveTime should be the number of milliseconds that should elapse
before the server should consider an idle connection to be "dead" and start
sending keep-alive packets. The KeepAliveInterval sets (in ms) the
frequency of the keep-alive packets.

>Or, rather, what do we do to avoid them in the first place?
>Some users connect over the internet and these are sometimes not terminated
>properly (lost connection, impatient user, ...). However, it seems that
>also connections from the LAN can remain (seemingly connected).

For the cases where the LAN connections seem slow to detach, you could put
a timer on the AfterDisconnect event that tests the Connected property
until it returns False and then logs the elapsed time somewhere. If
nothing else, this could help to identify which applications (or user
behaviours) are prone to these slow wind-downs. You might find, for
example, that you have certain users who use the Exit button of your app to
end a reporting routine when it gets hung up by a printer error, or
something along those lines.

>We observe this with the IB_SQL tool: "List users" shows a number of users
>even though SYSDBA is the only one actually connected. Some usernames show
>up more than once in the list.

That tool accesses the isc_database_info structure, which (when run on the
host machine) shows all the live connections to the database. If you have
multiple users/apps making attachments with the same user name, then you
will see that many repeats of the same user name.

>Moreover, using gfix the shutdown the database seemed to not release all
>connections - e.g. it was not possible to rename the database file.

Shutdown doesn't close the database file, it prevents new connections
and/or new transactions, depending on how you specified the shutdown. The
database won't go into a shutdown state if anything is still happening when
the specified timeout has elapsed. So, even if you tell all the users to
disconnect, it could well be that some connections are still winding down
by the time the timeout period ends.

>IIRC, this started to happen after a total "power down" where the UPS
>didn't close down the server gracefully. Before that, for quite some time,
>we observed no traces of left-over connections.


>Aage J.
>Visit and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>Also search the knowledgebases at
>Yahoo! Groups Links