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 Thu, 07 Nov 2013 00:16:19 GMT

    [ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13815485#comment-13815485

Mike Matrigali commented on DERBY-532:

> Also with such a solution, at commit time two concurrent transactions both
> inserting duplicates would both be running checks. Could that give rise to
> dead-locks I wonder? Could both have a row level X write lock on a duplicate
> in addition to an existing (left-most) unlocked existing row, say? Or can we
> latch the X locked row in the BTree to detect a duplicate anyway?)

This is interesting, we could easily cause deadlocks if not careful.  I think
this is an issue with either implementation if the "pre-commit" checking gets locks on
rows while checking for duplicates.  I think in current current patch
the problem happens if 2 transactions at same time insert same key as an
existing key, and in my proposal it happens sooner when 2 transactions insert
same key at same time but does not need an existing key.  There are other
scenario's but these seem the most straight forward.
At least in the proposed implementation
the check phase always asks for locks left to right.  I think deadlocks would
be pretty much guaranteed for competing transactions inserting duplicate keys.
 if we go ahead and get shared locks while checking
for duplicates.  I think in the non-defered case the 2nd competing transaction
would wait on insert, for the first transaction.

I have not thought this all the way through but the options seem to be (in
all cases we would have exclusive latch on the page we looking at, and thus
row can not be changing underneath us):
1) get shared waiting locks on each row while doing the duplicate checking.
   If we get the lock do the obvious checks (ie. if deleted no check, if
   not deleted to the dup check).  If we get a timeout or deadlock I suggest
   catching and throwing a duplicate key error.  It means that another
   transaction has an active write action on a duplicate row, so seems
   reasonable to return a duplicate error.

   I think it would be bad to let the timeout or deadlock escape to the user,
   as it is hard to explain how a transaction doing a single insert is

2) get shared non-waiting locks on each row.  will throw more "in-flight"
   duplicate key errors that #1, but will not pay the wait/deadlock wait
   time penalty.  Do same error handling as in #1, but will only get timeout's.

3) don't do any locking.  Then would have to throw duplicate error even on
   deleted rows as there is no way to tell if they are committed deleted or

I would not do option #3.  I lean toward option #2, but would be ok with
option #1.

I will think about if there is anything smart we can do if we know the
rows we are looking at are "locked for insert", but nothing comes to mind

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

View raw message