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 Wed, 13 Nov 2013 23:19:20 GMT

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

Mike Matrigali commented on DERBY-532:

I do think we need to think hard about a similar set of issues that you wrote up for the single
post check after the insert, to
convince everyone of correctness.  But I think the post scan need only answer a single question,
did the
insert I made cause a duplicate key - I don't think it needs to prevent a subsequent duplicate
key from 
being inserted, even while the scan is happening so it can do a simpler scan

1) all inserts into this tree will do the same left to right scan for duplicates after the
insert.  It may happen
    right after the insert or it may happen at end of transaction.
2) the scan positions at the first instance of the key (with no row position).
    If the scan comes back with no key assert if it is an immedate check as something is seriously
    otherwise in deferred mode I think it is ok as the inserting transaction may have deleted
it in the meantime
    and we should not try to track that.
    if the key exists it waits for a S lock on the key.  If it gets a lock and it is deleted
then all is fine.  If it
    gets a lock then note it if it is the first or throw a duplicate key error if it is the
2nd.  The normal case
    is the scan finds one and only one row.
    If it gets a lock timeout or a deadlock then I am not positive what to do.  I lean toward
catching and 
        turning it
        into a duplicate key error.   I think applications are much more likely to handle
that than a unexpected
        deadlock.  I think the easy case of 2 concurrent inserts of same key to a deferred
constraint  is going
        to generate a deadlock, and users are not going to expect a system with 2 xacts doing
just a single 
        insert each to generate a deadlock.

> 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