tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aitor Garcia | Tempel.es" <agar...@tempel.es>
Subject dbcp is mixing up connections
Date Wed, 14 Dec 2011 16:21:09 GMT
Hi,

After three days of research I've found notthing that helps me to solve 
this problem, it's why I'm trying to get help from the list.

I'm getting connections from dbcp pool in this way:

    /*********************************************************/
    /* Open a connection to the database                     */
    /*********************************************************/


    // AITOR 13/12/2011
    // Seems that connections stay active
    java.sql.Connection conn = null;

    // PreparedStatement prestmt = null;
    Statement stmt = null;
    ResultSet res = null;

    /*********************************************************/
    /* Open a connection to the database                     */
    /*********************************************************/

    public boolean web_dbConnect()  throws SQLException
    {

         try
         {
             javax.naming.Context initContext = new
    javax.naming.InitialContext();
              javax.naming.Context envContext  = (javax.naming.Context)
    initContext.lookup("java:/comp/env");
             javax.sql.DataSource datasource = (javax.sql.DataSource)
    envContext.lookup("jdbc/localpool");

             // get connection from datasource
             conn = datasource.getConnection();

             // wrap the connection with log4jdbc
             conn = new net.sf.log4jdbc.ConnectionSpy(conn);

             // now use Connection as normal (but it will be audited by
    log4jdbc)
             web_dbLog("Get DB Connection '" + conn + "'");

             /*
             DatabaseMetaData meta = conn.getMetaData();

             web_dbLog("Driver Name: " + meta.getDriverName());
             web_dbLog("Driver Version: " + meta.getDriverVersion());
             web_dbLog("Max Connections: " + meta.getMaxConnections());
             */

             /*
             ResultSet res = meta.getTables(null, null, "%", null);

             while (res.next())
             {
                 web_dbLog(res.getString(3));
             }
             */
         }
         catch( javax.naming.NamingException ne )
         {
             // throw new RuntimeException( "Unable to aquire data
    source", ne );
             web_dbLog("ERROR: Unable to aquire DB Connection from
    Datasource");

             return (false);


         }



         return (true);

    }


Closing connection here:


    /*********************************************************/
    /* Close the connection to the database                     */
    /*********************************************************/

    public void web_dbClose() throws SQLException
    {
         // Never close the database connection, because
         // it may interfere with other scripts which
         // share the same connection.
         web_dbLog("Return DB Conection to pool");




         if(res == null)
         {
             web_dbLog("Sorry, ResultSet in null");
         }
         else
         {
             res.close();
             res = null;

             web_dbLog("ResultSet closed!");
         }


         if(stmt == null)
         {
             web_dbLog("Sorry, Statement in null");
         }
         else
         {
             stmt.close();
             stmt = null;

             web_dbLog("Statement closed!");
         }

         if(conn == null)
         {
             web_dbLog("Sorry, Connection in null");
         }
         else
         {
             conn.close();
             conn = null;

             web_dbLog("Connection closed!");
         }
    }


All was working UNTIL AJAX comes. I have an AJAX script that launches 
different JSP scripts at the same time, and seems that comcat mix the 
connections:

The .jsp just

1) Opens connections
2) Make a SELECT statement
3) Make an UPDATE
2) Close a connection

See the code:


     String SQL = "";


    web_dbConnect();

     HashMap section = new HashMap();

     // Que valor tiene actualmente el menú
     SQL = "SELECT ... ";



     section = web_getElem(SQL);





     SQL = "UPDATE "...;



     web_dbUpdate(SQL);



     web_dbClose();



Code works well but connections mix, I have installed log4jdbc to see 
whan happens & the probler arise with the micure of connections into 
different threads:

    15 [http-bio-8080-exec-4] INFO jdbc.connection - 1. Connection
    opened 15 [http-bio-8080-exec-4] INFO jdbc.audit - 1. Connection.new
    Connection returned 16 [http-bio-8080-exec-6] INFO jdbc.connection -
    2. Connection opened 17 [http-bio-8080-exec-7] INFO jdbc.connection
    - 3. Connection opened 17 [http-bio-8080-exec-7] INFO jdbc.audit -
    3. Connection.new Connection returned 17 [http-bio-8080-exec-6] INFO
    jdbc.audit - 2. Connection.new Connection returned 17
    [http-bio-8080-exec-2] INFO jdbc.connection - 4. Connection opened
    17 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Connection.new
    Connection returned 22 [http-bio-8080-exec-7] INFO jdbc.audit - 4.
    Statement.new Statement returned 22 [http-bio-8080-exec-7] INFO
    jdbc.audit - 4. Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@3a0589 22 [http-bio-8080-exec-6] INFO
    jdbc.audit - 4. Statement.new Statement returned 22
    [http-bio-8080-exec-7] INFO jdbc.sqlonly - SELECT ... 22
    [http-bio-8080-exec-4] INFO jdbc.audit - 4. Statement.new Statement
    returned 22 [http-bio-8080-exec-4] INFO jdbc.audit - 4.
    Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@12a2259 23 [http-bio-8080-exec-4] INFO
    jdbc.sqlonly - SELECT... 22 [http-bio-8080-exec-6] INFO jdbc.audit -
    4. Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@14177f3 23 [http-bio-8080-exec-6] INFO
    jdbc.sqlonly - SELECT ... 36 [http-bio-8080-exec-7] INFO
    jdbc.sqltiming - SELECT.... {executed in 13 msec} 42
    [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.new
    ResultSet returned 42 [http-bio-8080-exec-7] INFO jdbc.audit - 4.
    Statement.executeQuery(SELECT ... ) returned
    net.sf.log4jdbc.ResultSetSpy@2a2ae9 42 [http-bio-8080-exec-7] INFO
    jdbc.resultset - 4. ResultSet.getRow() returned 0 42
    [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.last()
    returned true 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4.
    ResultSet.getRow() returned 0 42 [http-bio-8080-exec-7] INFO
    jdbc.resultset - 4. ResultSet.beforeFirst() returned 57
    [http-bio-8080-exec-2] INFO jdbc.audit - 4. Statement.new Statement
    returned 57 [http-bio-8080-exec-2] INFO jdbc.audit - 4.
    Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@1b56848 57 [http-bio-8080-exec-2] INFO
    jdbc.sqlonly - SELECT.... 60 [http-bio-8080-exec-4] INFO
    jdbc.sqltiming - SELECT... {executed in 37 msec} 60
    [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.new
    ResultSet returned 60 [http-bio-8080-exec-4] INFO jdbc.audit - 4.
    Statement.executeQuery(SELECT ... ) returned
    net.sf.log4jdbc.ResultSetSpy@1c26db4 60 [http-bio-8080-exec-4] INFO
    jdbc.resultset - 4. ResultSet.getRow() returned 0 60
    [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.last()
    returned true 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4.
    ResultSet.getRow() returned 0 60 [http-bio-8080-exec-4] INFO
    jdbc.resultset - 4. ResultSet.beforeFirst() returned 96
    [http-bio-8080-exec-6] INFO jdbc.sqltiming - SELECT ... {executed in
    73 msec} 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4.
    ResultSet.new ResultSet returned 96 [http-bio-8080-exec-6] INFO
    jdbc.audit - 4. Statement.executeQuery(SELECT ...) returned
    net.sf.log4jdbc.ResultSetSpy@1b0620c 96 [http-bio-8080-exec-6] INFO
    jdbc.resultset - 4. ResultSet.getRow() returned 0 96
    [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.last()
    returned true 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4.
    ResultSet.getRow() returned 0 96 [http-bio-8080-exec-6] INFO
    jdbc.resultset - 4. ResultSet.beforeFirst() returned 102
    [http-bio-8080-exec-4] INFO jdbc.audit - 4. PreparedStatement.new
    PreparedStatement returned 102 [http-bio-8080-exec-7] INFO
    jdbc.audit - 4. PreparedStatement.new PreparedStatement returned 102
    [http-bio-8080-exec-4] INFO jdbc.audit - 4.
    Connection.prepareStatement(UPDATE ...) returned
    net.sf.log4jdbc.PreparedStatementSpy@d4a1d3 102
    [http-bio-8080-exec-7] INFO jdbc.audit - 4.
    Connection.prepareStatement(UPDATE ...) returned
    net.sf.log4jdbc.PreparedStatementSpy@1cc3baa 103
    [http-bio-8080-exec-4] INFO jdbc.sqlonly - UPDATE ... 103
    [http-bio-8080-exec-7] INFO jdbc.sqlonly - UPDATE .... 133
    [http-bio-8080-exec-2] INFO jdbc.sqltiming - SELECT ... {executed in
    76 msec} 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4.
    ResultSet.new ResultSet returned 133 [http-bio-8080-exec-2] INFO
    jdbc.audit - 4. Statement.executeQuery(SELECT ... ) returned
    net.sf.log4jdbc.ResultSetSpy@1611aec 133 [http-bio-8080-exec-2] INFO
    jdbc.resultset - 4. ResultSet.getRow() returned 0 133
    [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.last()
    returned true 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4.
    ResultSet.getRow() returned 0 133 [http-bio-8080-exec-2] INFO
    jdbc.resultset - 4. ResultSet.beforeFirst() returned 134
    [http-bio-8080-exec-6] INFO jdbc.audit - 4. PreparedStatement.new
    PreparedStatement returned 134 [http-bio-8080-exec-6] INFO
    jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned
    net.sf.log4jdbc.PreparedStatementSpy@2e9c76 135
    [http-bio-8080-exec-6] INFO jdbc.sqlonly - UPDATE ... 197
    [http-bio-8080-exec-7] INFO jdbc.sqltiming - UPDATE ... {executed in
    93 msec} 197 [http-bio-8080-exec-7] INFO jdbc.audit - 4.
    PreparedStatement.executeUpdate() returned 1 225
    [http-bio-8080-exec-8] INFO jdbc.connection - 5. Connection opened
    225 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Connection.new
    Connection returned 227 [http-bio-8080-exec-8] INFO jdbc.audit - 5.
    Statement.new Statement returned 227 [http-bio-8080-exec-8] INFO
    jdbc.audit - 5. Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@11f2041 227 [http-bio-8080-exec-8] INFO
    jdbc.sqlonly - SELECT ... 249 [http-bio-8080-exec-4] INFO
    jdbc.sqltiming - UPDATE .... {executed in 146 msec} 249
    [http-bio-8080-exec-4] INFO jdbc.audit - 4.
    PreparedStatement.executeUpdate() returned 1 250
    [http-bio-8080-exec-8] INFO jdbc.sqltiming - SELECT ... {executed in
    23 msec} 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5.
    ResultSet.new ResultSet returned 250 [http-bio-8080-exec-8] INFO
    jdbc.audit - 5. Statement.executeQuery(SELECT ... ) returned
    net.sf.log4jdbc.ResultSetSpy@7e942f 250 [http-bio-8080-exec-8] INFO
    jdbc.resultset - 5. ResultSet.getRow() returned 0 250
    [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.last()
    returned true 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5.
    ResultSet.getRow() returned 0 250 [http-bio-8080-exec-8] INFO
    jdbc.resultset - 5. ResultSet.beforeFirst() returned 250
    [http-bio-8080-exec-7] INFO jdbc.audit - 4.
    PreparedStatement.close() returned 251 [http-bio-8080-exec-10] INFO
    jdbc.connection - 6. Connection opened 251 [http-bio-8080-exec-10]
    INFO jdbc.audit - 6. Connection.new Connection returned 252
    [http-bio-8080-exec-10] INFO jdbc.audit - 6. Statement.new Statement
    returned 252 [http-bio-8080-exec-10] INFO jdbc.audit - 6.
    Connection.createStatement(1004, 1007) returned
    net.sf.log4jdbc.StatementSpy@74ece8 252 [http-bio-8080-exec-10] INFO
    jdbc.sqlonly - SELECT... 261 [http-bio-8080-exec-10] INFO
    jdbc.sqltiming - SELECT ... {executed in 9 msec} 261
    [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.new
    ResultSet returned 261 [http-bio-8080-exec-10] INFO jdbc.audit - 6.
    Statement.executeQuery(SELECT ...) returned
    net.sf.log4jdbc.ResultSetSpy@165d118 261 [http-bio-8080-exec-10]
    INFO jdbc.resultset - 6. ResultSet.getRow() returned 0 261
    [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.last()
    returned true 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6.
    ResultSet.getRow() returned 0 261 [http-bio-8080-exec-10] INFO
    jdbc.resultset - 6. ResultSet.beforeFirst() returned 264
    [http-bio-8080-exec-10] INFO jdbc.audit - 6. PreparedStatement.new
    PreparedStatement returned 264 [http-bio-8080-exec-10] INFO
    jdbc.audit - 6. Connection.prepareStatement(UPDATE ...) returned
    net.sf.log4jdbc.PreparedStatementSpy@18297fe 264
    [http-bio-8080-exec-8] INFO jdbc.audit - 5. PreparedStatement.new
    PreparedStatement returned 264 [http-bio-8080-exec-8] INFO
    jdbc.audit - 5. Connection.prepareStatement(UPDATE....) returned
    net.sf.log4jdbc.PreparedStatementSpy@c8aeb3 269
    [http-bio-8080-exec-7] INFO jdbc.resultset - 5. ResultSet.close()
    returned 269 [http-bio-8080-exec-10] INFO jdbc.sqlonly - UPDATE ....
    278 [http-bio-8080-exec-8] INFO jdbc.sqlonly - UPDATE... 340
    [http-bio-8080-exec-6] INFO jdbc.sqltiming - UPDATE .. {executed in
    205 msec} 340 [http-bio-8080-exec-6] INFO jdbc.audit - 4.
    PreparedStatement.executeUpdate() returned 1 341
    [http-bio-8080-exec-2] INFO jdbc.audit - 4. PreparedStatement.new
    PreparedStatement returned 341 [http-bio-8080-exec-2] INFO
    jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned
    net.sf.log4jdbc.PreparedStatementSpy@5f634c 342
    [http-bio-8080-exec-6] INFO jdbc.audit - 4.
    PreparedStatement.close() returned 343 [http-bio-8080-exec-4] INFO
    jdbc.audit - 4. PreparedStatement.close() returned 344
    [http-bio-8080-exec-2] INFO jdbc.sqlonly - UPDATE ... 365
    [http-bio-8080-exec-2] INFO jdbc.sqltiming - UPDATE ... {executed in
    21 msec} 365 [http-bio-8080-exec-2] INFO jdbc.audit - 4.
    PreparedStatement.executeUpdate() returned 1 367
    [http-bio-8080-exec-2] INFO jdbc.audit - 4.
    PreparedStatement.close() returned 428 [http-bio-8080-exec-10] INFO
    jdbc.sqltiming - UPDATE ...' {executed in 159 msec} 428
    [http-bio-8080-exec-10] INFO jdbc.audit - 6.
    PreparedStatement.executeUpdate() returned 1 431
    [http-bio-8080-exec-7] INFO jdbc.audit - 6. Statement.close()
    returned 433 [http-bio-8080-exec-7] INFO jdbc.connection - 6.
    Connection closed 433 [http-bio-8080-exec-7] INFO jdbc.audit - 6.
    Connection.close() returned 436 [http-bio-8080-exec-10] INFO
    jdbc.audit - 6. PreparedStatement.close() returned 436
    [http-bio-8080-exec-8] INFO jdbc.sqltiming - UPDATE ... {executed in
    158 msec} 436 [http-bio-8080-exec-8] INFO jdbc.audit - 5.
    PreparedStatement.executeUpdate() returned 1 439
    [http-bio-8080-exec-6] INFO jdbc.audit - 6. Statement.close()
    returned 439 [http-bio-8080-exec-4] INFO jdbc.audit - 6.
    Statement.close() returned 439 [http-bio-8080-exec-2] INFO
    jdbc.audit - 6. Statement.close() returned 453
    [http-bio-8080-exec-8] INFO jdbc.audit - 5.
    PreparedStatement.close() returned 



I've tested this into : Centos / Windows Vista
Tomcat : 7.0 (diferent )
Java: Jre 1.6.0_16, 1.7.0 & 1.7.2

And happens the same.

Thnks. Any help would be appreciated!


-- 

FIRMA


Estemensaje se dirige exclusivamente a su destinatario. Contiene 
información *CONFIDENCIAL* sometida a secreto profesional o cuya 
divulgación está prohibida por Ley.
Si ha recibido este mensaje por error, debe saber que su lectura, copia 
y uso están prohibidos.
Le rogamos nos lo comunique inmediatamente por esta misma vía o por 
teléfono 93 600 36 00y proceda a su destrucción.
El correo electrónico vía Internet no permite asegurar la 
confidencialidad de los mensajes que se transmiten ni su integridad o 
correcta recepción.
*TEMPEL S.A.*no asume responsabilidad por estas circunstancias. Si el 
destinatario de este mensaje no consintiera la utilización del correo 
electrónico vía Internet y la grabación de los mensajes, rogamos lo 
ponga en nuestro conocimiento de forma inmediata.
En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de 
Protección de Datos de Carácter Personal le informamos de que sus datos 
personales y de empresa pasarán a formar parte de un fichero registrado 
ante la Agencia Española de Protección de Datos.
Los datos personales que existen en nuestro poder están protegidos por 
nuestra Política de Seguridad, y no serán compartidos con ninguna otra 
empresa.
Usted puede ejercitar sus derechos de acceso, rectificación, cancelación 
y oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet 
de LLobregat (Barcelona).


Mime
View raw message