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 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, Derby 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.