Because in some (or most) cases you want an immediate rejection to
rollback and release resources and locks ASAP.
Suppose you have a giant transaction and the first sentence fail on a
constraint you know it's unrelated with the rest of transaction,
then the db engine will have to carry out all the job (and allocating
all resources) for nothing.
But wouldn't it be a more appropriate place to decide that when starting
a transaction.
You see when I create a referential constraint (or any other constraint
for that matter)
I don't know if someone wants to have it deferred or not.
But when someone starts his transaction he knows if he is going to break
some consistency rules or not.
So wouldn't it be better to make deferring of the constraint checks a
setting in a connection like this.
SET CHECKS_DEFERRED ON;
SET AUTOCOMMIT OFF;
INSERT ...
INSERT ...
INSERT ...
WHATEVER ...
COMMIT;
- Rami
What would be the problem with that?
As I can see, computational cost and resources.
For example NOT NULL constraint is checked before even the BEFORE
trigger is invoked.
Sometimes this can cause problems because the trigger would provide
the value but now it can't.
This is a different issue and in this case I can agree with you.
Don't remember how other databases handle this case.
I am just wondering what bad consequences would it have to do all the
constraint checking at the transaction boundary.
I know it would have a lot of good effects.
- Rami
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
