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 11:51:57 GMT
Daniel John Debrunner wrote:
> 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.

You're right, after changing this to CLOSE_CURSORS_AT_COMMIT I got 

> 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.

I tried that, but that did not change any behaviour. Besides, why would 
it? It doesn't seem logical to me why you would get an exception exactly 
there, if the resultset isn't closed and you don't get an exception on 
the subsequent next() and rs.getXXX() calls after the second execute.

> 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 agree. Just to be clear, I am not arguing that anyone should code 
applications that way, I'm just trying to figure out exactly how Derby 

Although sharing connections between unrelated threads which perform 
different tasks is not a good idea, I can imagine there are cases where 
one would benefit from having multiple open resultsets on a connection 
with autocommit off. This should be allowed, right? Also, Derby should 
be agnostic to whether these result sets are processed in different threads.

Actually, this is what I do in jdbcapi/SetQueryTimeoutTest.java, in 
order to verify that the correct statement is affected by the timeout 
when multiple statements are executing concurrently. What's strange is 
that this works in embedded mode but fails in client/server mode (I'm 
about to submit an implementation of setQueryTimeout for the client 
driver). The little test program I wrote yesterday works in both modes, 
however. I don't see what I'm doing differently - in both programs I 
have autocommit off, HOLD_CURSORS_OVER_COMMIT (by default), and have two 
threads that prepare and execute a select query on the same connection, 
then traverse the result sets. Still one program gets a closed resultset 
in client/server mode, the other doesn't. I must be overlooking 
something. Any ideas?

Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway

View raw message