<< Debugging Deadlocks | Multi-user database access >>

Working with multiple connections

Derby Developer's Guide

In the case of a deadlock you might want to re-try the transaction that was chosen as a
victim. In the case of a lock wait timeout, you probably do not want to do this right away.

The following code is one example of how to handle a deadlock timeout.

/// if this code might encounter a deadlock,
// put the whole thing in a try/catch block
// then try again if the deadlock victim exception
// was thrown
try {
s6.executeUpdate(
"UPDATE employee " +
"SET bonus = 625 "
"WHERE empno='000150'");
s6.executeUpdate("UPDATE project " +
"SET respemp = '000150' " +
"WHERE projno='IF1000'");
}
// note: do not catch such exceptions in database-side methods;
// catch such exceptions only at the outermost level of
// application code.
// See

Database-side JDBC routines and SQLExceptions

catch (SQLException se) {
if (se.getSQLState().equals("40001")) {
// it was chosen as a victim of a deadlock.
// try again at least once at this point.
System.out.println( "Will try the transaction again.");
s6.executeUpdate("UPDATE employee " +
"SET bonus = 625 " +
"WHERE empno='000150'");
s6.executeUpdate("UPDATE project " +
"SET respemp = 000150 " +
"WHERE projno='IF1000'");
}
else throw se;
}

Working with multiple connections to a single database

This section discusses deploying Derby so that many connections can exist to a single
database.

Deployment options and threading and connection modes

A database can be available to multiple connections in several situations.

· Multiple applications access a single database (possible only when Derby is running

inside a server framework).

· A single application has more than one Connection to the same database.

The way you deploy Derby affects the ways applications can use multi-threading and
connections, as shown in the following table.

Table 11.

Threading and connection modes

Connection Mode

Embedded

Server

Multi-Threaded

From an application, using
a single

Connection

a Derby database and
issuing requests against

Supply a single Connection
object to separate threads.
Derby ensures that only
one operation is applied
at a time for consistency.
Server frameworks

Server frameworks can
automatically multi-thread
operations. Remote
client applications can
multi-thread if desired.