<< Working with multiple connections | Pitfalls of sharing a connection among threads >>

Multi-user database access

Derby Developer's Guide

Connection Mode

Embedded

Server

that connection in multiple
threads.

automatically manage
multi-threaded operations..

Multi-Connection

From an application, using
multiple connections to a
Derby database and issuing
requests against those
connections on multiple
threads.

Create individual
connections within a single
application and use the
appropriate connection for
each JDBC request. The
connections can all be to
the same database, or can
be to different databases in
the same Derby system.

Remote client applications
can establish the multiple
connections desired.

Multi-User

Multiple applications (or
JVMs) accessing the same
Derby database. Each user
application has its own
connection or connections
to the database.

Not possible. Only one
application can access
a database at a time,
and only one application
can access a specific
system at a time. When
using a pre-1.4 JVM,
Derby might not prevent
multiple applications from
concurrently accessing the
same Derby system, but
do not allow this because
such access can corrupt
the databases involved.

Only one server should
access a database at a
time. Multiple remote client
applications can access the
same server, and thus can
access the same database
at the same time through
that server.

Multi-user database access

Multi-user database access is possible if Derby is running inside a server framework.

If more than one client application tries to modify the same data, the connection that
gets the table first gets the lock on the data (either specific rows or the entire table).
The second connection has to wait until the first connection commits or rolls back the
transaction in order to access the data. If two connections are only querying and not
modifying data, they can both access the same data at the same time because they can
each get a shared lock.

Multiple connections from a single application

A single application can work with multiple Connections to the same database and assign
them to different threads.

You can avoid concurrency and deadlock problems in your application in several ways:

· Use the TRANSACTION_READ_COMMITTED isolation level and turn on row-level

locking (the defaults).

· Beware of deadlocks caused by using more than one Connection in a single thread

(the most obvious case). For example, if the thread tries to update the same table
from two different

Connections

, a deadlock can occur.

· Assign Connections to threads that handle discrete tasks. For example, do not have

two threads update the Hotels table. Have one thread update the Hotels table and a
different one update the Groups table.

· If threads access the same tables, commit transactions often.