[
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
assuming:
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 wrong,
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
(v6.1#6144)
