At 02:49 PM 22/01/2009, you wrote:

>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.

Can't visualise this at all. From your other messages, I'm not even sure you understand the difference between a database and a table; or even that a database accommodates many tables (and other objects, besides). I'm not seeing the point of having multiple databases, either....but maybe you're hosting some software product for multiple customers, that operates one database for each customer...or something...

Why would you want an "is_busy" flag? Is that some mechanism to lock out multiple users? If so, why are you interested in using a transactional database engine?

>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.

Well, that's a useful idea for any application...but, if you're doing this right, the database connection won't be over the internet. The data access layer of a web application lives on the web server. Connections to databases are done by the webserver app on behalf of *its* clients, within a LAN. It's even feasible not to use a network protocol between the webapp and the database server at all, if the conditions suit the methodology.

>> 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.

Connecting is a quite separate thing from starting a transaction. An application session typically makes one connection to a database and, thereafter, performs each task within its own transaction. Within a transaction can be one or many statements executed.

Of course continually connecting and disconnecting to repeat the same operation over and over is going to thrash disk and everything else. Don't write applications like that.

>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().

If by "buffering" you mean the database cache, then these assumptions are misguided. The database cache lives as long as the firebird server process has connections. Superserver threads multiple connections and has a shared db cache that stays alive as long as there is at least one connection. Connections to a Classic server are one process instance per connection. Each process instance gets its own cache. Disconnecting ends the process and dissolves the cache.

But apparently your "design concept" is to have one database for each user and not to employ multi-user concurrency at all. So we're back to "what's the point?"

>Now, I am seriously impressed with Firebird's performance on db
>connections that remain open and really wish I would have known this
>before!

What's puzzling me is where you're getting your rather odd ideas about how things work. Have you ever studied the Quick Start guides or any of the other documentation?

>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?

Yes. A transaction, regardless of mode, has a beginning and an end. It begins with TRANSACTION START and ends with either COMMIT or ROLLBACK. Period. Adam already mentioned that COMMIT uses less resources than ROLLBACK - so commit read-only transactions if you want to conserve a bit of resource.

>If so, why?

A transaction puts a fence around the state of the database at the moment the transaction begins. A read-only transaction in Read Committed isolation can refresh its view of database state (if you rerun its queries) and give you an updated view of db state without needing to commit. Any other transactions need to be committed (to end them) and a new one started to get an updated view. Transaction isolation is the *thing* that permits multi-user concurrency.

A read-only transaction in Concurrency (Snapshot) isolation is no good to you. Not only do you have to keep committing it and restarting a new one to update your client's view of database state, it also (between commits) ties up garbage created by any other transactions you're running that perform updates or deletes. It also has the ability to prevent inserts to some sets. Inhibiting garbage collection is something you don't want to do on purpose with Firebird's multi-versioning architecture.

./heLen