JDBC and Threading

Question

I have a program that needs to query a database in a given interval and with the records it gets call perform some action, then update the table again.

I am using the ExecutorService to run the threads, but am wondering, should each thread get its own connection (because it needs to update database) or can I use the same connection I used to query the initial results?

Can connection pooling work, am on Oracle 9i.

Answer 1

You should always use separate connection for separate threads as the drivers are not thread safe in this manner. A connection pool could help you, as it allows connection reuse in a safe manner.

You could also do a query-dispatch pattern - if I understand your problem correctly -, where one thread is responsible for the querying and starts of N threads which might update the database - all of which have separate connection objects.

You might also consider doing a batch update via the PreparedStatement, so threads don't stumble upon each other in terms of row and table locks, using the following structure:

• 1 query thread
• NCPU processing threads
• 1 batch update thread

Like a mini db fork-join.

Edit

Examples on how to do batch update with Pstmt:

PreparedStatement pstmt = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?");
for (int i = 0; i < 100; i++) {
    pstmt.setInt(1, i * i);
    pstmt.setInt(2, i);
    pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();

Or you could query a Queue in the loop where the update requests arrive from the processing threads:

class WhatToUpdate {
    public int id;
    public int value;
}
Queue<WhatToUpdate> queue = new LinkedBlockingQueue<WhatToUpdate>();

PreparedStatement pstmt = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?");

int poisons = THE_NUMBER_OF_PROCESSING_THREADS;
while (true) {
    WhatToUpdate record == queue.take();
    if (record == null) { // poison pill
        if (--poisons <= 0) {
            break;
        }
    }
    pstmt.setInt(1, record.value);
    pstmt.setInt(2, record.id);
    pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();

Answer 2

If you look at the code for the Oracle jdbc OracleConnection and OraclePreparedStatement, most of the important methods are synchronized, so they are 'thread-safe.' Using a single Connection across multiple threads will not give you the greatest efficiency, as each thread will need to wait on the operations of the Connectionand PreparedStatement(especially execute) to complete before performing their own action.