java dbcp2多线程连接访问

huangapple
go评论97阅读模式
英文:

java dbcp2 multithreaded connection access

问题

我对在Tomcat上使用连接池非常熟悉，并且多年来一直在使用它，没有任何问题。然而，目前我正在开发一个需要同时运行多个线程以提高性能的主方法应用程序，而这些线程都需要访问同一个数据库。我已经将我的代码调整得可以在剥离数据库代码的情况下工作，并且仅为测试目的使用数组（例如，多线程可以正常工作）。然而，一旦我重新添加数据库连接，第一个线程就会锁定，其他线程根本不会运行。我已经尝试过c3p0和dbcp2；目前正在使用dbcp2。谢谢！有大量的文档可供参考，但很少有代码示例似乎适用于我的用例。以下是一个示例应用程序：

  1. import java.sql.*;
  2. import org.apache.commons.dbcp2.BasicDataSource;
  4. public class SandboxApp {
  5.     
  6.     private static BasicDataSource dataSource;
  8.     public static BasicDataSource getDataSource() {
  10.         if (dataSource == null) {
  12.             BasicDataSource ds = new BasicDataSource();
  14.             ds.setUrl("jdbc:mysql://localhost:3306/my-db");
  15.             ds.setUsername("root");
  16.             ds.setPassword("");
  18.             ds.setDriverClassName("org.mariadb.jdbc.Driver");
  20.             ds.setInitialSize(3);
  21.             ds.setMaxTotal(25);
  22.             ds.setMinIdle(0);
  23.             ds.setMaxIdle(8);
  24.             ds.setMaxOpenPreparedStatements(100);
  26.             dataSource = ds;
  27.         }
  28.         return dataSource;
  29.     }
  30.     
  31.     public static void main(String [] args) throws Exception{    
  32.        for(int i=0; i<11; i++){//spawn 11 threads &amp; get each thread to process 600k sql rows at the same time
  33.               new Thread("" + (i*600000)){
  34.                   
  35.                 public void run(){
  36.                     System.out.println("Thread: " + getName() + " running");//prints correctly for all threads
  37.                   
  38.                     Connection con = null;
  39.                     PreparedStatement pstmt = null;
  40.                     ResultSet rs = null;
  41.                 try {           
  42.                     con = SandboxApp.getDataSource().getConnection();
  43.                     
  44.                     pstmt = con.prepareStatement("select something from some_table limit "+getName()+",600000");
  45.                     rs=pstmt.executeQuery();
  46.                     while(rs.next()){  
  47.                         System.out.println("Doing stuff for thread "+getName());//this only prints for getName() == 0
  48.                             //give the other threads a turn...
  49.                             try {
  50.                                 Thread.sleep(10);
  51.                             }
  52.                             catch(InterruptedException ex) {
  53.                             }
  54.                     }
  56.                   } catch (Exception e) {
  57.                     e.printStackTrace();
  58.                   }finally{
  59.                       try {pstmt.close();} catch (SQLException e) {}
  60.                       try { con.close(); } catch(Exception e) {}
  61.                       try { rs.close(); } catch(Exception e) {}
  62.                   }
  63.                 }
  64.               }.start();
  65.         }
  66.     }
  67. }

请注意，我已经省略了引入包和类的部分。如果您有进一步的问题，请随时问我。

