Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <44238ED3.100@apache.org>
Date: Thu, 23 Mar 2006 22:16:51 -0800
From: Daniel John Debrunner <djd@apache.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.3) Gecko/20040910
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: When is a new StatementContext pushed?
References: <d9619e4a0603232144xb6f63ct6fb876b3229862fb@mail.gmail.com>
In-Reply-To: <d9619e4a0603232144xb6f63ct6fb876b3229862fb@mail.gmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Mamta Satoor wrote:

> Hi,
>  
> I am curious if there is some rule about when does the Derby engine push
> a new StatementContext? I have tried couple examples and noticed few
> cases where a new StatementContext is pushed and later popped.
> 1)When a user gives a sql statement and if it is not in the cache, then
> during the compilation phase, a StatementContext is pushed and at the
> end of compilation, the StatementContext is popped. When the execution
> phase starts for the statement, a new StatementContext is pushed and at
> the end of execution, the StatementContext is popped.
> 2)When the user moves in a JDBC ResultSet with next/first/last etc, a
> StatementContext is pushed and popped around the ResultSet movement code.
> 3)If a trigger is defined on a dml statement, trigger execution has its
> own StatementContext.
>  
> My question is when is there a need to push a new StatementContext
> rather than using an existing one?

I believe StatementContext's are there to mainly handle statement
cleanup. Statements in SQL are atomic, either all the statement occurs
or none of it. Thus an INSERT statement that inserts five rows but fails
on the third due to a unique constraint violation, needs to rollback and
cleanup all the effects of that statement.

If a statement calls another statement, e.g. a function call from an
INSERT statement that performs server side JDBC then a statement level
exception in the server-side JDBC needs to only rollback the changes and
cleanup from the effect of the server-side JDBC statement, it must not
cleanup the outer INSERT statement.

In the Java method for the function, the application code can either
catch the exception and continue, or allow the exception to be thrown
into the databsae engine. In the first case the outer INSERT statement
needs to continue successfully, hence the limiting of the cleanup to the
server-side statement. In the second case the INSERT must be also rolled
back due to the exception, but now it's handled as a separate statement
level cleanup of the INSERT statement.

So I think the current simple model is that each statement pushes a
StatementContext when it is executing so that it can be rolled back
individually, even though in some cases (a trigger) the failure of the
inner statement may also cause the outer statement to be rolled back as
well.
Not sure how this actually works for a trigger.

The alternate would be to have nesting counts within a StatementContext,
which would also require (possibly) StatementContexts to be owned by
statements. Currently StatementContexts are just temporarily owned by
the statement while it is executing.

HTH,
Dan.