JDBC and Threading

java, jdbc, oracle

Solution

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();

Problem

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.

Original source