| Subject | Re: [firebird-support] ASP.NET Commit without rollback available? |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-04-03T00:20:39Z |
At 09:27 AM 3/04/2009, you wrote:
Your subject "Commit without rollback available?" indicates a misapprehension of what commit and rollback are. All updates and deletions, whether committed or rolled back, leave "garbage" in the database. That "garbage" (known as "old record versions" remains until it is tagged as eligible for garbage collection. Once the GC is done, the space occupied by those old record versions is available to store more records (including more old record versions).
Break big, bulky jobs into transactions of about 8K - 10K records; and avoid updating or deleting records that are still awaiting commit or rollback in the same transaction.
NO_AUTO_UNDO has nothing to do with your problem of this exorbitant use of disk. It is an in-memory structure that lives as long as the transaction.
Once you get your head around the management of your transactions, you will find you do not see such disk growth. Bear in mind that, contrary to your belief, Firebird doesn't create "files" inside a database. It requests *pages* from the file system as required, when it can't find enough reusable space inside the databases. Although it never mixes data from different tables in the same page, the data pages that belong to a particular table could be anywhere on the disk. If you are using a large page size - 8KB or 16 KB then, potentially, the engine could be asking for a chunk of disk, perhaps even up to 32KB, *each time* you update or insert a record. This is obviously not desirable.
./heLen
>Is it possible to commit a transaction without having a copy created in the database of the old information?No. This is multi-versioning, which is fundamental to concurrency in Firebird (along with several imitators, nowadays).
Your subject "Commit without rollback available?" indicates a misapprehension of what commit and rollback are. All updates and deletions, whether committed or rolled back, leave "garbage" in the database. That "garbage" (known as "old record versions" remains until it is tagged as eligible for garbage collection. Once the GC is done, the space occupied by those old record versions is available to store more records (including more old record versions).
>I am trying to update large files; doing so causes the database to grow sometimes by over 10x the size of the file I'm appending. I've found this is because of the rollback feature. I've tried setting FbTransactionOptions.NoAutoUndo; however, that doesn't seem to do anything.Provided your applications are taking proper care of transactions, garbage collection will occur in good order and re-usable space will be available. Your observations suggest that your applications (not just this one) are not taking care of committing or rolling back transactions very well.
>Here is an example of the code I'm working with:Once you understand what you need to do, take your client-related questions to the appropriate forum and ask what you ought to do to keep your database in good, clean order.
Break big, bulky jobs into transactions of about 8K - 10K records; and avoid updating or deleting records that are still awaiting commit or rollback in the same transaction.
NO_AUTO_UNDO has nothing to do with your problem of this exorbitant use of disk. It is an in-memory structure that lives as long as the transaction.
Once you get your head around the management of your transactions, you will find you do not see such disk growth. Bear in mind that, contrary to your belief, Firebird doesn't create "files" inside a database. It requests *pages* from the file system as required, when it can't find enough reusable space inside the databases. Although it never mixes data from different tables in the same page, the data pages that belong to a particular table could be anywhere on the disk. If you are using a large page size - 8KB or 16 KB then, potentially, the engine could be asking for a chunk of disk, perhaps even up to 32KB, *each time* you update or insert a record. This is obviously not desirable.
./heLen