db-derby-dev mailing list archives

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

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

Dag H. Wanvik commented on DERBY-532:

Good idea to run all tests with default deferrable constraints, but with immediate checking,
I'll run this newly uploaded patch, derby-532-serializable-scan-1 though that experiment.

This patch removed the special logic in BTreeController, and moves the extra checking to language
(IndexChanger is in package *.impl.sql.execute).

It essentially uses the approach Knut suggested force strict same order locking: a serializable
scan on the key of the index row proposed to be inserted. For a deferrable primary key that
inserts a non-duplicate, this gives the following locks, with a number indicate order they
are set

    U[v-1]  : an update lock on the previous row in the index (2.)
    X[v]:  an exclusive lock on the newly inserted row (1. set before the U-lock when inserting
into base table)

For a normal primary key insert, we only get the latter lock.
For an insert of a (first) duplicate, we would see the following locks:

    U[v-1]:  an update lock on the previous row in the index (2.)
    U[v]: an update lock on the first inserted value, for which we now insert a duplicate
    X[v]: an exclusive lock on the newly inserted row. (1.)

Since any transaction wanting to insert "v" would need to lock "v-1" to the left, we would
effectively serialize any contending transactions behind the first one to insert "v"; meaning
they would detect the duplicate, and throw (not deferred) or postpone further checking till
commit (deferred mode).

A test case verifying this has been added in ConstraintCharacteristicsTest#testLocks.
The patch is a sum of the patched not yet committed so far (no prerequisites). I had to make
one small concession: the unique nullable constraints needed an extra predicate inside BtreeController
to allow them to insert a duplicate. An alternative would be to mark these indexes as something
else than "uniqueWithDuplicateNulls" when constraints are deferred.

The checking at commit time uses a BtreeScan also, but not an identical one, it uses read
committed, read-only no hold lock scan. I *think* this should be safe (?).

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