> Helen, I considered placing the commit outside of the loop, but because of
> my limited understanding of what the server does in the background, I
> thought that the transaction would consume plenty resources if this process
> was run over a couple of hundred thousand records.

The way Firebird works, is to add new records when ever a
change is made to an old one. This is where OAT comes in.
The oldest active transaction is one that has not been
comitted, but has changes pending to commit. The new record
is flagged with the transaction number, much in the same way
as I suggested adding an ADDING flag. A commit deletes all
the older copies that are not in use, while a rollback just
deletes the copy with the same transaction number. In order
for a commit to work, all the other copies of the same
record with a lower transaction number must be free to be
deleted. If a change is pending - deadlock!

> The way I understand it, is that the server will log all changes at
> transaction start, and release these resources when the transaction is
> committed or rolled back. It made sense to see the member and all his
> dependents as a unit of work needed to be handled together, not all members
> collectively.

Units of work depend on what you are trying to do. If you
add 100,000 records, then nobody else can use them until you
commit that transaction. Similarly if you update the payment
date on all members within the one transaction, nobody else
can update those records until you finish.

In a multi-user system it depends on how many people are
trying to make changes that determines where the line should
be drawn. If several people are all updating members
information, then you do not want something running that
prevents access, such as a transaction covering all records.
On the other hand, you may not want 100,000 new records to
be visible until you are finished.

I hope I haven't simplified this too much - the detail is
not accurate, but it should give you the idea.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services