[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13835590#comment-13835590
]
Dag H. Wanvik edited comment on DERBY-532 at 12/2/13 7:48 PM:
--------------------------------------------------------------
Here are the rest of the failures and errors I saw running the JUnit tests with
default deferrable (the experiment mentioned above). I'll be analyzing them to
see if they are to be expected or not.
- LangProcedureTest (lock difference)
{panel}
=> To be expected: we are running with serializable isolation mode
and the index used is no longer physically unique, so the
query reads the previous key also. Locks expected in the non deferred case:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{345,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{345,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
what we see in the deferrable case is:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{503,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{503,ROW,S,T1,(1,7),GRANT,T,1,null}
{503,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
{panel}
- LangScripts update (lock difference)
{panel}
=> To be expected. We have this table:
{noformat}
create table tab1 (c1 int not null primary key, c2 int)
insert into tab1 values (1, 8)
{noformat}
and then we do:
{noformat}
update tab1 set c2 = c2 + 3 where c1 = 1;
{noformat}
Read committed, deferrable
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{184,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
{184,ROW,X,TAB1,(1,7),GRANT,T,3,null}
{noformat}
Serializable , deferrable
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{186,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
{186,ROW,X,TAB1,(1,3),GRANT,T,1,null}
{186,ROW,X,TAB1,(1,7),GRANT,T,3,null}
{noformat}
i.e. we also have a lock on the first (control) row (before row
containing 1). In the non-deferrable case, again we have a unique
physical index on c1, so a previous lock is not required. Note, for
read committed there is no difference.
{panel}
- LojReorderTest (different execution plan)
{panel}
=> To be expected. The original plan looked like this:
{noformat}
"Sort ResultSet:",
"Source result set:",
"_Hash Left Outer Join ResultSet:",
"_Left result set:",
"__Nested Loop Left Outer Join ResultSet:",
"__Left result set:",
"___Nested Loop Left Outer Join ResultSet:",
"___Left result set:",
"____Index Scan ResultSet for A",
"___Right result set:",
"____Index Scan ResultSet for B",
"__Right result set:",
"___Index Scan ResultSet for C",
"_Right result set:",
"__Hash Scan ResultSet for D"});
{noformat}
With deferrable constraints it looks like this:
{noformat}
"Sort ResultSet:",
"Source result set:",
"_Hash Left Outer Join ResultSet:",
"_Left result set:",
"__Nested Loop Left Outer Join ResultSet:",
"__Left result set:",
"___Hash Left Outer Join ResultSet:",
"___Left result set:",
"____Index Scan ResultSet for A",
"___Right result set:",
"____Hash Scan ResultSet for B",
"__Right result set:",
"___Index Scan ResultSet for C",
"_Right result set:",
"__Hash Scan ResultSet for D"});
{noformat}
As we can see, the deferrable the inner (leftmost) LOJ uses as Hash
left order join with and index scan for A and a hash scan for B,
whereas the non deferrable case uses nested loop over two index
scans for A and B, presumably because the physical uniqeness of the
index on B gives the optimizer another cardinality estimate. In any
case both plans are good.
{panel}
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index
identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
for the received table: Expected: >3< Found: >4<
To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)
if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
SanityManager.ASSERT(
insertStatus != ConglomerateController.ROWISDUPLICATE); <=====!!
}
++ more
- UniqueConstraintSetNullTest
(java.sql.SQLIntegrityConstraintViolationException: The statement
was aborted because it would have caused a duplicate key value in a
unique or primary key constraint or unique index identified by
'U_CON' defined on 'CONSTRAINTEST'.)
++ more
- UniqueConstraintMultiThreadedTest
(junit.framework.AssertionFailedError: isolation levels: 1 1)
- XplainStatisticsTest ( expected rows:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
actual result:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])
- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
failed: junit.framework.ComparisonFailure: Output at line 85
expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >
- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
to get statistics for table TEST_TAB_1 (#expected=0, timeout=0)
Index statistics for TEST_TAB_1 1:
{tableId=2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=TEST_TAB_1,
indexName=SQL131129120747740, lcols=1, rows=2, unique/card=2,
created=2013-11-29 12:07:47.742} expected:<0> but was:<1>)
(Note: this run included the proposed patch for DERBY-6419 as well)
was (Author: dagw):
Here are the rest of the failures and errors I saw running the JUnit tests with
default deferrable (the experiment mentioned above). I'll be analyzing them to
see if they are to be expected or not.
- LangProcedureTest (lock difference)
{panel}
=> To be expected: we are running with serializable isolation mode
and the index used is no longer physically unique, so the
query reads the previous key also. Locks expected in the non deferred case:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{345,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{345,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
what we see in the deferrable case is:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{503,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{503,ROW,S,T1,(1,7),GRANT,T,1,null}
{503,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
{panel}
- LangScripts update (lock difference)
{panel}
=> To be expected. We have this table:
{noformat}
create table tab1 (c1 int not null primary key, c2 int)
insert into tab1 values (1, 8)
{noformat}
and then we do:
{noformat}
update tab1 set c2 = c2 + 3 where c1 = 1;
{noformat}
Read committed, deferrable
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{184,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
{184,ROW,X,TAB1,(1,7),GRANT,T,3,null}
{noformat}
Serializable , deferrable
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{186,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
{186,ROW,X,TAB1,(1,3),GRANT,T,1,null}
{186,ROW,X,TAB1,(1,7),GRANT,T,3,null}
{noformat}
i.e. we also have a lock on the first (control) row (before row
containing 1). In the non-deferrable case, again we have a unique
physical index on c1, so a previous lock is not required. Note, for
read committed there is no difference.
{panel}
- LojReorderTest (different execution plan)
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index
identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
for the received table: Expected: >3< Found: >4<
To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)
if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
SanityManager.ASSERT(
insertStatus != ConglomerateController.ROWISDUPLICATE); <=====!!
}
++ more
- UniqueConstraintSetNullTest
(java.sql.SQLIntegrityConstraintViolationException: The statement
was aborted because it would have caused a duplicate key value in a
unique or primary key constraint or unique index identified by
'U_CON' defined on 'CONSTRAINTEST'.)
++ more
- UniqueConstraintMultiThreadedTest
(junit.framework.AssertionFailedError: isolation levels: 1 1)
- XplainStatisticsTest ( expected rows:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
actual result:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])
- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
failed: junit.framework.ComparisonFailure: Output at line 85
expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >
- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
to get statistics for table TEST_TAB_1 (#expected=0, timeout=0)
Index statistics for TEST_TAB_1 1:
{tableId=2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=TEST_TAB_1,
indexName=SQL131129120747740, lcols=1, rows=2, unique/card=2,
created=2013-11-29 12:07:47.742} expected:<0> but was:<1>)
(Note: this run included the proposed patch for DERBY-6419 as well)
> 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: IndexDescriptor.html, IndexDescriptorImpl.html,
> IndexRowGenerator.html, SortObserver.html, deferredConstraints.html,
> deferredConstraints.html, deferredConstraints.html, deferredConstraints.html,
> deferredConstraints.html, derby-532-fix-metadata-1.diff,
> derby-532-fix-metadata-1.status, 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-post-scan-1.diff,
> derby-532-post-scan-1.stat, derby-532-post-scan-2.diff,
> derby-532-post-scan-2.stat, derby-532-post-scan-3.diff,
> derby-532-post-scan-3.stat, derby-532-post-scan-4.diff,
> derby-532-post-scan-4.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-test-with-default-deferrable-all-over.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)
