how to use JDBC ConnectionFactory for multiple connections in one servlet

[klimovsk]

Hello, JBoss Community!

I want to have multiple JDBC-connections (to postgreSQL) in my servlet. (my goal - work with db via multiple threads)

Now I use the following code to work with database (i do simple inserts or selects):

               InitialContext initialContext = new InitialContext();                              

               DataSource  datasource = (DataSource) initialContext.lookup("java:jdbc/postgresql");

               db = datasource.getConnection();

               ...

               PreparedStatement ps = db.prepareStatement(insQueue);

               ps.setString(...);

               ...

               ps.execute();

               ps.close();

When I start 4 instances of servlet (in parallel, each from different machine) I see, that full time, when all operations are completed is the same, as 4 x (time of operations for 1 servlet), i.e. operations form a queue, but I want to run them in parallel.

Please, help me understand mechanism and my error.

Sorry for my bad English

[peterj]

What is your maximum connection count? (Please post your *-ds.xml).

What monitoring have you done in PostgreSQL to determine what could be causing the serialization of requests? What monitoring have you done to your app to see if there is a bottleneck?

Exactly what SQL statements are being invoked? And how is the database defined?

If you max connections set higher than 1 (in other words, you allow multiple connections), and there are no locking issues (neither in yourcode nor in the SQL request), you should be able to runs dozens, or even hundreds, of SQL requests in parallel.

[klimovsk]

There is my current version of *-ds.xml: (lines marked as italic have been added on the advice of people on the network, but still did not help)

<local-tx-datasource>

    <jndi-name>jdbc/postgresql</jndi-name>

    <connection-url>jdbc:postgresql://192.168.51.189:5432/bedrock</connection-url>

    <driver-class>org.postgresql.Driver</driver-class>

    <user-name>postgres</user-name>

    <password>postgres</password>

    <min-pool-size>30</min-pool-size>

    <max-pool-size>50</max-pool-size>

    <prefill>true</prefill>

    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

    <prepared-statement-cache-size>100</prepared-statement-cache-size>

    <share-prepared-statements>true</share-prepared-statements>

     <metadata>

         <type-mapping>PostgreSQL 9.0</type-mapping>

      </metadata>

</local-tx-datasource>

SQL statement, which I invoke is "insert into employee values (?,?,?,?)"

On my linux db-server I execute command like "ps auxww | grep ^postgres | grep bedrock" and see, that there is 30 idle connections from ip, where  my JBoss is running (ok, connection pool is created). When I start execute SQL statemens (from 4 different hosts through my servlet in JBoss)  I see, that 29 connections are still in state 'idle' and 1 - in 'insert'. Ok, I think may be problem is in table locking, I create 3 new tables and try to execute statements from each host to different table -  situation did not change, all inserts are going through 1 connecion.

I try use in my code ConnectionPoolDataSource instead of simple DataSource and I get error:

java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrapperDataSource cannot be cast to javax.sql.ConnectionPoolDataSource

And one more problem - after I start use datasource.getConnection instead of DriverManager.getConnection I have the following error in my jboss log:

10:32:19,719 INFO  [org.jboss.resource.connectionmanager.CachedConnectionManager] Closing a connection for you.  Please close them yourself: org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6@7ad38cd3: java.lang.Throwable: STACKTRACE

    at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:278) [:6.0.0.Final]

    at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:524) [:6.0.0.Final]

    at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941) [:6.0.0.Final]

    at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89) [:6.0.0.Final]

    at com.test.JDBC_Servlet.jdbc_test.doGet(jdbc_test.java:117)

    at com.test.JDBC_Servlet.jdbc_test.doPost(jdbc_test.java:293)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [:1.0.0.Final]

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [:1.0.0.Final]

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:324) [:]

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242) [:]

    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [:]

    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) [:]

    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:181) [:6.0.0.Final]

    at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.event(CatalinaContext.java:285) [:1.1.0.Final]

    at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.invoke(CatalinaContext.java:261) [:1.1.0.Final]

    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:88) [:6.0.0.Final]

    at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:100) [:6.0.0.Final]

    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) [:]

    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [:]

    at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158) [:6.0.0.Final]

    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [:]

    at org.jboss.web.tomcat.service.request.ActiveRequestResponseCacheValve.invoke(ActiveRequestResponseCacheValve.java:53) [:6.0.0.Final]

    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362) [:]

    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [:]

    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:654) [:]

    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:951) [:]

    at java.lang.Thread.run(Thread.java:679) [:1.6.0_22]