Does synchronized lock a Result Set object?

Question

I'm trying to multi thread a Result Set. I want to make sure whenever I call the next() within one of the many threads, all other threads are locked out. This is important , because if many threads call the next() method simultaneously, this will result in skipping the rows. Here is what I did

public class MainClass {
    private static ResultSet rs;

    public static void main (String [] args) {

        Thread thread1  = new Thread(new Runnable() {
            @Override
            public void run() {
                runWhile();
            }});
        Thread thread2  = new Thread(new Runnable() {
            @Override
            public void run() {
                runWhile();
            }});

        thread1.start();
        thread2.start();
        thread1.join();
        thread2.join();

        System.exit(0);
    }

    private static void runWhile () {
        String username = null;
        while ((username = getUsername()) != null) {
            // Use username to complete my logic 
        }
    }

    /**
     * This method locks ResultSet rs until the String username is retrieved. 
     * This prevents skipping the rows 
     * @return
     * @throws SQLException
     */
    private synchronized static String getUsername() throws SQLException {
        if(rs.next()) {
            return  rs.getString(1).trim();
        }
        else
            return null;
    }
}

Is this a correct way of using synchronized. Does it lock the ResutSet and makes sure other thread do not interfere ?

Is this a good approach ?

Answer 1

JDBC objects shouldn't be shared between threads. That goes for Connections, Statements, and ResultSets. The best case here would be that the JDBC vendor follows the spec and does internal locking so that you can get by with this, in which case all the threads are still trying to acquire the same lock and only one can make progress at a time. This will be slower than using a single thread, because on top of doing the same work to read from the database there is extra overhead from managing all the threading.

(Locking done by the driver could be for the driver's benefit, so the provider doesn't have to deal with bug reports of race conditions caused by users misusing their software. That it does locking doesn't necessarily imply the software should actually be used by multiple threads.)

Multithreading works when threads can make progress concurrently, see Amdahl's Law. If you have a situation where you can read the ResultSet and use the results to create tasks which you submit to an ExecutorService (as Peter Lawrey recommends in a comment) then that would make more sense (as long as those tasks can work independently and don't have to wait on each other).