[
https://issues.apache.org/jira/browse/DERBY-6419?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13831880#comment-13831880
]
ASF subversion and git services commented on DERBY-6419:
--------------------------------------------------------
Commit 1545394 from [~dagw] in branch 'code/trunk'
[ https://svn.apache.org/r1545394 ]
DERBY-532 Support deferrable constraints
Patch derby-532-post-scan-4 implements basic support for deferred
constraints for PRIMARY KEY and UNIQUE constraints. Deferrable
constraints are not enabled by default yet; one needs to set a
property to try the feature: "derby.constraintsTesting".
This patch enables deferred constraints for:
a) primary key constraints
b) unique constraint with not nullable columns
c) unique constraint with nullable columns
by new logic in insertion and sorts.
The patch includes relaxing the constraint at insertion and update
time, as well as adding a constraint to an existing table.
Derby treats constraints a) and b) the same, and in the code these are
marked as "unique" when they are not deferrable (as in existing code).
Constraint type c) is currently marked as
"uniqueWithDuplicateNulls". Insert/update of these is implemented in
the BTree by including the RowLocation of the base row in the set of
keys in the index row (DERBY-3330). This makes them trivially unique,
but there is an extra code path in BTreeController that checks
neighbor rows for duplicates, and only allows insertion if the key
contains a null. When adding a constraint to an existing table, these
are handled by a specially crafted sorter
(UniqueWithDuplicateNullsMergeSort).
The implementation of insert/update of deferrable indexes is based on
a similar approach, i.e. by backing with a non-unique index, and checking
duplicates in the language layer, notably IndexChanger.
In IndexChanger, after inserting a row, we check if it is unique by
performing a scan of the BTree. A time-out here leads to a pessimistic
assumption that there is a duplicate. Duplicate key values are saved
until checking time (usually commit time), when a new scan is
performed to validate the uniqueness property.
[This means a) and b) if deferrable are no longer marked "unique"].
Deferrable indexes are not shared.
If there are duplicates and we have deferred constraint mode (a
dynamic session property), we save the duplicate index row in a disk
based hash table (DeferredDuplicates#rememberDuplicate).
For a) and b), constraints which are deferrable are marked as
"uniqueDeferrable" and "hasDeferrableChecking". Constraints of type c)
which are deferrable are marked "uniqueWithDuplicateNulls" and
"hasDeferrableChecking". These marks determines the code paths
used. Note that existing indexes and non-deferrable constraint do not
get a new code path, which should preserve correctness and performance
of those.
Now, with these markers in place, deferral of checks happens in three
places:
{{ IndexChanger#insertAndCheckDups}}
{{CreateIndexConstantAction#executeConstantAction +
MergeSort#compare and UniqueWithDuplicateNullsMergeSort#compare }}
{{InsertResultSet#setUpAllSorts}}
The former is active for deferral under INSERT and UPDATE. The middle
when adding a deferrable constraint to an existing table, when we sort
existing rows detecting any duplicates. The last is used when importing
rows.
At transaction commit (1), or when the constraint mode for a deferred
constraint is changed back to immediate (2), we validate the
constraint (DeferredDuplicates#validate) by replaying the hash table
and scanning the index for the duplicate index rows to ascertain there
are none, or else we have an error: transaction or statement severity
respectively for (1) and (2).
The constraint mode is a SQL session level variable, and inside
routines (nested connections), we push this on the stack. This means
change of the constraint mode inside nested connections will be popped
on routine exit. If, as part of this, a constraint changes from
deferred to immediate mode, we also validate it for correctness. If
this fail, the transaction rolls back
We needed to do this from a newly introduced method,
GenericLanguageConnectionContext#popNestedSessionContext. This
pops the SQL session context.
That hook is called from GenericPreparedStatement#executeStmt. As a
part of this effort, we also renamed #setupNestedSessionContext to
#pushNestedSessionContext.
The patch also adds support for checking deferred constraints in
xa_prepare and xa_commit (.., true), cf. specification attached to the
JIRA issue.
Concurrency: if a transaction gets a lock time-out when trying to
establish if a row just inserted is a duplicate (another transaction
may have just inserted a row with a similar index key), we use a
pessimistics assumption and add that key to the set of keys to be
checked at commit time. If a key can't be grabbed then, a time-out is
thrown. We plan to add an optimized scan to avoid waiting for the lock
at insertion time, cf DERBY-6419.
The "not enforced" feature is not yet implemented in this patch.
Several new test cases been added to ConstraintCharacteristicsTest to
test these basic behaviors.
> Make BTree scan honor OPENMODE_LOCK_NOWAIT for row locks.
> ----------------------------------------------------------
>
> Key: DERBY-6419
> URL: https://issues.apache.org/jira/browse/DERBY-6419
> Project: Derby
> Issue Type: Sub-task
> Components: Store
> Reporter: Dag H. Wanvik
>
> Optimization desirable to support deferred constraints.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
