[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13814235#comment-13814235
]
Mike Matrigali commented on DERBY-532:
--------------------------------------
I am trying to understand the locking and isolation implications of doing the
"duplicate" checking in the non-duplicate new btrees at insert time. And what
is the right thing to do with rows marked deleted. I think doing this check at
insert time is going to add unintended problems with either isolation or
locking. I think the insert time check for duplicates is really a performance
thing to narrow down the number of deferred rows to keep track of, and would
like to see how bad a simpler strategy would perform which then could be
optimized later with a more complicated btree implementation later if
necessary. Would this type of implementation also match up well with whatever
we would have to do for deferred foreign key constraints?
We have always made the indexes created for constraint checking available to
the optimizer to pull rows out of
in the past. Should these new deferred constraint indexes also be available,
or is there anything special we can
do with these to make deferred update implementation easier. I looked at some
public documents and did not
get any specific info on how other db's do this, but got the feeling that these
might be treated differently in some
way vs. other indexes. We should be careful on implementation to make sure
stuff like sort avoidance for
uniqueness works correctly with these.
If the deferred unique constraint indexes were not made available to the
optimizer to satisfy query results from (and thus the rows could be
out of date until end of transaction), then another option would
be to define the unique constraints exactly as they are today in the store -
but somehow mark them as not
available to optimizer. The obvious problem with them is that the inserting
transaction should see rows it has
inserted and won't. And just defer the updating of this
index until end of transaction, driving the updates from a deferred list
maintained by the sql layer. In this case
all isolation level locking would be unchanged (just delayed) and no possible
unexpecting locking differences
between a deferred and non-deferred constraint.
Could the SQL layer during deferred mode keep track of every insert into the
"duplicate" constraint index and then do
the constraint check at commit time itself. At end transaction time I think it
is clear that every row that is looked at to do the duplicate check needs to be
first locked and waited on and then checked, with locks released according to
isolation
level standards. This would include rows marked deleted, which would be locked
to make sure that are not part
of other transactions that have not committed yet. This check could be coded
as a scan for the key and not 2 rows or it would be pretty clean to add a
special interface to return true/false for more than one row match for a given
key description.
> 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-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-xa-1.diff, derby-532-xa-2.diff
>
>
> 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)
