Controlling Cloudscape Application Behavior
Page 6 of 7

Working with Multiple Threads Sharing a Single Connection

JDBC allows you to share a single Connection among multiple threads.

Pitfalls of Sharing a Connection Among Threads

Here is a review of the potential pitfalls of sharing a single Connection among multiple threads:

  • Committing or rolling back a transaction closes all open ResultSet objects and currently executing Statements.

    If one thread commits, it closes the Statements and ResultSets of all other threads using the same connection.

  • Executing a Statement automatically closes any existing open ResultSet generated by an earlier execution of that Statement.

    If threads share Statements, one thread could close another's ResultSet.

In many cases, it is easier to assign each thread to a distinct Connection. If thread A does database work that is not transactionally related to thread B, assign them to different Connections. For example, if thread A is associated with a user input window that allows users to delete hotels and thread B is associated with a user window that allows users to view city information, assign those threads to different Connections. That way, when thread A commits, it does not affect any ResultSets or Statements of thread B.

Another strategy is to have one thread do queries and another thread do updates. Queries hold shared locks until the transaction commits in SERIALIZABLE isolation mode; use READ_COMMITTED instead.

Yet another strategy is to have only one thread do database access. Have other threads get information from the database access thread.

However, in many cases, the programmer decides to have multiple threads accessing the database share a single Connection and transaction. Multiple threads are permitted to share a Connection, Statement, or ResultSet. However, the application programmer must ensure that one thread does not affect the behavior of the others.

Recommended Practices

Here are some tips for avoiding unexpected behavior:

  • Try not to share Statements (and their ResultSets) among threads.
  • Each time a thread executes a Statement, it should process the results before relinquishing the Connection.
  • Each time a thread accesses the Connection, it should consistently commit or not, depending on application protocol.
  • Have one thread be the "managing" database Connection thread that should handle the higher-level tasks, such as establishing the Connection, committing, rolling back, changing Connection properties such as auto--commit, closing the Connection, shutting down the database (in an embedded environment), and so on.
  • Close ResultSets and Statements that are no longer needed in order to release resources.

Reasons for Trying to Share a Connection or Statement Across Threads

Some programmers may share a Connection among multiple threads because they have experienced bad concurrency using separate transactions. Here are some tips for increasing concurrency:

  • Use row-level locking.
  • Use the READ_COMMITTED isolation level.
  • Avoid queries that cannot use indexes; they require locking of all the rows in the table (if only very briefly) and may block an update.

In addition, some programmers may share a statement among multiple threads to avoid the overhead of each thread's having its own. If you use stored prepared statements, threads can share the same statement from different connections using the stored prepared statement cache. For more information, see Additional Benefits for Multiple Connections: The Stored Prepared Statement Cache in Tuning Cloudscape.

Threads Sharing a Statement: A Case Study

This example shows what can happen if two threads try to share a single Statement.

PreparedStatement ps = conn.prepareStatement(
    "UPDATE account SET balance =  balance + ? WHERE id = ?");
/* now assume two threads T1,T2 are given this
java.sql.PreparedStatement object and that the following events
happen in the order shown (pseudojava code)*/
T1 - ps.setBigDecimal(1, 100.00);
T1 - ps.setLong(2, 1234);
T2 -  ps.setBigDecimal(1, -500.00);
// *** At this point the prepared statement has the parameters
// -500.00 and 1234
// T1 thinks it is adding 100.00 to account 1234 but actually
// it is subtracting 500.00
T1 - ps.executeUpdate();
T2 - ps.setLong(2, 5678);
// T2 executes the correct update
 T2 - ps.executeUpdate();
/* Also, the auto-commit mode of the connection may lead
to some strange behavior.*/

If it is absolutely necessary, the application can get around this problem with Java synchronization.

If the threads each obtain their own PreparedStatement (with identical text), their setXXX calls do not interfere with each other. Moreover, Cloudscape is able to share the same compiled query plan between the two statements; it needs to maintain only separate state information. However, there is the potential for confusion in regard to the timing of the commit, since a single commit commits all the statements in a transaction.