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 Wed, 13 Nov 2013 22:45:23 GMT

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

Mike Matrigali commented on DERBY-532:
--------------------------------------

one other consideration is that Derby actually has more than row locks than S, U, and W. 
We don't really document them as it is more confusing than it is worth to users, and the lock
debugging tools map the 8 locks into these 3 letters.   There are actually 8 different types
of locks
defined in org.apache.derby.iapi.store.raw.RowLock, and you can see the complete compatibility
table
there.
    /* Row Shared lock for repeatable read and below isolation level */
    public static final RowLock RS2  = new RowLock(0);
    /* Row Shared lock for serialized read isolation level */
    public static final RowLock RS3  = new RowLock(1);
    /* Row Update lock for reapeatable read and below isolation level*/
    public static final RowLock RU2  = new RowLock(2);
    /* Row Update lock for serializable isolation level*/
    public static final RowLock RU3  = new RowLock(3);
    /* Row Insert previous key lock */
    public static final RowLock RIP  = new RowLock(4);
    /* Row Insert lock */
    public static final RowLock RI   = new RowLock(5);
    /* Row exclusive write lock for repeatable read and below isolation level */
    public static final RowLock RX2  = new RowLock(6);
    /* Row exclusive write lock for serializable isolation level */
    public static final RowLock RX3  = new RowLock(7);

The one of interest to this discussion is the RIP lock, which implements the previous key
lock for inserts.  This lock did not exist in the initial implementations, but was required
as users found concurrency unacceptable when we just got a regular write lock as the previous
key lock for inserts.

inserts get the right previous key locks.  Doing a scan before the insert is going to get
the wrong previous
key lock for an insert to have, and it is also going to block in cases I don't think are expected
by someone
using deferred constraint.

Here is the compatibility table:
{code}
    /** Row lock compatibility table. */
    private static final boolean[][] R_COMPAT = {
        //          Granted
        // Request   RS2     RS3    RU2    RU3    RIP    RI     RX2    RX3
        //
        /* RS2 */    {true,  true,  true,  true,  true,  false, false, false },
        /* RS3 */    {true,  true,  true,  true,  false, false, false, false },
        /* RU2 */    {true,  true,  false, false, true,  false, false, false },
        /* RU3 */    {true,  true,  false, false, false, false, false, false },
        /* RIP */    {true,  false, true,  false, true,  true , true,  false },
        /* RI  */    {false, false, false, false, true,  false, false, false },
        /* RX2 */    {false, false, false, false, true,  false, false, false },
        /* RX3 */    {false, false, false, false, false, false, false, false }

{code}





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

Mime
View raw message