[
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 (3.)
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,
> 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)
