[
https://issues.apache.org/jira/browse/DERBY-4166?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lily Wei updated DERBY-4166:
----------------------------
Attachment: DERBY-4166-schemachange2.diff
Thanks Kathey for looking at the patch.
-the space/tab issue in schema.sql has been taking care of
-count_att is delete fom DbTasks.deleteMailByThread
-change the logic in Insert mail so
it will
nt num = Rn.nextInt(10 - 1);
for (int i = 0; i < num; i++) {
//insert mail to INBOX
// get the id from select id from REFRESH.INBOX
(stmt1)
// insert attachments for approximately 1 in 10
mails
while (i == Rn.nextInt(10 -1)) {
int num_attatch = Rn.nextInt(5-1);
for (int j = 0; j < num_attach;j++) {
// insert the attachment using the id from
stmt1
}
Test run fine for a day. However, I am seeing deadlock error now.
Within the day, delete thread hold lock on table inbox which will cascade
delete table attach. Also, insert thread will hold lock on inbox for getting
the id and holding lock for attach for inserting row to attach. The time the
test takes to delete row from tables is usually less than a second. However,
the time it takes to insert to inbox and attach can be varying from 0.x second
to 16 sec.
To avoid the deadlock, I can:
1. Sleep more before delete thread gets call
2. Look at while inserting less than 10 rows to each table on total of two
tables can takes up to 16 seconds.
3. Put id in a array so insert into attach does not hold lock for table inbox
and table attach
4. Open for more suggestion.
2009-07-25 07:43:56.494 GMT Thread[Refresh Thread,5,main] (XID = 131869), (SESSI
ONID = 3), (DATABASE = mailsdb), (DRDAID = null), Cleanup action starting^M
2009-07-25 07:43:56.494 GMT Thread[Refresh Thread,5,main] (XID = 131869), (SESSI
ONID = 3), (DATABASE = mailsdb), (DRDAID = null), Failed Statement is: delete fr
om REFRESH.INBOX where to_delete = 1^M
ERROR 40001: A lock could not be obtained due to a deadlock, cycle of locks and
waiters is:
Lock : ROW, INBOX, (1,623)
Waiting XID : {131869, X} , REFRESH, delete from REFRESH.INBOX where to_delete
= 1
Granted XID : {131737, X}
Lock : ROW, INBOX, (1,613)
Waiting XID : {131737, X} , BROWSE, update REFRESH.INBOX set to_delete = ? whe
re id = ?
Granted XID : {131869, X}
. The selected victim is XID : 131869.^M
at org.apache.derby.iapi.error.StandardException.newException(StandardEx
ception.java:303)^M
at org.apache.derby.impl.services.locks.Deadlock.buildException(Deadlock
.java:390)^M
at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Con
currentLockSet.java:613)^M
at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Abstract
Pool.java:119)^M
at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Concur
rentPool.java:28)^M
at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(R
owLocking3.java:248)^M
at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapCo
ntroller.java:504)^M
at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapCo
ntroller.java:638)^M
at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRow
OnPage(B2IRowLocking3.java:309)^M
at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockSc
anRow(B2IRowLocking3.java:599)^M
at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockSc
anRow(B2IRowLockingRR.java:105)^M
TreeForwardScan.java:305)^M
at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(BTreeSca
n.java:1503)^M
at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(T
ableScanResultSet.java:680)^M
at org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextR
owCore(IndexRowToBaseRowResultSet.java:373)^M
at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRow
Core(ProjectRestrictResultSet.java:256)^M
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DM
LWriteResultSet.java:127)^M
at org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows
(DeleteResultSet.java:335)^M
at org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.collectAffec
tedRows(DeleteCascadeResultSet.java:185)^M
at org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.open(DeleteC
ascadeResultSet.java:120)^M
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
cPreparedStatement.java:416)^M
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
paredStatement.java:297)^M
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
ment.java:1235)^M
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Em
bedPreparedStatement.java:1675)^M
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Embed
PreparedStatement.java:302)^M
at org.apache.derbyTesting.system.mailjdbc.utils.DbTasks.deleteMailByThr
ead(DbTasks.java:330)^M
at org.apache.derbyTesting.system.mailjdbc.tasks.Refresh.deleteMailByRef
resh(Refresh.java:105)^M
at org.apache.derbyTesting.system.mailjdbc.tasks.Refresh.doWork(Refresh.
java:83)^M
at org.apache.derbyTesting.system.mailjdbc.tasks.Refresh.run(Refresh.jav
a:51)^M
I got 4 deadlock error from one day run.
> improvements to the mailjdbc test
> ---------------------------------
>
> Key: DERBY-4166
> URL: https://issues.apache.org/jira/browse/DERBY-4166
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.6.0.0
> Reporter: Kathey Marsden
> Priority: Minor
> Attachments: DERBY-4166-databasesize.diff, Derby-4166-samedb.diff,
> DERBY-4166-schemachange.diff, DERBY-4166-schemachange2.diff, Derby-4166.diff
>
>
> When recently working with the mailjdbc system test
> org.apache.derbyTesting.system.mailjdbc on DERBY-4152 I noticed some
> potential improvements that might be good for the test. We should probably
> hold off on these improvements however until the root cause of DERBY-4152 is
> established, however, so we don't muddy the waters with that issue by
> changing the test.
> 1) DbTasks.moveToFolders may throw an IllegalArgumentException.
> There is a line: message_id = Rn.nextInt(count - 1);
> if count is 1 the argument to nextInt() might be 0 which is not allowed. I
> hit this once but lost the stack trace, but it is apparent that when there is
> only one row in the table this can occur.
>
> 2) Allow/implement multiple attachments per message and cleanup
> DbTasks.insertMail() logic.
> - Remove the attach_id column from INBOX to allow multiple attachments.
> -Make the attachment insert part of the message for loop in insertMail.
> Use getGeneratedKeys() to get the id of the inserted message.
> When attachments are inserted, insert (1-4) attachments and give them a
> corresponding attach_id from 1-4.
> This will allow for removal of the select statements used to determine id and
> attach_id. I'll file another issue for these improvements if folks agree
> that they are sensible.
> A detailed description of the current implementation of insertMail is
> described at
> https://issues.apache.org/jira/secure/attachment/12405685/insertMailSummary.txt
> 3) DbTasks.databaseSize calculation is wrong. It doesn't match du -sk. The
> method does not recurse into subdirectories and includes the length() on
> directory files which is undefined accourding to the file.length() javadoc.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
