--- In firebird-support@yahoogroups.com, "Bill Oliver"
<bill.oliver@...> wrote:

>
> Thanks Ann, Daniel, for your reponses! Very helpful.
>
> Ann, your SQL query for the system pages was quite useful, and served
> to verify that I wasn't growing excessive TIP pages.
>
> I had the user run the test again, and I see during the test cycle,
> the test table grows excessively, and takes up 4891 pages on disk!
>
> IDENTITY_GROUP_HIERARCHY (162)
> Primary pointer page: 333, Index root page: 334
> Average record length: 0.03, total records: 325287
> Average version length: 21.45, total versions: 324898, max
> versions: 1
> Data pages: 4891, data page slots: 4891, average fill: 91%
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 5
> 80 - 99% = 4885
>
> When the test is complete and the database settles down the number of
> data pages returns to 21:
>
> IDENTITY_GROUP_HIERARCHY (162)
> Primary pointer page: 333, Index root page: 334
> Average record length: 17.24, total records: 481
> Average version length: 21.45, total versions: 92, max versions: 1
> Data pages: 21, data page slots: 1842, average fill: 28%
> Fill distribution:
> 0 - 19% = 9
> 20 - 39% = 7
> 40 - 59% = 4
> 60 - 79% = 0
> 80 - 99% = 1
>
> Of course, since the data pages are never released, the database file
> size grows.
>
> My user says:
>
> All transactions that are actually changing data, do a commit and
> succeed. All hibernate operations are taking place within a database
> transaction. This transaction is always rolled back. These
> transactions will be across many tables, not just the 2 we are
> focusing on.
>
> So, now I am looking at the test case, to see if the rollback could be
> replaced with a delete operation instead. I think this would reduce
> the number of back versions created in this table.
>
> Also, I'm not really clear as to how/when the back versions get
> purged, so I will do some more analysis there.
>

Do some research on that before spending time on the code.

Firebird maintains an undo log (for about 5000 operations from
memory). It uses this undo log when a 'nice' rollback occurs. When a
client application issues a rollback command, Firebird reverses each
operation within the transaction, and then 'commits' the transaction
(which has now effectively done nothing). If the rollback is nasty,
such as someone pulling the power cord and the server noticing the
connection is dead, then the transaction is flagged as rolled back,
and it is left for someone else to clean up.

What you are proposing to do in your client software is precisely this
same thing, except the reversing you propose is driven by your client
application. I would strongly advise against such logic, because you
may make a mistake that means that you reverse out incorrect
(neglecting effects of triggers etc). I am guessing it would actually
** add ** to the number of back versions and worsen your issue.

The back versions are flagged as available to be used as soon as the
garbage collection is able to, but this can only happen if no
transaction could possibly be interested in this value (eg, a
lingering active transactions).

I would advise you to not get concerned with the initial growth of the
database. This is normal and expected. It will hit an equilibrium
eventually. If you were to graph it, it would grow sharply at first
then ease into a gentle gradient that roughly follows the amount of
additional data.

If you never reach that equilibrium, then it just means that your
transactions are not committing (or commit retaining) which means that
the garbage collection can not do its clean up of the back versions,
which means that when it comes to storing the next item, the database
file must grow.

Adam