At 04:16 AM 20/04/2005 +0000, you wrote:

>Dear all
>
>Can anyone please tell me when the firebird will keep the record
>version?

Your question might mean several things. Maybe this will answer all of
them. :-))

When a user makes a successful request to update a row, that new version is
written to the database, together with the transaction ID of the
transaction that did it. If possible, it is written to the same disk
location as the last-committed version (the version that it will replace,
if committed). An image of the last-committed version is written,
also. It is known as the "delta". Any other transactions that can
currently see the last committed version will now refer to the delta. Now
that the delta exists, no other transactions will succeed in a request to
update or delete the row that the delta refers to. The client will receive
an exception if it tries. The exact exception depends on the transaction
isolation and other parameters of that transaction.


In this state, the transaction that wrote the new (but yet uncommitted)
version sees only the new version. It cannot see the delta.

>if in one program(only one user), one query have select from a table A,
>and other query update the table A and commit, then firebird will keep
>the record version?

Even a single user may be running operations in more than one
transaction. It is more useful to consider this in terms of one
transaction vs another transaction, rather than one user vs another user.

SELECTs do not cause new record versions to be created. If the select
query and the update query are both in a single transaction, committing the
update query will make the output of the SELECT out-of-date. The user will
have to start a new transaction and run the SELECT query again, to bring an
updated set to the client.

If you user was running the SELECT in one transaction and ran the UPDATE in
another, then the transaction running the SELECT will "know" of the change
committed by the other transaction only if the SELECT transaction is in
Read Committed isolation. In this condition, the visible output set is
invalid, just as before. However, the SELECT statement's transaction does
not have to be committed in order to refresh it. The set can be "closed"
(a client-side operation) and the query re-run to update the set. The
delta becomes available for garbage collection.

If the SELECT statement's transaction is in Concurrency
(Snapshot/Repeatable Read) isolation then the output set remains valid *for
that transaction". Any request in that transaction to perform an update or
delete on the old version will fail, because this transaction is referring
now to the delta, not to the most recently committed version.

>Or in what occasion it will keep the record version?

Obviously, the delta cannot be released for garbage collection until this
transaction, and any others that are referring to it, are all committed or
rolled back.

>Will it ever release the record version?

The delta will be released for GC once every transaction that referred to
it is either committed or rolled back.

In the case where the transaction that created the *new* record version is
rolled back, the delta is restored as the latest committed version, and the
new record version becomes eligible for garbage collection.

.heLen