| Subject | Re: Isolation level in CHECKs and triggers |
|---|---|
| Author | Alexander V.Nevsky |
| Post date | 2003-11-24T20:21:50Z |
--- In firebird-support@yahoogroups.com, "Jerome Bouvattier"
_most pessimistic mode_, for example, in read_commited:
a) if there is uncommited row in master table, FK will not allow you
to insert referenced for it row into detail (still like on transaction
context)
b) if row in master on which you want to reference inserting new
detail is deleted but not yet commited in another transaction, FK will
not allow your insert though you can read mentioned row in master! Out
of transaction context.
in concurrency (snapshot)
a) if there is row in master table inserted after you started
transaction or was not commited before you started transaction, FK
will not allow you to insert referenced for it row into detail,
regardless of was it commited or not (still like on transaction
context, you can't see in snapshot changes made after it's start)
b) if row in master on which you want to reference inserting new
detail is deleted in another transaction after you started
transaction or deleted and not commited before you started
transaction, FK will not allow your insert regardless of was it
commited or not. Out of transaction context, snapshot can't see
mentioned changes.
So, FK guarantee integrity, neverthelles of opportunity if
concurrent changes will be rolled back, wisely lurking out of bounds
of transaction context in case if changes can only potentially lead to
inconsistence. Trigger, which operates in transaction context, can't
do this.
Best regards,
Alexander.
> I'm confused now ;-). I think I understand the locking rules butwhen a
> constraint is evaluated, the check only *reads* data, so there is nolock
> involved. right ? What matters is what the tx sees then. And a "readTherefore
> committed" tx might not read the same data as a "Snapshot" tx.
> each *might* not evaluate equally the same constraint.committed data
> I also understand that is why decl. RI are evaluated against
> whatever the current tx' isolation level is.unless
> All this making non-declarative RI not 100% safe on dynamic tables
> you make sure your tx are "read committed".No :) Declarative RI is performed out of transaction context in
>
> Do I get it right ?
_most pessimistic mode_, for example, in read_commited:
a) if there is uncommited row in master table, FK will not allow you
to insert referenced for it row into detail (still like on transaction
context)
b) if row in master on which you want to reference inserting new
detail is deleted but not yet commited in another transaction, FK will
not allow your insert though you can read mentioned row in master! Out
of transaction context.
in concurrency (snapshot)
a) if there is row in master table inserted after you started
transaction or was not commited before you started transaction, FK
will not allow you to insert referenced for it row into detail,
regardless of was it commited or not (still like on transaction
context, you can't see in snapshot changes made after it's start)
b) if row in master on which you want to reference inserting new
detail is deleted in another transaction after you started
transaction or deleted and not commited before you started
transaction, FK will not allow your insert regardless of was it
commited or not. Out of transaction context, snapshot can't see
mentioned changes.
So, FK guarantee integrity, neverthelles of opportunity if
concurrent changes will be rolled back, wisely lurking out of bounds
of transaction context in case if changes can only potentially lead to
inconsistence. Trigger, which operates in transaction context, can't
do this.
Best regards,
Alexander.