Example of threads sharing a statement This example shows what can happen if two threads try to share a single Statement. Statements shared by threadsdangers of 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 can 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, 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.