Ann,
changing the subject was a good idea, as the OP must have considered
solved the problem at hand and moved forward.

A> So that was what was in my mind when I read about avoiding OAT
A> problems by running reports in read-committed / read only mode.

It seems to me that the OP's problem was actually the amount of
garbage piling up due to long-lasting [RC] transactions and the gc
thread stealing precious CPU cycles and disk bandwidth at wrong/peak
times.

A> That mode is good for casual browsing, I guess.

Most interactive [made with Delphi, mainly, but I know it applies to
other environments as well] applications I've seen to date use a
scheme such as:

1. Start transaction
2. Read some data
3. Refresh/goto 2 as required
4. Commit

or

1. Start transaction
2. Read some data
3. Change some data
4. Refresh/goto 2 as required
5. Commit

which tends to favour transactions that are longer than they need to
be, and that requires RC isolation if one wants fresh data when
refreshing the sets (which usually is the case). The most advanced
connectivity libraries go to great lengths to minimize the impact of
said schemes on Firebird's delicate MGA-based environment. Other
applications, though (including 100% of the multi-tier applications)
use a more efficient and simpler scheme:

1. Start transaction
2. Read some data
3. Commit
4. Refresh/goto 1 as required

or

1. Start transaction
2. Read some data
3. Commit
4. Change some data
5. Start transaction/send changes/commit
6. Refresh/goto 1 or 4 as required.

which works well with Concurrency isolation and uses shorter
transactions. Beats me why the majority of people tend to use the
first scheme even nowadays and for new applications; I guess Firebird
wouldn't need to support RC isolation at all if it wasn't for these
cases.

I hope this clarifies the reasons why I don't think I was suggesting
anyone to use RC when Concurrency was available.

A> We should have asked how long were his long running reports - an
A> hour should be OK, 6 hours isn't.

Wouldn't that entirely depend on what were the other users doing at
the time? Anyway your point is clear.

A> The data is accurately stored, but inaccurately read if there are
A> changes between the time one table is read and the other. For
A> example, if you look for customers first, the go back and read
A> orders, you may find orders that don't match any of the customers
A> you read previously.

OK, this is the classic nonrepeatable read problem. Your example
doesn't occurr very frequently (or doesn't occurr usually) because
reads are generally done by following the links, though joins and
master/detail arrangements. A few reports read the same data twice,
and among those a few care if the read is not repeatable. That's why
people haven't been dumping RDBMSs that wouldn't allow higher
isolation levels in the past, I guess. This I meant when talking about
"lab environment". Something that lives in Theory (which doesn't mean
it shouldn't be well understood, so your clarification is welcome).

A> We've started calling the mode Consistent Read in Falcon/MySQL

<SNIP>

Makes a lot of sense. Thanks for the insight. Unfortunately,
database-agnostic tools and libraries need to map Firebird's
transaction settings to something, and Repeatable Read has always
been the closest something to Concurrency. Having to work with a
number of different DBs I keep forgetting nunaces like the phantoms
issue.

Ciao
--
Nando Dessena
======================================================
I support Firebird, I am a Firebird Foundation member!
Join today at http://www.firebirdsql.org/ff/foundation
======================================================