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 Re: dbcp is mixing up connections
Date Thu, 15 Dec 2011 12:12:17 GMT
Thanks for your answers.

I've found the solution Myself.

1) You have 2 different blocks in a jsp file:

     The one into <%! %> tags is about definition
     The one into <% %> tags is about coding

2) A jsp file (script.jsp) is transfomed by tomcat into a single class 
file into the work directory including the source code of the sceipt 
(with all the includes)

3) 'definition block' goes into the definition part of the class in the 
generated class and 'code block' goes into one method of the class:

      public void _jspService(final 
javax.servlet.http.HttpServletRequest request, final 
javax.servlet.http.HttpServletResponse response)

4) I assume that the Tomcat Container, instances the class depending of 
the retrieved requests. This is WHY Tomcat is a Container.

5) Tomcat, creates ONE (or maybe SOME) Class object and call to the 
_jspService on every script request

What happens if you handle Pool Coonections with a 'java.sql.Connection 
conn' variable declared into the definitions block "<%! %>"?

Happens than if you are donig multitheading and executing the same 
sctipt in parallel you will mix up connections because evey thead is 
executing the same method in parallel and putting a different connection 
into the java.sql.Connection conn class variable.

I don't know if this is a tomcat bug.




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).


El 14/12/2011 17:21, Aitor Garcia | Tempel.es escribió:
> 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