Are addBatch() and executeBatch() thread-safe?

Question

Can i call executeBacth from a thread while another one keeps calling addBatch() on the same Statement ( or PreparedStatement ) object?

Update: Does anyone have exprerience with this issue? 'cause i get incorrect results. Not all updates i added to the the batch is executed.

Answer 1

Yes. According to the JDBC specification, all JDBC driver implementations must be thread safe:

Compliance with the JDBC 3.0 API, section A.1.6

---

If I understand your comment on BalusC' response correctly, you are iterating through the ResultSet from one Statement and operate with other PreparedStatements in a separate thread simultaneously to update other rows. This does not necessarily have to work (again it depends on the JDBC driver, but is not directly related to thread safety). I am not sure about the most recent versions, but older Oracle JDBC drivers did e.g. not support multiple statements, did of course not fail properly, but produced unexpected results as you describe. If I remember correctly, creating a second statement on a connection while iterating through the result set from the first statement would cause the first statement to be silently closed and the first result set only to return the rows already fetched from the database, although more rows could have been available. Your implementation sound similar and may show similar behaviour, also with other databases.

Answer 2

PreparedStatement don't force the implementing classe to make these method thread safe. So quite obvious it depends on implementing class.

For ex:- DelegatingPreparedStatement is having these methods but those are not thread safe, whereas 'OraclePreparedStatement' is also having these methods and those are thread safe.

Answer 3

I would take a step back and deeply reconsider the design. Why on earth would you like to share the same Statement (and thus implicitly also the Connection) between two threads?

The normal JDBC practice is that you should acquire and close the Connection, Statement and ResultSet in the shortest possible scope. That is, inside the very same method block. Here's a basic example:

public void update(List<Item> items) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(sql);
        for (Item item : items) {
            statement.setObject(1, item.getSomething());
            statement.addBatch();
        }
        statement.executeBatch();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }
}

If all you want is just improving connecting performance, then use a connection pool. For example C3P0. But certainly do not share expensive DB resources among threads! This way you also don't need to worry about threadsafety. That's an implementation detail.

Oh, if that's not clear yet: you will not improve database performance by sharing the same statement and connection among multiple threads. Even worse, it would only slowdown and you'll run into threadsafety issues at both the Java and the Database side.

Answer 4

As jambjo pointed out, the spec requires thread safety. However, as Rakesh Juyal pointed out, there is no way to ensure such safety in practice. So if you want to be truly portable and robust, avoid multi-threaded access to the variables as much as possible, unless you are sure the drivers you are using are spec compliant.

As for addBatch and executeBatch, these methods are themselves unreliable in certain cases. I know that whenever I've tried to use these with Oracle drivers (single thread) I got unpredictable results. So maybe thread safety isn't your problem, but rather batches.

Answer 5

It might be very vendor specific for JDBC. I'd rather never rely on concurrent access to batch and delegate the complexity of concurrency to database server. why not to have just more independent connections ?