Question on nested transaction and SPS recompilation, anyone?

[Yip Ng]

Hi

   I am working on a jira issue (DERBY-1621) and I am hoping to get some clarifications from
someone who is familar with the recompilation phase of a stored prepard statement(SPS) logic.

   If a SPS needs to be recompiled during execution, SPSDescriptor getPreparedStatement()
first updates the SYS.SYSDEPENDS system table with the current transaction.  Then it
attempts to get a user nested transaction(if it can) when updating the SYS.SYSSTATEMENTS
system table, then commits the user nested transaction.  

1) Why isn't the SPS recompilation using the same transaction to update SYS.SYSDEPENDS and
    SYS.SYSSTATEMENTS?  Is this for a concurrency enhancement?    

2) Wouldn't this cause a problem for the dependency manager to lose stored dependencies
   if the parent transaction is rollback due to a constraint violation? i.e.:

   create table t1 (i int);
   create table t2 (i int);
   create trigger tt after insert on t1 for each statement mode db2sql insert into t2 values 1;
   insert into t1 values 1;
   create unique index tu on t2(i);  
   insert into t1 values 1;   

   insert into t1 values 1 will fail due to unique constraint violation from the trigger action, but now the SPS is marked as valid and the stored dependencies are lost due to the parent transaction rollback.  So the SPS will not get invalidated later if a drop index tu was issued because the conglomerate tu does not have the SPS as one of its dependents.