Re: is it a good practice to have several connections to an embedded DB

[Stanley Bradbury <St...@gmail.com>]

legolas wood wrote:
> Thank you for your comments.
> should i use a pooling library to open several connection on Embedded
> Derby database or i can use normal method(Driver manager....) to do it ?
>
> Thanks
> Daniel Jue wrote:
>> If you mean several different connections, this should be fine.  I'm
>> pretty sure that's how connection pooling works:  You have several
>> (lets say 20) connections that are pre-established, and then those get
>> handed out to other processes/services as needed, and then when those
>> processess/services "close" the connection, it really just releases it
>> back to the pool.  This is because setting up the initial connections
>> can be time consuming.
>>
>> (Note for the following paragraph I do not have any connection pooling
>> explicitly set up, I'm still working on how to do that...I am using
>> Derby as an embedded DB, although the actual data lives at a location
>> on my web app server, but outside the scope of my web application,
>> i.e. C:\mydbdir\ .)
>>
>> Now, with one of my programs, using a jdbc connection, I use the
>> "default" connection string, which I believe re-uses the existing
>> connection.  For instance, I have a couple Java stored procedures for
>> my Derby db, and sometimes one gets called from the other.  The
>> sub-procedure uses a default connection.  It sure looks like I'm
>> making a new connection, since I'm creating a new connection object,
>> but my understanding is that it somehow re-uses an existing
>> connection.
>>
>> Dan
>>
>>
>>
>> On 10/29/06, legolas wood 
>> <le...@public.gmane.org> wrote:
>>> Hi
>>> thank you for reading my post
>>> is it a good practice to have several connections to an embedded DB ?
>>> what will happen when we open several connections to an embedded DB ?
>>>
>>> Thanks
>>>
Hi Legolas -
The main advantage I see to having multiple connections open to an 
embedded database is to allow multiple concurrent transactions to 
execute.  When only one connection is open you can only have one SQL 
statement executing at a time. 

So I guess it comes down to the type of processing you are doing.  My 
thoughts on the use of a connection pool are similar; If you do not want 
to maintain one or more open connection across many operations but will 
be regularly connecting and disconnection from the database as needed 
then a connection pool might make sense.  The benefit I see to 
connection pools is they allow many threads to share a smaller number of 
database connections thus reducing the number of open connections at any 
one time.  Keep in mind, there is overhead in using a connection pool so 
I would not implement one unless there is a specific requirement.

HTH

Re: is it a good practice to have several connections to an embedded DB

[Daniel John Debrunner <dj...@apache.org>]

Stanley Bradbury wrote:

> The main advantage I see to having multiple connections open to an 
> embedded database is to allow multiple concurrent transactions to 
> execute.  When only one connection is open you can only have one SQL 
> statement executing at a time.

The last sentence above is true but also not true. :-)
It depends on what is meant by "executing".

In Derby only one statement per connection can be actively executing on 
any thread, but the connection may have multiple open queries that are 
in the middle of execution. Ie. this is valid, assuming the connection 
is not in auto-commit mode.

ResultSet rs1 = ps1.executeQuery();
ResultSet rs2 = ps2.executeQuery();

Both of these queries are open and the application can interleave 
rs1.next() and rs2.next() calls as needed in any order, e.g.

while (rs1.next())
{
   if (rs.getInt(1) == 77)
   {
       rs2.next();
   }
}

The application can also execute DDL or DML statements while these 
queries are open, e.g.

while (rs1.next())
{
   if (rs.getInt(1) == 77)
   {
       rs2.next();
       int value = rs2.getInt(2);
       ps3.setInt(1, value);
       ps3.executeUpdate();
   }
}

The single actively executing comes in if two threads tried to execute 
any action using the same connection, e.g. if T1 executed rs1.next() and 
T2 rs2.next() then T2 would block until the rs1.next() had returned for 
T1. I would strongly recommend not sharing a JDBC connection across 
multiple threads, it basically means you've pushed database 
synchronization requirements into the application.

Of course, maybe Stan meant "only one SQL transaction", which would be 
completely true. :-)
Dan.