--- In firebird-support@yahoogroups.com, "jeff_j_dunlap"
<jeff_j_dunlap@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> >
> > Are you intending on sharing the single connection/transaction
> > instance between your web users? If so, you are going to
> > encounter some thread-safety issues or some serialisation
> > bottlenecks. In simple terms, what happens to user 2 if when
> > they connect to your web application, user 1 is already using
> > the connection?
>
> My plans are to build a small vector of structures, each structure
> containing an instance of an open database and a boolean 'is_busy'

flag.

Presumably your web application would need to run through the vector
until it found an available instance. Having two vectors, one for
available, one for in use may be faster, but probably a non issue.
Just don't forget to protect your setting of is_busy flag with a
critical section so that two simultaneous users don't end up with the
same connection.

>
> Each structure can contain it's own verify_connection() & reconnect()
> routines to ensure that something didn't happen to the connection like
> FB server shutdown, or some other connection failure.

Seems reasonable.

>
> > Anyway, back to Firebird; I would imagine the overhead of
> > starting and committing a transaction is significantly lower
> > than the overhead of waiting for an available connection from
> > the pool of 1.
>
> > You should do some profiling to see the time taken to start
> > and commit a read only transaction to see whether you are
> > fighting a problem that only exists in your mind.
>
> In my first FB app, I was unhappy with the delay caused by the way I
> would create new db instances and connect and disconnect for each
> transaction. Doing so spikes the processor, uses more memory, and
> causes disk trashing (because data buffering is lost). Running my own
> recent tests, I was able to reproduce this and am fully convinced that
> connect() is not a cheap operation--too expensive to use in a web
> application.
>
> Some FB users on linux have told me that buffering is maintained after
> disconnect() but I'm on a windows server and buffering is lost after
> disconnect().

It matters here whether you use Superserver or Classic Server.
Superserver IIRC releases its buffer after every connection to a
database is closed. It may be as simple as creating another connection
from a service on your server that serves to hold the database open.

I am also very surprised the OS cache is not helping? It is certainly
noticeable on mine.

>
> Now, I am seriously impressed with Firebird's performance on db
> connections that remain open and really wish I would have known this
> before!
>
> As for a read-only transaction, you mentioned commit and I'm a bit
> confused. In my tests, I have not been committing read-only
> transactions since I thought that commit was for write operations. Is
> commit required for read-only transactions? If so, why?

All database activity happens within the context of a transaction.
Some databases dumb this down for you. Firebird uses MGA, which allows
you to have a stable snapshot (if that is what you want) without
blocking writers. In a snapshot transaction, you see the database as
it was at the moment your transaction started. You do not see new
records, you see the old values of updated records and deleted records.

In order to provide this functionality, Firebird must keep track of
what transactions are currently active. If any of those transactions
could potentially be "interested" in an old record version, that old
record version can not be removed (garbage collected).

If too much garbage collects in your database, it will choke the
performance, so there is a balancing act to be done. At some point,
every transaction should be "ended" so the garbage collector can do
its thing. Ended means either rolled back or committed.

In terms of a read only transaction, there is no (logical) difference
between a rollback and a commit, both end the transaction.

Now a read-committed read-only transaction is a special case.
Read-committed means that it always fetches the most recent committed
record version (note difference to snapshot). A read-committed
read-only transaction doesn't need to worry about old record versions
and therefore does not inhibit garbage collection and could I suppose
therefore be left open indefinitely.

All transactions need to be ended eventually (even if it is an
implicit rollback caused by a disconnection).

Adam