db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-532) Support deferrable constraints
Date Fri, 15 Nov 2013 11:13:21 GMT

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

Knut Anders Hatlen commented on DERBY-532:
------------------------------------------

{quote}
    >
    > o in immediate case do the lock wait, read scan.

Yes, and report as duplicate if timeout or deadlock. I think it's better than reporting lock
timeout as you suggest.

    >
    > and in deferred case do the lock wait read scan at commit.

Yes, and again, report as duplicate if timeout or deadlock.
{quote}

If I understand this proposal correctly, an insert operation will throw duplicate key exception
instead of lock timeout exception if it cannot obtain a lock during duplicate checking. If
so, that doesn't match what we do in the non-deferrable case:

{noformat}
ij> connect 'jdbc:derby:memory:db;create=true' as c1;
ij> create table t(x int primary key);
0 rows inserted/updated/deleted
ij> autocommit off;
ij> insert into t values 1;
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:memory:db' as c2;
ij(C2)> insert into t values 0;
1 row inserted/updated/deleted
ij(C2)> insert into t values 1;
ERROR 40XL1: A lock could not be obtained within the time requested
{noformat}

I agree that we should not throw lock timeout exception if we cannot get a lock immediately
during the preliminary check in the deferred case. Then we should just add that key to the
list of keys to check at commit time. But if we fail to get a lock in the final duplicate
check (either immediate or deferred), I think it is correct to report that as a lock timeout,
not as a constraint violation, as we don't know if it actually is a constraint violation until
the other transactions have completed.

> 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