Subject Re: [firebird-support] Re: Why it's soo slow ? it's just a very simple select ...
Author Ann Harrison
On Fri, Mar 9, 2012 at 8:35 AM, nathanelrick <nathanelrick@...> wrote:

> > In one of your mails you wrote you use a transaction pool. I personally
> do
> > not understand the use of that, since transactions with Firebird should
> be
> > as short as possible. If you keep transactions open for a long time,
> > performance can degrade over time with increasing users and load because
> of
> > versioning in the database.
> good memory you have :)
> that simple, i make a pool of "read only" transaction and this for 2
> raisons :
> 1/ opening and closing a read only transaction cost a lot of time,
> especially on heavy multi user database (probably because of some "mutex"
> to protect some section in the code i imagine)

The problem is that each transaction start - read write or read only -
normally requires a write to the database header page and each commit or
rollback requires a write to the transaction inventory page. The first
write sets the transaction id and the second changes the transaction from
active so the garbage collector knows not to save old versions for that
transaction. Those pages must be written when the transaction ends. In
Classic, those change cause the header page and active transaction
inventory page to go from one clients memory to disk to the next clients
memory at a huge rate.

Do be aware that there's a problem with read-only, read-committed
transactions and blobs. That problem occurs when you read a row with a
blob in it, then go to get the blob, and between those two events someone
made a change to the row that changed (or deleted) the blob, committed that
change and some other transaction found the old record version and old blob
id and garbage collected them. You get a blob not found error. Easy
enough to handle - reread the record and get the new blob value - or
discover that the record is gone and pretend that your transaction got
there after the record was deleted.

Good luck,



[Non-text portions of this message have been removed]