Transaction Problems of a Derby Stored Procedure

[sin(EaTing),]

Hi,
I am writing some stored procedure for Derby. And I am confused about the transaction settings for a Derby stored procedure using a nested connection.
Actually I am trying to modify the transaction isolation level to something like Serializable or Read Committed, and also change the auto commit to be false so that I can manually control the rollback or commit in the stored procedure. What I am doing is just something like:


            connection = DriverManager.getConnection("jdbc:default:connection");
            connection.setAutoCommit(false);
            connection
                    .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

            PreparedStatement insertToIndexPerson = connection
                    .prepareStatement("INSERT INTO index_time_person (index_time, person_email) VALUES (?, ?)");
            insertToIndexPerson.setTimestamp(1, the_time);
            insertToIndexPerson.setString(2, the_person_email);
            insertToIndexPerson.executeUpdate();
            insertToIndexPerson.close();
            connection.commit();


I found both the autocommit mode and isolation level can be modified successfully.
But when I read Derby guide documents (derbydev.pdf "Programming database-side JDBC procedures), I found these items which is obvious forbids what I was doing:

In order to preserve transactional atomicity, database-side JDBC procedures that use
nested connections have the following limitations.
• Cannot issue a commit or rollback, unless called within a CALL statement.
• Cannot change connection attributes such as auto-commit.

If these items are true for my stored procedure, then I cannot modify the isolation level and neither change the autocommit mode.
And I am yet confused about the first statement "Cannot issue a commit or rollback, unless called within a CALL statement." Could a Derby procedure be called without a "CALL statement"?

I am new to Derby and quite curious about these. Thanks!

[Daniel John Debrunner-2]

sin(EaTing), wrote:

> *In order to preserve transactional atomicity, database-side JDBC
> procedures that use
> nested connections have the following limitations.
> • Cannot issue a commit or rollback, unless called within a CALL statement.
> • Cannot change connection attributes such as auto-commit.
>
> *
> If these items are true for my stored procedure, then I cannot modify
> the isolation level and neither change the autocommit mode.

The autocommit mode cannot be changed, it is always false for
server-side connections. Calling setAutoCommit(false) is allowed since
it doesn't change the mode.

Set isolation level I'm not sure about.

> And I am yet confused about the first statement "*Cannot issue a commit
> or rollback, unless called within a CALL statement."* Could a Derby
> procedure be called without a "CALL statement"?

Procedures are only supported in CALL statements. However server-side
jdbc can also exist in functions which can be called from any expression.

So the documentation you found is somewhat incorrect, it could be
improved to use routines when describing functions and procedures, and
just procedures when needed. E.g.

In order to preserve transactional atomicity, database-side JDBC
routines that use nested connections have the following limitations.
  • Can only issue a commit or rollback within a procedure.
  • Cannot change connection attribute auto-commit.

Dan.

[sin(EaTing),]

Thanks, Dan.
The document I read seems already the latest, which is Derby Developer's Guide Version 10.3.
And I am still confused about the isolation mode, I traced the stored procedure using getTransactionIsolation() and the return value shows the isolation level was changed, I am not sure about the whether that data can reflect the real isolation level.

On Feb 15, 2008 11:59 PM, Daniel John Debrunner <djd@...> wrote:

sin(EaTing), wrote:

> *In order to preserve transactional atomicity, database-side JDBC

> procedures that use
> nested connections have the following limitations.
> • Cannot issue a commit or rollback, unless called within a CALL statement.
> • Cannot change connection attributes such as auto-commit.
>
> *
> If these items are true for my stored procedure, then I cannot modify
> the isolation level and neither change the autocommit mode.

The autocommit mode cannot be changed, it is always false for
server-side connections. Calling setAutoCommit(false) is allowed since
it doesn't change the mode.

Set isolation level I'm not sure about.

> And I am yet confused about the first statement "*Cannot issue a commit
> or rollback, unless called within a CALL statement."* Could a Derby
> procedure be called without a "CALL statement"?

Procedures are only supported in CALL statements. However server-side
jdbc can also exist in functions which can be called from any expression.

So the documentation you found is somewhat incorrect, it could be
improved to use routines when describing functions and procedures, and
just procedures when needed. E.g.

In order to preserve transactional atomicity, database-side JDBC

routines that use nested connections have the following limitations.
 • Can only issue a commit or rollback within a procedure.
 • Cannot change connection attribute auto-commit.

Dan.

[Dyre Tjeldvoll]

"sin(EaTing)," <ussraf@...> writes:

> Thanks, Dan.
> The document I read seems already the latest, which is Derby Developer's
> Guide Version 10.3.
> And I am still confused about the isolation mode, I traced the stored
> procedure using getTransactionIsolation() and the return value shows the
> isolation level was changed, I am not sure about the whether that data can
> reflect the real isolation level.

I think that the documentation is incorrect here. It is possible to
modify the isolation level in both a function and a procedure, but not
while a cursor is open because changing the isolation level will commit
the current transaction.

You can see a Derby JUnit test which modifies the
isolation level in a number of ways here

http://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CacheSessionDataTest.java


--
dt

[sin(EaTing),]

That's great. Thanks.

On Feb 16, 2008 6:51 PM, <Dyre.Tjeldvoll@...> wrote:

"sin(EaTing)," <ussraf@...> writes:

> Thanks, Dan.
> The document I read seems already the latest, which is Derby Developer's
> Guide Version 10.3.
> And I am still confused about the isolation mode, I traced the stored
> procedure using getTransactionIsolation() and the return value shows the
> isolation level was changed, I am not sure about the whether that data can
> reflect the real isolation level.

I think that the documentation is incorrect here. It is possible to
modify the isolation level in both a function and a procedure, but not
while a cursor is open because changing the isolation level will commit
the current transaction.

You can see a Derby JUnit test which modifies the
isolation level in a number of ways here

http://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CacheSessionDataTest.java


--
dt