AWH> Ah yes, the old question of whether you care about accuracy or
AWH> performance.

What I was referring to, actually, was another instance. I've been
looking into disk performance - both in reliability and speed. It
turns out that many disks can be set to report that the write is
complete when the data is in the disk cache but before it goes to
oxide. Some disks report on the state of that setting. Some lie
about the state of the setting when they report.

And in benchmarks of databases, you have to look very closely to
be sure that commits actually go to disk and not rest in the database
or operating system cache.

So that was what was in my mind when I read about avoiding OAT
problems by running reports in read-committed / read only mode.
That mode is good for casual browsing, I guess.

>
> Here what it's all about:
>
> A: I have some long running reports. I also have a huge gap (or gab)
> between OIT and NT.

AWH> Unless the report takes hours to run or requires
AWH> user interaction that could stall its execution, it's not the
AWH> cause of the stalled OAT.


We should have asked how long were his long running reports - an
hour should be OK, 6 hours isn't. If the reports take 6 hours,
then lets see if there's a way to shorten them or run them on
a copy of the database.

AWH> you're right, as long as no piece of data is related to any other
AWH> piece of data, there's on problem. If, however, you have foreign
AWH key relationships, a read-committed transaction may report
AWH> referencing records without referenced records.

>
> Do you mean in reality or in some lab environment? I fail to see how
> you could possibly commit such a relational integrity violation.

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

AWH> Can you give me an example of a report
AWH> where concurrency isn't good enough? I know about update anomalies,
AWH> but what are the read-only anomalies?

>
> Not read anomalies; I was thinking about phantoms, but actually I
> haven't checked whether Firebird's Concurrency level allows them (as
> does Repeatable Read in other DBs) or not.

We've started calling the mode Consistent Read in Falcon/MySQL because
it is different from the standard "Repeatable" Read in that Firebird /
InterBase / Netfrastructure / Falcon all give consistent reads without
phantoms. There are anomalies that occur with some write operations
that make concurrency transactions less that serializable.

> Anyway, I can imagine
> situations in which Consistency would be required (as otherwise it
> wouldn't exist), but they are not exactly "reports" although they
> might imply printing.

Consistency mode is important if you require full serializability.
Specifically, Firebird produces non-serializable results in these
cases

Create table t1 (f1 integer);

T1: insert into t1 (f1) select count (*) from t1;
T2: insert into t1 (f1) select count (*) from t1;
T1: insert into t1 (f1) select count (*) from t1;
T2: insert into t1 (f1) select count (*) from t1;
T1: insert into t1 (f1) select count (*) from t1;
T2: insert into t1 (f1) select count (*) from t1;
T1: commit;
T2: commit;
T3: insert into t1 (f1) select count (*) from t1;
T3: select * from t1;
1
1
2
2
3
3
7

When the result should have been a deadlock issued to
T2 or T1.

Similarly

Create table t1 (f1 integer, f2 integer);
Create table t2 (f1 integer, f2 integer);

Insert into t1 (f1, f2) values (1, 100);
Insert into t2 (f1, f2) values (1, 10);


T1: update t1 set t1.f2 = (select max (t2.f2)
from t2 where t2.f1 = t1.f1);
T2: update t2 set t2.f2 = (select max (t1.f2)
from t1 where t1.f1 = t2.f1);

Those are the anomalies I'm aware of.


Regards,


Ann