StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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.</p> <p>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:</p> <ul> <li>1 query thread</li> <li>NCPU processing threads</li> <li>1 batch update thread</li> </ul> <p>Like a mini db fork-join.</p> <p><strong>Edit</strong></p> <p>Examples on how to do batch update with Pstmt:</p> <pre><code>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(); </code></pre> <p>Or you could query a Queue in the loop where the update requests arrive from the processing threads:</p> <pre><code>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(); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload