![]() |
Controlling Cloudscape Application Behavior
|
Reference Manual |
Working with Multiple Threads Sharing a Single ConnectionJDBC allows you to share a single Connection among multiple threads.
Pitfalls of Sharing a Connection Among ThreadsHere is a review of the potential pitfalls of sharing a single Connection among multiple threads:
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. Reasons for Trying to Share a Connection or Statement Across ThreadsSome programmers may share a Connection among multiple threads because they have experienced bad concurrency using separate transactions. Here are some tips for increasing concurrency:
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 StudyThis 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |