Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: Michael Segel <msegel@segel.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Concurrency in connections and Threads
Date: Thu, 10 Nov 2005 11:42:53 -0600
User-Agent: KMail/1.8
References: <4358EE8A.9070503@Sun.COM>
 <1131636511.19880.44.camel@pc977.sb.statsbiblioteket.dk>
 <43736835.2000006@Sun.COM>
In-Reply-To: <43736835.2000006@Sun.COM>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200511101142.53141.msegel@segel.com>

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=20
transactions per connection, but then you'd have to expect that the DB=20
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 multi=
ple=20
transactions per connection? Keep in mind that you don't want to create a=20
situation where you have nested transactions. (This would get ugly fast) So=
=20
you would have to implement this in a manner that=20

=46rom a design perspective, do the potential benefits outweigh the develop=
ment=20
costs? In this case, I'm not sure.

Can anyone provide an example where the cost of opening a connection is too=
=20
expensive and that implementing multiple transactions in a single thread is=
=20
better?

As a side note, this would actually be a good use of Derby. To allow for=20
easier exploration of potential advancements in DB theory.=20

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=
=20
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 contentio=
n.
> >>
> >>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

=2D-=20
Michael Segel
Principal=20
MSCC