[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13822013#comment-13822013
]
Mike Matrigali commented on DERBY-532:
--------------------------------------
I don't think it is a big deal to add all inserts to the deferred check list
when you are in deferred mode, but recognize others may not agree.
I consider any code that eliminates the inserts from the list early just an
optimization, but any problem with
the optimization can easily lead to a corrupt table if it misses just one case
it should have checked later. I think
we agree at this point that all the other pieces still need to happen.
If you think the optimization is necessary do you think the following can be
proven correct, ie we will never
miss a case where the row we inserted is causing a duplicate key and should
have been added to the
deferred check list:
o do the insert first.
o in deferred case do an immediate non-serialized, no hold, no lock wait, read
only scan of the keys.
if duplicate add to list, and if it could not get a lock add to the list.
locks have to be acquired for store
to do the right thing with rows marked deleted - can not do read uncommitted.
o in immediate case do the lock wait, read scan.
and in deferred case do the lock wait read scan at commit.
In both scan cases it is possible that while we are scanning someone might add
a duplicate row
that the can will miss, but I think that is ok. We just need to make sure all
the rows as of "now" are being
checked. And we insure that because the scan positions at the leftmost
matching key "now" and moves
right. No "now" row can move left. It is up to whoever is inserting those
later rows to do their own check.
But I appreciate all to think about this.
> Support deferrable constraints
> ------------------------------
>
> Key: DERBY-532
> URL: https://issues.apache.org/jira/browse/DERBY-532
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Jörg von Frantzius
> Assignee: Dag H. Wanvik
> Labels: derby_triage10_11
> Attachments: deferredConstraints.html, deferredConstraints.html,
> deferredConstraints.html, deferredConstraints.html, derby-532-import-1.diff,
> derby-532-import-1.status, derby-532-import-2.diff, derby-532-import-3.diff,
> derby-532-import-3.status, derby-532-more-tests-1.diff,
> derby-532-more-tests-1.stat, derby-532-serializable-scan-1.diff,
> derby-532-serializable-scan-2.diff, derby-532-serializable-scan-2.stat,
> derby-532-syntax-binding-dict-1.diff, derby-532-syntax-binding-dict-1.status,
> derby-532-syntax-binding-dict-2.diff, derby-532-syntax-binding-dict-2.status,
> derby-532-syntax-binding-dict-all-1.diff,
> derby-532-testAlterConstraintInvalidation.diff,
> derby-532-testAlterConstraintInvalidation.status, derby-532-unique-pk-1.diff,
> derby-532-unique-pk-1.status, derby-532-unique-pk-2.diff,
> derby-532-unique-pk-3.diff, derby-532-unique-pk-3.status,
> derby-532-xa-1.diff, derby-532-xa-2.diff, derby-532-xa-3.diff,
> derby-532-xa-3.status
>
>
> In many situations it is desirable to have constraints checking taking place
> only at transaction commit time, and not before. If e.g. there is a chain of
> foreign key constraints between tables, insert statements have to be ordered
> to avoid constraint violations. If foreign key references are circular, the
> DML has to be split into insert statements and subsequent update statements
> by the user.
> In other words, with deferred constraints checking, life is much easier for
> the user. Also it can create problems with softwares such as
> object-relational mapping tools that are not prepared for statement ordering
> and thus depend on deferred constraints checking.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
