Embedded - can it be multi-user?

[Paul Linehan <li...@tcd.ie>]

Hi all,



I've just started to use Derby and really like it. I've come from
a Firebird embedded environment with Java, but the
fact that Derby is written in Java is a decisive factor
in its favour - its feature set is also impressive.


However, there's one thing that's confusing me. First thing I did
was go to the "getstartderby.pdf" docco and go through the
tutorial. All worked fine, however I was confused by this bit


(End of page 33, start of 34)
-------------------------
Derby's two architectures have caused confusion for some new Derby
users, who mistakenly think that embedded is a single-user
configuration. This is not true. The embedded driver supports multiple
simultaneous connections, performs locking, and provides performance,
integrity, and recoverability. Any application that uses the Getting Started
with Derby embedded driver can open multiple Derby connections and
then provide a means for multiple users to interact with the database
on each connection.
The Derby Network Server is an example of such an application.
-------------------------

Now, I was using the SQuirreL SQL Client to look at my databases
as they were being created, but I couldn't use ij with the database
*_and_* SQuirreL at the same time.

What I'm wondering is, if there's a single app connecting to the database
multiple times, is it up to the app to manage the connections so that
only one connection is active at any one time, or how exactly does that
work? Say in a context where an App Server is connecting to an embedded
Derby database - i.e. no server running - does the App Server have to manage
requests to the database in a queue or how, exactly does the system work?

TIA for any pointers, tips, clues, references, URL's or anything else useful.


Rgs,


Paul...


--


linehanp@tcd.ie

Mob: 00 353 86 864 5772

Re: Embedded - can it be multi-user?

[Bryan Pendleton <bp...@gmail.com>]

> Now, I was using the SQuirreL SQL Client to look at my databases
> as they were being created, but I couldn't use ij with the database
> *_and_* SQuirreL at the same time.

Correct. Two separate JVMs cannot both access the same database using
the embedded driver concurrently.

> What I'm wondering is, if there's a single app connecting to the database
> multiple times, is it up to the app to manage the connections so that
> only one connection is active at any one time, or how exactly does that
> work? Say in a context where an App Server is connecting to an embedded
> Derby database - i.e. no server running - does the App Server have to manage
> requests to the database in a queue or how, exactly does the system work?

Java is multi-threaded, and Derby's JDBC apis (the java.sql.* interfaces)
are thread safe. Multiple connections can be opened and used concurrently.

So a well-written application which processes work on behalf of multiple
users can certainly open multiple separate connections to the database,
and each connection (in a separate thread) can be performing
work concurrently using the same embedded driver, so long as this is all
a single JVM. (More precisely, it must be a single class-loader in a single
JVM; separate class loaders behave like separate JVMs w.r.t embedded use.)

Derby's NetworkServer is an example of such a well-written application, but
it is not the only one possible. Still, many people find it convenient
to use Derby's NetworkServer rather than writing their own. The
NetworkServer even has some simple support for being managed by an
application server: http://db.apache.org/derby/docs/10.8/adminguide/cadminservlet98430.html

thanks,

bryan