Re: Concurrency in connections and Threads
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. CraigOn Nov 10, 2005, at 7:33 AM, Arieh Markel wrote:I have autocommit on, so - like you say - I am safe.Thanks,AriehLars 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 followingprocessing populates different tables.So far, in my implementation, all threads shared the same connection.I am wondering whether there are any resulting concurrency issuesthat 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 DerbyBased 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. Sothe following scenario (serially executed):Turn autocommit offThread 1 executes update of table AThread 2 executes update of table BThread 2 executes a rollbackwould cause the update of table A to be rolled back as well. Ifautocommit is on, you're safe from this particular scenario.-Lars -- Arieh Markel Sun Microsystems Inc.CNS CTO - Advanced Technology 500 Eldorado Blvd. MS UBRM05-169e-mail: [EMAIL PROTECTED] Broomfield, CO 80021http://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!
Re: Concurrency in connections and Threads
On Thu, 2005-11-10 at 18:42, Michael Segel wrote: > On Thursday 10 November 2005 09:33, Arieh Markel wrote: > Safe? I agree in your scepticism. It said he was safe from that particular scenario, but I don't enough to say that having autocommit on keeps him same from other similar things. Just wanted to bring up the scenario. > > Now if you think about it, why would a database engine want to manage > multiple > transactions per connection? Keep in mind that you don't want to create a > situation where you have nested transactions. (This would get ugly fast) So > you would have to implement this in a manner that > > From a design perspective, do the potential benefits outweigh the development > costs? In this case, I'm not sure. Which reminds me: We're currently doing our own very small connection pool (basically one connection per thread) exactly to avoid these kinds of problems. Does Derby connection pooling say anything about what happens with multiple threads? Or is there some other way to ensure connection-per-thread? > Can anyone provide an example where the cost of opening a connection is too > expensive and that implementing multiple transactions in a single thread is > better? Supposedly for connecting to network servers. > As a side note, this would actually be a good use of Derby. To allow for > easier exploration of potential advancements in DB theory. > > To do this problem, you'd also have to extend the SQL language a little. > > But hey, what do I know? The mind doesn't start to function until after the > 2nd slice of pizza and the third beer. ;-) Indeed. Where's mine? -Lars
Re: Concurrency in connections and Threads
On Thursday 10 November 2005 09:33, Arieh Markel wrote: Safe? Its not really a good idea to share your connection like that. Just because you can do it, doesn't mean its a good idea. ;-) Its possible for newer JDBC API implementations to allow for multiple transactions per connection, but then you'd have to expect that the DB manufacturer is willing to also implement this on the database side. (Ok, so I've given this a little bit of thought...) Now if you think about it, why would a database engine want to manage multiple transactions per connection? Keep in mind that you don't want to create a situation where you have nested transactions. (This would get ugly fast) So you would have to implement this in a manner that From a design perspective, do the potential benefits outweigh the development costs? In this case, I'm not sure. Can anyone provide an example where the cost of opening a connection is too expensive and that implementing multiple transactions in a single thread is better? As a side note, this would actually be a good use of Derby. To allow for easier exploration of potential advancements in DB theory. To do this problem, you'd also have to extend the SQL language a little. But hey, what do I know? The mind doesn't start to function until after the 2nd slice of pizza and the third beer. ;-) > 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 -- Michael Segel Principal MSCC
Re: Concurrency in connections and Threads
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
Re: Concurrency in connections and Threads
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
Re: Concurrency in connections and Threads
Arieh Markel wrote: My assumptions are as follows: - table lock granularity is what is in place in Derby Are those assumptions true ? Just wanted to point out , that by default Derby is configured for row-level locking. http://db.apache.org/derby/docs/10.0/manuals/develop/develop73.html#IDX690 This talks about a property derby.storage.rowLocking which is not there in tuning manual. I think the documentation is off in that respect. Derby supports the LOCK TABLE statement which can be used to explicitly acquire a shared or exclusive table lock. http://db.apache.org/derby/docs/dev/ref/rrefsqlj40506.html Sunitha.
Re: Concurrency in connections and Threads
Hi Arieh, I haven't run any experiments but I would expect that you would have to be careful about how you commit this work. If one thread commits the connection while another thread is in the middle of a statement, someone is likely to be disappointed. You will likely have deadlock issues if you try to scale your application by running two teams of threads, each working on its own connection. Regards, -Rick 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 ? Thanks, Arieh PS: please, reply directly, as I am not in the aliases ...