英文:

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:

  1. import java.sql.*;
  2. import org.apache.commons.dbcp2.ConnectionFactory;
  3. import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
  4. import org.apache.commons.dbcp2.PoolableConnection;
  5. import org.apache.commons.dbcp2.PoolableConnectionFactory;
  6. import org.apache.commons.dbcp2.PoolingDataSource;
  7. import org.apache.commons.dbcp2.PoolingDriver;
  8. import org.apache.commons.dbcp2.Utils;
  9. import org.apache.commons.pool2.ObjectPool;
  10. import org.apache.commons.pool2.impl.GenericObjectPool;
  11. public class SandboxApp {
  12. private static BasicDataSource dataSource;
  13. public static BasicDataSource getDataSource() {
  14. if (dataSource == null) {
  15. BasicDataSource ds = new BasicDataSource();
  16. ds.setUrl(&quot;jdbc:mysql://localhost:3306/my-db&quot;);
  17. ds.setUsername(&quot;root&quot;);
  18. ds.setPassword(&quot;&quot;);
  19. ds.setDriverClassName(&quot;org.mariadb.jdbc.Driver&quot;);
  20. ds.setInitialSize(3);
  21. ds.setMaxTotal(25);
  22. ds.setMinIdle(0);
  23. ds.setMaxIdle(8);
  24. ds.setMaxOpenPreparedStatements(100);
  25. dataSource = ds;
  26. }
  27. return dataSource;
  28. }
  29. public static void main(String [] args) throws Exception{	
  30. for(int i=0; i&lt;11; i++){//spawn 11 threads &amp; get each thread to process 600k sql rows at the same time
  31. new Thread(&quot;&quot; + (i*600000)){
  32. public void run(){
  33. System.out.println(&quot;Thread: &quot; + getName() + &quot; running&quot;);//prints correctly for all threads
  34. Connection con = null;
  35. PreparedStatement pstmt = null;
  36. ResultSet rs = null;
  37. try {			
  38. con = SandboxApp.getDataSource().getConnection();
  39. pstmt = con.prepareStatement(&quot;select something from some_table limit &quot;+getName()+&quot;,600000&quot;);
  40. rs=pstmt.executeQuery();
  41. while(rs.next()){  
  42. System.out.println(&quot;Doing stuff for thread &quot;+getName());//this only prints for getName() == 0
  43. //give the other threads a turn...
  44. try {
  45. Thread.sleep(10);
  46. }
  47. catch(InterruptedException ex) {
  48. }
  49. }
  50. } catch (Exception e) {
  51. e.printStackTrace();
  52. }finally{
  53. try {pstmt.close();} catch (SQLException e) {}
  54. try { con.close(); } catch(Exception e) {}
  55. try { rs.close(); } catch(Exception e) {}
  56. }
  57. }
  58. }.start();
  59. }
  60. }
  61. }

答案1

得分: 0

@user207421是对的，getDataSource()方法应该是同步的，当然我之前已经尝试过这个；然而这仍然没有解决线程"0"不让其他线程轮流运行的问题。

我从代码中剥离了所有其他库等内容，直到它能够工作，然后开始逐步构建它，找出破坏点。似乎主要的决定性因素是ResultSet的大小。我尝试在各个地方添加额外的线程休眠时间，然而唯一有效的方法是将查询拆分为请求较小的ResultSets。

有600k个结果集，只有1个线程会运行，有1k个结果集，4个线程会运行。只有包含100行的ResultSets，所有11个线程都会运行。请注意，我在一个16个CPU系统上进行测试，JVM分配了8GB的内存（aws m5.4xlarge），因此硬件资源不应该是一个 contributing factor。所以我想我只能将代码分成较小的块。

当我最初研究这个问题时，我对缺乏针对这个特定问题的特定代码示例感到惊讶（与ResultSet大小和线程数量无关），因此我只是在这里发布了最终对我有效的代码示例，以供参考：

  1. import java.sql.*;
  2. import org.apache.commons.dbcp2.BasicDataSource;
  4. public class SandboxApp {
  6.     private static BasicDataSource dataSource;
  8.     public static synchronized BasicDataSource getDataSource() {
  10.         if (dataSource == null) {
  12.             BasicDataSource ds = new BasicDataSource();
  14.             ds.setUrl("jdbc:mysql://localhost:3306/my-db");
  15.             ds.setUsername("root");
  16.             ds.setPassword("");
  18.             ds.setDriverClassName("org.mariadb.jdbc.Driver");
  20.             ds.setInitialSize(3);
  21.             ds.setMaxTotal(25);
  22.             ds.setMinIdle(0);
  23.             ds.setMaxIdle(8);
  24.             ds.setMaxOpenPreparedStatements(100);
  26.             dataSource = ds;
  27.         }
  28.         return dataSource;
  29.     }
  31.     public static void main(String [] args) throws Exception{   
  32.         for(int i=0; i<11; i++){//spawn 11 threads &amp; get each thread to process 100 sql rows at the same time
  33.             new Thread("" + (i*100)){
  35.                 public void run(){
  36.                     System.out.println("Thread: " + getName() + " running");
  38.                     Connection con = null;
  39.                     PreparedStatement pstmt = null;
  40.                     ResultSet rs = null;
  41.                     try {           
  42.                         con = SandboxApp.getDataSource().getConnection();
  44.                         pstmt = con.prepareStatement("select something from some_table limit "+getName()+",100");
  45.                         rs=pstmt.executeQuery();
  46.                         while(rs.next()){  
  47.                             System.out.println("Doing stuff for thread "+getName());//With smaller ResultSet, this works fine for all 11 threads
  48.                             //give the other threads a turn...
  49.                             try {
  50.                                 Thread.sleep(10);
  51.                             }
  52.                             catch(InterruptedException ex) {
  53.                             }
  54.                         }
  56.                     } catch (Exception e) {
  57.                         e.printStackTrace();
  58.                     }finally{
  59.                         try {pstmt.close();} catch (SQLException e) {}
  60.                         try { con.close(); } catch(Exception e) {}
  61.                         try { rs.close(); } catch(Exception e) {}
  62.                     }
  63.                 }
  64.             }.start();
  65.         }
  66.     }
  67. }
英文:

@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:

  1.     import java.sql.*;
  2. import org.apache.commons.dbcp2.BasicDataSource;    
  3. public class SandboxApp {
  4. private static BasicDataSource dataSource;
  5. public static synchronized BasicDataSource getDataSource() {
  6. if (dataSource == null) {
  7. BasicDataSource ds = new BasicDataSource();
  8. ds.setUrl(&quot;jdbc:mysql://localhost:3306/my-db&quot;);
  9. ds.setUsername(&quot;root&quot;);
  10. ds.setPassword(&quot;&quot;);
  11. ds.setDriverClassName(&quot;org.mariadb.jdbc.Driver&quot;);
  12. ds.setInitialSize(3);
  13. ds.setMaxTotal(25);
  14. ds.setMinIdle(0);
  15. ds.setMaxIdle(8);
  16. ds.setMaxOpenPreparedStatements(100);
  17. dataSource = ds;
  18. }
  19. return dataSource;
  20. }
  21. public static void main(String [] args) throws Exception{   
  22. for(int i=0; i&lt;11; i++){//spawn 11 threads &amp; get each thread to process 100 sql rows at the same time
  23. new Thread(&quot;&quot; + (i*100)){
  24. public void run(){
  25. System.out.println(&quot;Thread: &quot; + getName() + &quot; running&quot;);
  26. Connection con = null;
  27. PreparedStatement pstmt = null;
  28. ResultSet rs = null;
  29. try {           
  30. con = SandboxApp.getDataSource().getConnection();
  31. pstmt = con.prepareStatement(&quot;select something from some_table limit &quot;+getName()+&quot;,100&quot;);
  32. rs=pstmt.executeQuery();
  33. while(rs.next()){  
  34. System.out.println(&quot;Doing stuff for thread &quot;+getName());//With smaller ResultSet, this works fine for all 11 threads
  35. //give the other threads a turn...
  36. try {
  37. Thread.sleep(10);
  38. }
  39. catch(InterruptedException ex) {
  40. }
  41. }
  42. } catch (Exception e) {
  43. e.printStackTrace();
  44. }finally{
  45. try {pstmt.close();} catch (SQLException e) {}
  46. try { con.close(); } catch(Exception e) {}
  47. try { rs.close(); } catch(Exception e) {}
  48. }
  49. }
  50. }.start();
  51. }
  52. }
  53. }

huangapple
  • 本文由 发表于 2020年5月30日 05:24:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/62094801.html
  • dbcp
  • java
  • multithreading
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定