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