Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 15 Nov 2013 11:13:21 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12313704.1124846280000.80192.1384514001095@arcas>
In-Reply-To: <JIRA.12313704.1124846280000@arcas>
References: <JIRA.12313704.1124846280000@arcas>
Subject: [jira] [Commented] (DERBY-532) Support deferrable constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


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

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 th=
an 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 dup=
licate 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 t=
he non-deferrable case:

{noformat}
ij> connect 'jdbc:derby:memory:db;create=3Dtrue' 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 viol=
ation 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=C3=B6rg 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.di=
ff, 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-sc=
an-2.diff, derby-532-serializable-scan-2.stat, derby-532-syntax-binding-dic=
t-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-bindi=
ng-dict-all-1.diff, derby-532-testAlterConstraintInvalidation.diff, derby-5=
32-testAlterConstraintInvalidation.status, derby-532-unique-pk-1.diff, derb=
y-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.di=
ff, derby-532-xa-3.diff, derby-532-xa-3.status
>
>
> In many situations it is desirable to have constraints checking taking pl=
ace only at transaction commit time, and not before. If e.g. there is a cha=
in of foreign key constraints between tables, insert statements have to be =
ordered to avoid constraint violations. If foreign key references are circu=
lar, the DML has to be split into insert statements and subsequent update s=
tatements by the user.
> In other words, with deferred constraints checking, life is much easier f=
or the user. Also it can create problems with softwares such as object-rela=
tional 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)