--- In firebird-support@yahoogroups.com, Nando Dessena <nando@...> wrote:

>
> Helen, Adam,
>
> >> Read only: yes. But !!! Read Committed for reports is terrible
> >> !!! Reports should always use a snapshot transaction (Concurrency
> >> isolation) to avoid getting discrepancies due to database state
> >> changing during execution!
>
> A> Amen!
>
> Had you bothered to read the rest of the thread <g> you would have
> known that the suggestion to make sure the read-only flag was set
> stems from the necessity to help the old* transaction counters moving
> forward, which was the subject of the discussion.

I am talking about the read-committed vs snapshot, not whether a
transaction is read-only. I do know the advice was to try a read-only
read committed transaction for the report because that does not block OAT.

> To the best of my
> knowledge Concurrency, which would otherwise be the first choice for a
> report (and well-designed, disconnected interactive screens), doesn't
> help in this case. BTW, not all reports are affected by the
> discrepancy problems on Concurrency, and for some reports Concurrency
> isn't even enough anyway. One assumes that if they worked with Read
> Committed so far then it must be OK for the OP.

There is a difference between works ok and never noticed the problem.

When testing, the report is probably run through a nice unit test
which proves that the totals of the report match the sum of the
records, or the % figures all add to 100% taking into account rounding
errors. Unfortunately, these tests usually fail to include
asynchronous modification of reported data in different transactions
between the reporting transaction start and completion like you get in
the real world. In the absense of other simultaneous transactions,
there is of course no difference between read-committed and snapshot,
and the correct result would be seen.

That is a very bad bug to have, because it is very hard to duplicate.
The customer will point it out and you repeat their exact steps and it
works perfectly.

If snapshot is really a problem, then another approach would be to
backup the database to a temporary location, and report against the
second temporary database, thus removing the OAT issue altogether. The
speed and simplicity of NBackup makes this a very good option, and
storage space usually isn't an issue. It also allows you to run the
report on a different machine for performance reasons if you like.

Adam