db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-532) Support deferrable constraints
Date Thu, 14 Nov 2013 00:09:20 GMT

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

Mime
View raw message