Discussions

Performance and scalability: DB connection hang up

  1. DB connection hang up (7 messages)

    Hi all
    We have a web application that reads some data connecting to db. Sometimes the connection hangs up, so we have to restart Tomcat, to re-create new connection.
    Our environment is:
    -Tomcat 4.1.27 on Sun OS 5.9, with jdk 1.4.0
    -jdbc driver ojdbc14.jar (version Oracle JDBC Driver version - 9.0.2.0.0)
    -Oracle server 8.1.7.4.0, on Sun OS 5.9

    -Connection pools are created via JNDI, so inside server.xml:
    <Resource name="jdbc/glprod" auth="Container"
    type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/glprod">
    <parameter>
    <name>factory</name>
    <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
    <name>driverClassName</name>
    <value>oracle.jdbc.driver.OracleDriver</value>

    -Other parameter are user, password, maxActive, maxIdle, maxWait. Are there any other useful parameter?
    -Using toad (select * from v$session) I notice that db connection are missing (when we have this error)
    -SQL statement are ONLY select, no complicated insert or else ...
    -When connection hangs up, we have inside log file this line:
    java.sql.SQLException: Eccezione IO: Socket closed
    -On Oracle server I haven't find any error log or alert

    Does anyone can help me?
    Thanks
    Aldo Funicelli

    Threaded Messages (7)

  2. DB connection hang up[ Go to top ]

    can you tell me what is/are the SQL that you running while this connection is open.
    Have you tried with any other jdbc driver?
  3. DB connection hang up[ Go to top ]

    1. In your code check, if you close the connection after you executed SQL.
    2. Check if you can start tomcat to debug jdbc or datasource. For example, in OC4J, you can start the container like this to debug jdbc: java -Djdbc.debug=true -Ddatasource.verbose=true -jar oc4j.jar

    Hope this helps,
    Senthil.
  4. DB connection hang up[ Go to top ]

    Thanks for the answer
    1) I use ibatis SQLMaps as framework to connect to DB. This library is responsible to release all connection.
    2) I use oracle jdbc thin client ojdbc4.jar (Driver version - 9.0.2.0.0)
    The error is present only on our Unix server. The test machine is Windows 2000 (with Tomcat 4.1.27), and db connection to the same Oracle server remais always active.

    I set the log level (of log4j) to debug.
    I hope to find something new ...
    Thanks
  5. DB connection hang up[ Go to top ]

    We have a firewall between Tomcat and Oracle server.
    The exception Socket closed may be caused by this?

    Thanks
  6. We have a firewall between Tomcat and Oracle server.The exception Socket closed may be caused by this?Thanks
    You may try to run a small UNIX shell script to check the connections to Oracle from the machine where Tomcat runs. For example, this might work:

    while true ;
    date
    netstat -an | grep 1522 # or the port where Oracle listens
    sleep 60
    done

    If you run this script on the UNIX machine, you are going to know if the connections get phisycally closed (at TCP/IP level). Maybe, you can find a pattern (for example: all the sockets gets closed 10 minutes after they are openend) and see if there are problems with the time-out policy of tomcat or Oracle.

    Jose Ramon Huerga
    http://www.terra.es/personal/jrhuerga/
  7. DB connection hang up[ Go to top ]

    Firewall can be the cause. I have seen one such situation earlier...

    The appserver used to keep sending the keepalive message to Oracle, but the firewall sitting between them was dropping that message. Now, Oracle since it did not recieve that message, was closing the connections without notifying the appserver. Due to this, the appserver was assuming the connections are alive, whereas they were not thus resultin in a hung application. It was corrected by changing some setting in the firewall (related to keepalive, but I am not aware of the details of the changes made).
  8. DB connection hang up[ Go to top ]

    Aldo, I have a very similar situation where all our connections to Oracle on a linux box go dead after 2 - 4 hours. This happens with connections to Tomcat and another standalone Java app.

    Have you found a solution to this problem?

    I appreciate any help.