tx isolation level on cascade delete

["Sumner, Alex" <Al...@mentor.com>]

Hi,

I am porting a db from Oracle to Derby. The db provides the persistence
for a large multi-user Java app. The Oracle version makes heavy use of
cascade delete constraints. To delete an object at the top level of the
Java object model you delete its row from one table and cascade deletes
will then remove thousands of dependent rows (owned objects in the
model) from tens of other tables (one table for each object type).

I have a problem in that the cascaded deletions acquire table locks.
This stops all other users of the application from working when any of
them deletes a top level object, because for several minutes many of the
tables are locked. With row locking this would not be a problem as other
users would be working on different objects.

I am only requesting READ_COMMITTED tx isolation and I have tried
adjusting derby.locks.escalationThreshold, but this has no effect. On
stepping through the code the explanation appears to be in the following
code from org.apache.derby.impl.sql.execute.DeleteResultSet:

//delete the rows that in case deferred case and
//during cascade delete (All deletes are deferred during cascade action)
void deleteDeferredRows() throws StandardException
{
	
DataValueDescriptor		rlColumn;
 	RowLocation	baseRowLocation;
	ExecRow		deferredRLRow = null;

	deferredBaseCC = tc.openCompiledConglomerate(false,
		tc.OPENMODE_FORUPDATE|tc.OPENMODE_SECONDARY_LOCKED,
		lockMode,
		TransactionController.ISOLATION_SERIALIZABLE,
		constants.heapSCOCI,
		heapDCOCI);

It seems an isolation level of
TransactionController.ISOLATION_SERIALIZABLE is hard coded for all
cascaded deletions and I guess this is the reason that table locks are
required. 

Is there a fundamental reason why this level of isolation is required
here? What would it take to allow a lower level to be requested? I am
happy to do some work towards this, but my knowledge of the Derby code
is not extensive, so currently I don't know if there is much prospect of
success. Could anyone advise me? As it stands I can see no way to use
cascading deletion in a multi-user scenario.

Thanks,

Alex Sumner