From derby-user-return-2612-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Nov 11 15:03:32 2005 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 3674 invoked from network); 11 Nov 2005 15:03:31 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 11 Nov 2005 15:03:31 -0000 Received: (qmail 83276 invoked by uid 500); 11 Nov 2005 15:03:30 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 83233 invoked by uid 500); 11 Nov 2005 15:03:29 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 83222 invoked by uid 99); 11 Nov 2005 15:03:29 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 11 Nov 2005 07:03:29 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.98.34] (HELO brmea-mail-3.sun.com) (192.18.98.34) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 11 Nov 2005 07:03:21 -0800 Received: from fe-amer-10.sun.com ([192.18.108.184]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id jABF373F009145 for ; Fri, 11 Nov 2005 08:03:07 -0700 (MST) Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0IPS00001OBAQQ00@mail-amer.sun.com> (original mail from Craig.Russell@Sun.COM) for derby-user@db.apache.org; Fri, 11 Nov 2005 08:03:07 -0700 (MST) Received: from [192.168.0.10] ([24.6.172.77]) by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0IPS00457PSQ971D@mail-amer.sun.com> for derby-user@db.apache.org; Fri, 11 Nov 2005 08:02:51 -0700 (MST) Date: Fri, 11 Nov 2005 07:02:50 -0800 From: Craig L Russell Subject: Re: Concurrency in connections and Threads In-reply-to: <43736835.2000006@Sun.COM> Sender: Craig.Russell@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 X-Mailer: Apple Mail (2.746.2) Content-type: multipart/alternative; boundary="Boundary_(ID_zNklnx2g9YJz1lM9nCWmrQ)" References: <4358EE8A.9070503@Sun.COM> <4359717E.6070105@sun.com> <43597A7C.8090309@Sun.COM> <43597E0F.2030209@sun.com> <435981B8.6070600@Sun.com> <43598337.1040309@sun.com> <435D0F95.7080100@Sun.COM> <43721C2A.9080208@Sun.COM> <437222DD.8030400@sun.com> <43722F6E.3060002@Sun.COM> <1131636511.19880.44.camel@pc977.sb.statsbiblioteket.dk> <43736835.2000006@Sun.COM> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --Boundary_(ID_zNklnx2g9YJz1lM9nCWmrQ) Content-type: text/plain; format=flowed; delsp=yes; charset=US-ASCII Content-transfer-encoding: 7BIT 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! --Boundary_(ID_zNklnx2g9YJz1lM9nCWmrQ) Content-type: text/html; charset=ISO-8859-1 Content-transfer-encoding: QUOTED-PRINTABLE 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.=A0For best porta= bility, a connection should be owned by a thread until all its statem= ents have been completely executed and its result sets closed. Then t= he connection can be put back into the pool for another thread to use= .=A0

Craig=

On Nov 10, 2005, at 7:33 AM, Arieh Markel wr= ote:

I have autocommit on, so - like you sa= y - 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:

= =A0

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.

M= y assumptions are as follows:

- the threads are well behaved am= ong themselves relative to (java) concurrency

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

- table l= ock 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 unnecess= ary contention.

Are those assumptions true ?

=A0 =A0


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

Turn autocommit off
Thread 1 exe= cutes update of table A
Thread 2 executes u= pdate of table B
Thread 2 executes a rollba= ck

would cause the update of table A to be rolled back as wel= l.=A0 If
autocommit is on, you're safe from this particular scenari= o.

-Lars


=A0

=

--=A0
Arieh Markel =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Sun Mi= crosystems Inc.
CNS CTO - Advanced Technolo= gy=A0 =A0 =A0 =A0 =A0 50= 0 Eldorado Blvd. MS UBRM05-169
e-mail: arieh.markel@sun.COM =A0 =A0 =A0 =A0 =A0 Broomfield, = CO 80021
http://blogs.sun.com/arieh =A0 =A0 =A0 =A0 =A0 =A0 Phone: (303) 272-8547 x78547


Craig Russ= ell

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!


--Boundary_(ID_zNklnx2g9YJz1lM9nCWmrQ)--