[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13822700#comment-13822700
]
Dag H. Wanvik edited comment on DERBY-532 at 11/14/13 6:11 PM:
---------------------------------------------------------------
{quote}
> 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.
{quote}
Yes, I agree we wouldn't want to wait for the lock there; just adding it to the
list in a "pessimistic" assumption is fine, so we'd check again on commit.
{quote}
>
> o in immediate case do the lock wait, read scan.
{quote}
Yes, and report as duplicate if timeout or deadlock. I think it's better than
reporting lock timeout as you suggest.
{quote}
>
> and in deferred case do the lock wait read scan at commit.
{quote}
Yes, and again, report as duplicate if timeout or deadlock.
{quote}
> In both scan cases it is possible that while we are scanning someone
> might add a duplicate row that the scan 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.
{quote}
I believe this will be correct.
was (Author: dagw):
{quote}
> 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.
{quote}
Yes, I agree we wouldn't want to wait for the lock there; just adding it to the
list in a "pessimistic" assumption is fine, so we'd check again on commit.
{quote}
>
> o in immediate case do the lock wait, read scan.
{quote}
Yes, and report as duplicate if timeout or deadlock. I think it's better tha
reporting lock timeout as you suggest.
{quote}
>
> and in deferred case do the lock wait read scan at commit.
{quote}
Yes, and again, report as duplicate if timeout or deadlock.
{quote}
> In both scan cases it is possible that while we are scanning someone
> might add a duplicate row that the scan 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.
{quote}
I believe this will be correct.
> 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)
