java dbcp2 multithreaded connection access

Question

I'm pretty familiar using connection pooling on tomcat & have used it for years without problem. However at the moment I'm working on a main method application that needs to run simultaneous threads for performance reasons, and those threads each need to access the same database. I've gotten my code to work if I strip out database code altogether & just use arrays for test purposes (e.g. multithreading works) however as soon as I add back in database connections, the first thread takes the lock and the other threads don't run at all. Have played with c3p0, and dbcp2; currently working with dbcp2. Thanks! There's tons of documentation out there, but not many code samples that seem specific to my use case. Here's a sample app:

import java.sql.*;
import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDataSource;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.dbcp2.Utils;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;

public class SandboxApp {

    private static BasicDataSource dataSource;

    public static BasicDataSource getDataSource() {

        if (dataSource == null) {

            BasicDataSource ds = new BasicDataSource();

            ds.setUrl("jdbc:mysql://localhost:3306/my-db");
            ds.setUsername("root");
            ds.setPassword("");

            ds.setDriverClassName("org.mariadb.jdbc.Driver");

            ds.setInitialSize(3);
            ds.setMaxTotal(25);
            ds.setMinIdle(0);
            ds.setMaxIdle(8);
            ds.setMaxOpenPreparedStatements(100);

            dataSource = ds;
        }
        return dataSource;
    }

    public static void main(String [] args) throws Exception{   
       for(int i=0; i<11; i++){//spawn 11 threads & get each thread to process 600k sql rows at the same time
              new Thread("" + (i*600000)){

                public void run(){
                    System.out.println("Thread: " + getName() + " running");//prints correctly for all threads

                    Connection con = null;
                    PreparedStatement pstmt = null;
                    ResultSet rs = null;
                try {           
                    con = SandboxApp.getDataSource().getConnection();

                    pstmt = con.prepareStatement("select something from some_table limit "+getName()+",600000");
                    rs=pstmt.executeQuery();
                    while(rs.next()){  
                        System.out.println("Doing stuff for thread "+getName());//this only prints for getName() == 0
                            //give the other threads a turn...
                            try {
                                Thread.sleep(10);
                            }
                            catch(InterruptedException ex) {
                            }
                    }

                  } catch (Exception e) {
                    e.printStackTrace();
                  }finally{
                      try {pstmt.close();} catch (SQLException e) {}
                      try { con.close(); } catch(Exception e) {}
                      try { rs.close(); } catch(Exception e) {}
                  }
                }
              }.start();
        }
    }
}

Your datasource initialization is racy. Multiple threads could initialize the datasource, you may end up with multiple instances of datasource, and/or a thread may get a datasource that is improperly initialized. I suggest you move datasource initialization outside the threads, so datasource is available to all the threads. — Burak Serdar, May 29 '20 at 22:01
Yes, getDataSource() should be synchronized as it manages a resource shared between the threads.. I've tried this already, still doesn't solve for thread 0 not letting the other threads connect to the DB.. @BurakSerdar user207 any ideas? — Reece, May 31 '20 at 16:42
Move the call to getDataSource() outside the thread. Initialize it before you create the threads. Datasource access is usually thread safe, but double check to confirm. If one thread is blocking all the others, maybe your datasource is using a pool of size 1? — Burak Serdar, May 31 '20 at 18:25
@user207421 yes you're right; simply adding synchronized keyword to the getDataSource() method solved the problem.. could have sworn I tried that already.. must have gotten my wires crossed somewhere in between desperately switching from c3p0 to dbcp2 — Reece, May 31 '20 at 21:15

score 0 · Accepted Answer · answered Jun 01 '20 at 17:08

@user207421 was right, that the getDataSource() method should be synchronized & of course I'd already tried this; however this still didn't solve for my problem of thread "0" not letting the other threads take a turn.

I stripped back everything from my code, all other libraries etc.. until I got it to work, and then started building it back up again to find the breaking point. It seems that the main determining factor is the size of the ResultSet. I tried adding in extra thread.sleep time in various places however the only thing that has worked is breaking down the queries to request smaller ResultSets.

600k result sets, only 1 thread will run, 1k ResultSets and 4 threads will run. With ResultSets containing only 100 rows, all 11 threads will run. Note, I was testing this on a 16 CPU system with 8GB of memory allocated to the JVM (aws m5.4xlarge), so hardware resources shouldn't have been a contributing factor. So I guess I'll just have to break my code into smaller chunks.

When I was initially looking into this I was surprised as to the lack of a specific code samples for this specific problem (irrespective of ResultSet size & number of threads), so I'm just posting here what finally worked for me for the sake of a complete code sample:

    import java.sql.*;
    import org.apache.commons.dbcp2.BasicDataSource;    

    public class SandboxApp {

        private static BasicDataSource dataSource;

        public static synchronized BasicDataSource getDataSource() {

            if (dataSource == null) {

                BasicDataSource ds = new BasicDataSource();

                ds.setUrl("jdbc:mysql://localhost:3306/my-db");
                ds.setUsername("root");
                ds.setPassword("");

                ds.setDriverClassName("org.mariadb.jdbc.Driver");

                ds.setInitialSize(3);
                ds.setMaxTotal(25);
                ds.setMinIdle(0);
                ds.setMaxIdle(8);
                ds.setMaxOpenPreparedStatements(100);

                dataSource = ds;
            }
            return dataSource;
        }

        public static void main(String [] args) throws Exception{   
           for(int i=0; i<11; i++){//spawn 11 threads & get each thread to process 100 sql rows at the same time
                  new Thread("" + (i*100)){

                    public void run(){
                        System.out.println("Thread: " + getName() + " running");

                        Connection con = null;
                        PreparedStatement pstmt = null;
                        ResultSet rs = null;
                    try {           
                        con = SandboxApp.getDataSource().getConnection();

                        pstmt = con.prepareStatement("select something from some_table limit "+getName()+",100");
                        rs=pstmt.executeQuery();
                        while(rs.next()){  
                            System.out.println("Doing stuff for thread "+getName());//With smaller ResultSet, this works fine for all 11 threads
                                //give the other threads a turn...
                                try {
                                    Thread.sleep(10);
                                }
                                catch(InterruptedException ex) {
                                }
                        }

                      } catch (Exception e) {
                        e.printStackTrace();
                      }finally{
                          try {pstmt.close();} catch (SQLException e) {}
                          try { con.close(); } catch(Exception e) {}
                          try { rs.close(); } catch(Exception e) {}
                      }
                    }
                  }.start();
            }
        }
    }

java dbcp2 multithreaded connection access

1 Answers1