db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: I need some advice to choose database for an upcomming job
Date Tue, 08 Nov 2005 16:09:57 GMT
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
> 
>> My thinking is that the executeQuery() will cause a commit which will
>> cause the open held ResultSet used by the other thread to move off the
>> row, as required by the SQL standard. Thus before any getXXX() call can
>> be made, the held cursor needs to be re-positioned using next(). I think
>> you have said before that Derby doesn't act this way, though looking at
>> the embedded code it should.
> 
> 
> So I tried the following:
> 
>             conn.setAutoCommit(true);
>             Statement st1 = conn.createStatement();
>             try {
>                 st1.execute("drop table t1");
>             } catch (SQLException e) {
>                 // Ignore
>             }
>             st1.execute("create table t1 (a int)");
>             st1.execute("insert into t1 
> values(0),(1),(2),(3),(4),(5),(6)");
> 
>             PreparedStatement ps1 = conn.prepareStatement("select * from 
> SYS.systables",
> 
> ResultSet.TYPE_FORWARD_ONLY,
> 
> ResultSet.CONCUR_READ_ONLY,
> 
> ResultSet.HOLD_CURSORS_OVER_COMMIT);
>             PreparedStatement ps2 = conn.prepareStatement("select * from 
> t1",
> 
> ResultSet.TYPE_FORWARD_ONLY,
> 
> ResultSet.CONCUR_READ_ONLY,
> 
> ResultSet.HOLD_CURSORS_OVER_COMMIT);
>             ResultSet rs1 = ps1.executeQuery();
>             ResultSetMetaData md1 = rs1.getMetaData();
>             rs1.next();
>             show(rs1, md1);
>             rs1.next();
>             ResultSet rs2 = ps2.executeQuery();
>             ResultSetMetaData md2 = rs2.getMetaData();
>             show(rs1, md1);
>             rs2.next();
>             show(rs2, md2);
>             rs2.next();
>             show(rs2, md2);
>             rs1.next();
>             show(rs1, md1);
>             rs2.next();
>             show(rs2, md2);
> 
> 
> (The show method calls ResultSet.getString on all columns.)
> 
> As you can see, the second executeQuery() call is between rs1.next() and 
> show(rs1, md1). There's no exception thrown - no need to reposition, it 
> seems. I changed the prepareStatement for ps1 to use 
> CLOSE_CURSORS_AT_COMMIT, and then I got an exception about the result 
> set being closed, so there's definitely a commit made at the second 
> executeQuery.

I think I am finally beginning to grasp what is going on here. :)

-----------------------
JDBC 3.0 - Section 10.1.

A commit occurs when a statement is complete in auto-commit mode.
For Select statements, the statement is complete when the associated
result set is closed. The result set is closed as soon as one of the
following occurs:
   ...
   another Statement object is executed on the same connection
------------------------

Reading this from the bottom and up, what happens when running with 
CLOSE_CURSORS_AT_COMMIT is that executing the other statement causes the 
first result set to be closed, causing the statement to complete, 
causing the transaction to commit.

Now, if we have HOLD_CURSORS_OVER_COMMIT, we don't want the result set 
to be closed after the commit. But since closing the result set is what 
triggers the commit, it would mean that the result set would first have 
to be closed (in order to trigger the commit) and then, after the 
commit, to not be closed after all! What I suspect is that when we have 
specified HOLD_CURSORS_OVER_COMMIT, the second execute does not close 
the result set in the first place (and here the spec quote above is 
vague, it does not state anything about holdability), thus never causing 
a commit.

Quite funny, then, that whether the second execute triggers a commit 
depends on the holdability of other result sets.

-- 
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/

Mime
View raw message