Subject Re: [firebird-support] Error on FB 1.5.5 involving "hung" connections
Author Thomas Steinmaurer
> Have been using 1.5.5 in a Client Server environment successfully
> for over 450 clients, with hundreds of users, in hundreds
> locations. However, we are starting to use it with ColdFusion in a
> web environment and have been getting about 12 hits to the database
> per second.
> Now, we have been getting some "hung" transactions periodically that
> leave the database as being in use by more than one user when in
> fact it has been renamed, taken off line and brought back on line.
> Sometimes it shows on IBExpert as Firebird User Thread and others it
> shows the user as (SQL Server) in addition to the SYSDBA who is
> looking at it using IBExpert.
> Together with this, and maybe the same problem, we start with a
> small database (say 20 MB) and over the weekend, it may grow to some
> 666 MB!! Obviously, we are (or ColdFusion is) leaving some
> transactions uncommitted and the size grows incredibly.

Uncommitted or committed with a soft commit (= commit retaining).

A soft commit might be used behind the scenes when using some kind of
AutoCommit mode.

> We are looking slowly through the ColdFusion pages to make sure we
> do not leave transactions uncommitted. But, what can cause that
> user getting stuck as SQL Server or Firebird User Thread?

The (SQL Server) user indicates that a Sweep is running. If you didn't
start a Sweep manually, then it got started automatically. This is
defined via the Sweep interval which kicks in when OAT-OIT > Sweep
interval. A stuck OIT can cause an automatic Sweep periodically. The
reasons for a stuck OIT can be:

- Long-running transaction which has been left open (does not apply when
it's a read-only read committed transaction)
- A large data manipulation transaction got rolled back and not committed
- Soft commits without doing hard commits from time to time

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions