Hi Arieh,

The situation is complicated if you run multiple statements using the same connection and don't exhaust the ResultSet before returning the connection to the pool.

If all you are doing is insert/update/delete then the statements are essentially complete when they are executed. Just watch out for queries. For best portability, a connection should be owned by a thread until all its statements have been completely executed and its result sets closed. Then the connection can be put back into the pool for another thread to use. 

Craig

On Nov 10, 2005, at 7:33 AM, Arieh Markel wrote:

I have autocommit on, so - like you say - I am safe.

Thanks,

Arieh

Lars Clausen wrote On 11/10/05 08:28,:

On Wed, 2005-11-09 at 18:18, Arieh Markel wrote:

 

I am using Derby in 'embedded' mode.

A pool of worker threads takes jobs from a queue and following
processing populates different tables.

So far, in my implementation, all threads shared the same connection.

I am wondering whether there are any resulting concurrency issues
that I may not be aware of.

My assumptions are as follows:

- the threads are well behaved among themselves relative to (java) concurrency

- no two threads update the same database table at any given moment

- table lock granularity is what is in place in Derby

Based on that, the same connection (albeit processing different tables)
may be used by different threads without creating unnecessary contention.

Are those assumptions true ?

   


You should be aware that each connection only has one transaction.  So
the following scenario (serially executed):

Turn autocommit off
Thread 1 executes update of table A
Thread 2 executes update of table B
Thread 2 executes a rollback

would cause the update of table A to be rolled back as well.  If
autocommit is on, you're safe from this particular scenario.

-Lars


 


-- 
Arieh Markel                           Sun Microsystems Inc.
CNS CTO - Advanced Technology          500 Eldorado Blvd. MS UBRM05-169
e-mail: arieh.markel@sun.COM           Broomfield, CO 80021
http://blogs.sun.com/arieh             Phone: (303) 272-8547 x78547


Craig Russell

Architect, Sun Java Enterprise System http://java.sun.com/products/jdo

408 276-5638 mailto:Craig.Russell@sun.com

P.S. A good JDO? O, Gasp!