db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-532) Support deferrable constraints
Date Thu, 14 Nov 2013 18:11:24 GMT

    [ 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)

Mime
View raw message