Question on nested transaction and SPS recompilation, anyone?

[Yip Ng <yi...@gmail.com>]

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.

Re: Question on nested transaction and SPS recompilation, anyone?

[Yip Ng <yi...@gmail.com>]

On 8/10/06, Mike Matrigali <mi...@sbcglobal.net> wrote:
>
> Just to make it clear, derby does not support any version of a "nested"
> transaction which one could commit but then subsequently rollback as
> part of the original transaction.   All "nested" transactions provided
> by store are committed independently of the parent transaction.  These
> transactions are only available internal to the server implementation
> and are not available to user applications.
>
> Thanks for clearing that up, Mike.
>

Re: Question on nested transaction and SPS recompilation, anyone?

[Mike Matrigali <mi...@sbcglobal.net>]

Just to make it clear, derby does not support any version of a "nested"
transaction which one could commit but then subsequently rollback as
part of the original transaction.   All "nested" transactions provided
by store are committed independently of the parent transaction.  These
transactions are only available internal to the server implementation 
and are not available to user applications.

Use of internal transactions in the language I believe are always used
to execute some database read/write which needs locks to get a 
consistent view, but is important to release those locks with a commit
earlier than the parent user transaction.  For instance when compiling
a query we need a consistent view of the ddl metadata which may require
locks from the database but we don't want to hold those locks until
end user transaction.

As to your specific question, it would be a bug if the language is
executing 2 related ddl updates not in the same transaction if it would
cause a bug if one part committed and one part aborted.

Yip Ng wrote:
> On 8/9/06, *Yip Ng* <yipng168@gmail.com <ma...@gmail.com>> wrote:
> 
>     3)  Even though the nested transaction has committed but since the
>     parent transaction is rollback, shouldn't the change in the nested
>     transaction rollback as well?  This is not what
>     I am seeing with the example given in 2). 
> 
> 
> 
> I got mixed up with nested subtransaction on this one.  The internal 
> nested transaction is top level so it is independent of the parent.  
> Please ignore this question.  =)
> 
> 

Re: Question on nested transaction and SPS recompilation, anyone?

[Yip Ng <yi...@gmail.com>]

On 8/9/06, Yip Ng <yi...@gmail.com> wrote:
>
> 3)  Even though the nested transaction has committed but since the parent
> transaction is rollback, shouldn't the change in the nested transaction
> rollback as well?  This is not what
> I am seeing with the example given in 2).
>


I got mixed up with nested subtransaction on this one.  The internal nested
transaction is top level so it is independent of the parent.  Please ignore
this question.  =)

Re: Question on nested transaction and SPS recompilation, anyone?

[Yip Ng <yi...@gmail.com>]

3)  Even though the nested transaction has committed but since the parent
transaction is rollback, shouldn't the change in the nested transaction
rollback as well?  This is not what
I am seeing with the example given in 2).


On 8/9/06, Yip Ng <yi...@gmail.com> wrote:
>
> 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.
>
>
>