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
