Re: Concurrency in connections and Threads

[Craig L Russell]

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: [EMAIL PROTECTED]           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:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!