db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: is it a good practice to have several connections to an embedded DB
Date Wed, 01 Nov 2006 18:56:22 GMT
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)

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

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

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. :-)

View raw message