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 19:39:22 GMT

    [ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13822807#comment-13822807

Mike Matrigali commented on DERBY-532:

I just looked at the protocol documentation and I don't think currently the btree scan will
do what you want, with respect to OPENMODE_LOCK_NOWAIT.  It looks like it will do nowait on
the table lock but not on the subsequent row locks.  

I would suggest as an incremental approach to code the deferred check using waiting locks
for now, and
we can optimize it to not wait in a subsequent patch.  I think even in the nowait case the
new interface should throw a locktimeout error if it can't get the lock.

I need to think about what would be the best solution to providing this functionality.  We
can either enhance all scans to support lock nowait, which i think is it a lot of work.  Or
I think it would be easier and better for this feature in the long run to provide a new interface
that your code would just pass in the key you are looking for,
some params similar to openScan to tell what type of scan to use in addition to a wait or
no wait flag.  This would mean the duplicate check could be done in a single trip to store,
rather than open a scan and then do a next.   At the lowest level of the btree we often ask
for nowait locks, so that part is not hard.  But making it
available for all scans is overriding the user setting for this.  This is obviously a special
case, so think it is
cleaner making it clear it is a special case by using a different interface.

I would be interested in implementing this interface as a subsequent patch, once you get checked
in.  Or if you want to implement I can help.  Let me know which you prefer.

> 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

View raw message