db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-532) Support deferrable constraints
Date Tue, 05 Nov 2013 20:46:18 GMT

    [ 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

View raw message