[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13815390#comment-13815390
]
Dag H. Wanvik commented on DERBY-532:
-------------------------------------
Thanks, Mike. Your proposal makes sense to me and I agree on your architectural
consideration that as far as keeping the store out of the language
considerations here - I believe what you propose should work, but would be
slightly less performant that what I currently do, in that we would be storing
away (and checking at commit time) *all* inserted keys, not only those that are
duplicates. But I agree it would be safer if we don't fully trust the current
"left/right" checking, and also simpler, as far as reducing special code in the
store. I'll have a closer look at such an approach and report back.
Also with such a solution, at commit time two concurrent transactions both
inserting duplicates would both be running checks. Could that give rise to
dead-locks I wonder? Could both have a row level X write lock on a duplicate in
addition to an existing (left-most) unlocked existing row, say? Or can we latch
the X locked row in the BTree to detect a duplicate anyway?)
As far as foreign constraints are concerned, I haven't looked closely at those
yet, but my hunch is that we could reuse the mechanism developed for pk/unique.
> 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-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)
