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