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