At 04:03 PM 24/11/2003 +0100, Jerome B wrote:

>Hello Helen,
>
> > >Yes. Now, I can confirm [check constraints] work just like triggers.
> >
> > They don't work just like triggers, since you cannot use a CHECK
>constraint
> > to change a value or to update or delete rows in other tables.
>
>I meant they work the same *regarding the isolation level*. Like triggers,
>but unlike decl. RI, CHECKs use the isolation level of the current tx.

RI works in the current transaction context. So, as soon as a new record
version exists, the current row and all rows dependent on it are isolated
from other transactions at the same isolation level as the current transaction.

> > You can
> > *write* a check constraint that reads a value in another table but it's a
> > practice I strongly advise against. You create additional inter-table
> > dependencies in the metadata of your tables that are not protected by
>decl.
> > RI. RI triggers are designed for this;
>
>I'm all for triggers. But is it safe here ? (see the end of the post.)
>Apparently, Alexander says "no".

I can't figure out the basis of what Alexander describes. It is contrary
to the isolation/dependency/record versioning model that I
understand. I've emailed Ann and asked her to look over the discussion and
give us one of her famous lucid commentaries. :-)

> >
> > >I had and still have a hard time with select-form CHECKs. Somehow I can't
> > >use values of the currently modified row in the WHERE clause of my
>select.
> >
> > No, because they are not in context. Triggers provide context variables.
>
>But in the following constraint
>
>ALTER TABLE PERSONNEL ADD CONSTRAINT CHK_PERSONNEL CHECK (start_date <
>end_date);
>
>"start_date" and "end_date" are in context. Right ?

Yes - because CHECK constraints are table-level constraints.

>I was hoping to use the same in-context variables in a select-form CHECK.
>Didn't succeed.

What's a "select-from CHECK"? Are you talking about a subquery on another
table? (the one I recommended not to do)?

Something like
Alter table add constraint
check(new.some_flag in (select flags.flag from flags))

>Sorry if it sounds rude. I meant, since CHECKs behave like triggers
>*regarding isolation*, I'll just concentrate on triggers which I know
>better.
>
> >
> > There is *no* dirty read in Firebird whatsoever.
>
>Sorry, I misexpressed myself. I meant : indeed, the matter may not be a
>IB/FB matter only, since other isolation levels than "read committed" (be it
> dirty read or whatever else) also exist in other SQL engines.

What matters is the isolation level of the CURRENT transaction. If your
transaction is ReadCommitted, then its ability to "win" an update conflict
depends on its RecordVersion and LockWait settings. If other transactions
already have the same row engaged in a higher level of isolation, that will
affect *your* transaction immediately its request is received. No actions
- RI, checks, homebaked triggers - will take place in that case.

The fact that other engines support dirty read is completely
irrelevant. No driver gets dirty read isolation with Firebird. ODBC,
JDBC, etc. implement dirty read as read committed.

> > This sounds like a misinterpretation of what you have read. All of the RI
> > behaviour (and check constraints too) occur in the context of the current
> > transaction. Once your transaction has reached the point of checking
> > constraints and firing triggers, it has your row and its dependent rows
> > locked. Other transactions can't interfere with your transaction's work
>if
> > you set up your transaction to block them (and set up the other
> > transactions to be blocked).
>
>I'm confused now ;-). I think I understand the locking rules but when a
>constraint is evaluated, the check only *reads* data, so there is no lock
>involved. right ? What matters is what the tx sees then. And a "read
>committed" tx might not read the same data as a "Snapshot" tx.

Correct.

>Therefore
>each *might* not evaluate equally the same constraint.

Correct.

>I also understand that is why decl. RI are evaluated against committed data
>whatever the current tx' isolation level is.

Correct.

>All this making non-declarative RI not 100% safe on dynamic tables unless
>you make sure your tx are "read committed".

I still can't get at your reasoning for this and why you think
readcommitted is a solution to some perceived problem. Readcommitted + no
record version + lock wait *does* give you a window where you can stuff up
someone else's pending work, or someone else's same settings will stuff up
yours; but if you don't want this, then use other settings!

>Do I get it right ?

I'm struggling to understand your emphasis on "dangers" from transactions
with higher isolation levels. Readcommitted is the closest Fb gets to
dirty read. Use snapshot isolation if you want a totally predictable "do
or die" condition.

> > ..although I recall myself going through
> > a phase where I was generating all RI triggers manually from an
>application
> > I wrote, thinking it was pretty hot stuff.
>
>I never got into troubles with that (regarding integrity) ?

Neither did I - that's why I thought it was such hot stuff. I could just
parse my script and generate triggers that did exactly what RI triggers
do. But, eventually, I asked myself "What for?" (and I think Ann asked me
the same question in an old posting around 1998). With decl. RI I can do
the same thing and do it as part of my script development process. If I
don't like it, I have only one constraint to drop.

Helen