db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: I need some advice to choose database for an upcomming job
Date Mon, 07 Nov 2005 19:20:52 GMT
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
>> Oyvind.Bakksjo@Sun.COM wrote:
>>> Note B: If you're running with autocommit OFF, you should definately not
>>> use the same connection object in multiple simultaneous requests (either
>>> use synchronization or create multiple connections).
>> This is true even with automcommit on. Multiple threads using the same
>> connection with ResultSets will mess with each other, as per the JDBC
>> sprc. An executeQuery by one thread will close any ResultSet any other
>> thread is processing.
> Could you elaborate on this?
> I created a small test program which Prepares two statements in separate
> threads, but uses the same connection object. I execute statement A and
> read a few rows from the resultset in thread 1, execute statement B and
> read a few rows from it in thread 2, do a little interleaved reading,
> then read the rest of the rows from both resultsets in their respective
> threads. Got no exception, indicating that no result set was closed.
> I ran this in autocommit mode in both embedded and client/server mode.
> Code excerpt below, in case my explanation of the test program was unclear:
>             t1.prepareStatement();
>             t2.prepareStatement();
>             t1.executeQuery();
>             t1.showRow();
>             t1.showRow();
>             t1.showRow();
>             t2.executeQuery();
>             t2.showRow();
>             t2.showRow();
>             t2.showRow();
>             t1.showRow();
>             t2.showRow();
>             t1.showAll();
>             t2.showAll();
> I have seen some differing behaviour with respect to this in
> client/server and embedded mode before (that's why I made the test), but
> I couldn't reproduce any issue now.

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

So in your example the call to t2.executeQuery() should cause a commit
on the connection which will close the ResultSet for t1.

Your code probably has ResultSets which are held
HOLD_CURSORS_OVER_COMMIT (which is the default) and thus are not closed
by the commit. In addition I guess you showRow() does a next() and then
the rs.gerXXX()? I think if you called t2.executeQuery() between a
next() and the rs.getXXX() calls on the other thread, I think you will
see problems.

Also most likely if you change the ResultSet type to
CLOSE_CURSORS_AT_COMMIT on commit you will see problems.

Thus sharing connections across threads is just problematic unless the
application performs synchronization and/or has very good knowledge of
what others threads are doing at all times. Any application will just be
less error prone if it uses separate connections for separate threads,
isn't one of the reasons to use a relational database to not have to
worry about data synchronization issues? This of course is not specific
to Derby, the JDBC spec specifies this behavviour.

I think there was a long discussion on this about six months ago, lead
by Phil Wilder. He was trying to clarify autocommit mode and held cursor
behaviour in the JDBC spec. It's a little unclear, as for example,
section 10.1 was not updated for held cursors. That was a case where the
wiki would have been useful, to present a summary of the current discussion.

And as you say, I think that discussion was driven by differences in the
client and embedded drivers in this area.


View raw message