12 Replies Latest reply: Jul 17, 2007 8:08 AM by 588492 RSS

    executeUpdate hangs

    392052
      I have a very hard time figuring out why the preparedStatement.executeUpdate() method hangs. My application is a web based application, and during a stress test I keep loosing some HTTP requests that never returns.

      After a lot of debugging, I found that the executeUpdate method never returns, every 1 out of 500 times, approximately.

      How can I find out why this is happening?

      Platforms and Versions:

      Windows XP Pro
      Java 1.4
      Personal Oracle9i Release 9.2.0.1.0
      JDBC driver version: 9.2.0.1.0
        • 1. Re: executeUpdate hangs
          Avi Abrami
          Hi Torgeir,
          This is just a guess, but if your "executeUpdate()" method is trying to access something in the database that is currently locked (by something else), then it may be waiting (indefinitely) until that locked resource is released.

          Of-course, since I didn't see any details in your post about what your "executeUpdate()" method is trying to do, I can't offer any more detailed help (than my 'guess', above).

          Hope this helps you, anyway.

          Good Luck,
          Avi.
          • 2. Re: executeUpdate hangs
            392052
            There is not a whole lot of details I can give you, because this happens to every executeUpdate() method in the application.

            But for the most part, the SQL statements are INSERTS, and the application is doing a lot of it.

            I was basically trying to find a way to find out how I can debug this.
            • 3. Re: executeUpdate hangs
              3330
              Hello Torgeir,
              Some of the things I would try first for debugging....

              1) Code a simple Java class where I can see this error. Most probably, if there is a basic problem, it will be rectified during this step. Try to run the query from sqlplus too.
              2) Try if I get this behaviour when firing Update/Delete queries also.
              3) Try using executeBatch() method to insert rows. Maybe, this works.

              Lets hope, the error/bug can be identified while following these steps.

              Hope this helps.
              • 4. Re: executeUpdate hangs
                392052
                Thank you for the hints.

                1) I did this, and the same thing happens. It still hangs after a number of executeUpdate() calls. The strange thing is, that other connections/sessions are still able to do the calls. So the database itself does not hang, it is just some connections to the database that hangs (those that have executed the db inserts a few hundred times).

                2) Mostly inserts that is causing this.

                3) I tried this a while ago, and I got the same behaviour.
                • 5. Re: executeUpdate hangs
                  3330
                  Hello,
                  Possibly, there is some problem during one of the inserts. Maybe there is some problem with the Connection pool, if you are using one. After each insert/update/delete, are you closing the PreparedStatement object and Connection object and returning it to the Connection Pool?
                  • 6. Re: executeUpdate hangs
                    392052
                    Yes, I am closing the record-sets. This was actually the first thing I checked, and is now bug-fixed and bug free.

                    There is nothing wrong with the inserts or closing the resultsets, statements or connections. We have tested the exact same code with the same connection pool on other databases, such as HSQLDB and SQL server. Both are running the same code at high loads without problems.

                    I suspect that there is a problem with the configuration of Oracle or the driver itself.
                    • 7. Re: executeUpdate hangs
                      392279
                      I'm seeing something similar with the 9i JDBC driver and an 8i database.

                      I have a stored proc that takes a long time to run that never returns. The session to the database is dropped, but the thread in my app is waiting (see stack trace below).

                      "Dispatch Thread-2 : bg" prio=5 tid=0x818110 nid=0x12 runnable [c1e80000..c1e8199c]
                      at java.net.SocketInputStream.socketRead0(Native Method)
                      at java.net.SocketInputStream.read(SocketInputStream.java:129)
                      at oracle.net.ns.Packet.receive(Unknown Source)
                      at oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
                      at oracle.net.ns.NetInputStream.read(Unknown Source)
                      at oracle.net.ns.NetInputStream.read(Unknown Source)
                      at oracle.net.ns.NetInputStream.read(Unknown Source)
                      at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:931)
                      at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
                      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369)
                      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
                      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
                      - locked <d4dd6af0> (a oracle.jdbc.ttc7.TTC7Protocol)
                      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
                      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
                      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
                      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
                      - locked <d4dcd6d0> (a oracle.jdbc.driver.OracleCallableStatement)
                      - locked <d4dcdfa8> (a oracle.jdbc.driver.OracleConnection)
                      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
                      - locked <d4dcd6d0> (a oracle.jdbc.driver.OracleCallableStatement)
                      - locked <d4dcdfa8> (a oracle.jdbc.driver.OracleConnection)
                      at com.niku.union.persistence.PersistenceController.processSql(Unknown Source)
                      • 8. Re: executeUpdate hangs
                        392052
                        These problems might be related.

                        Does anybody know how to debug the driver or the database, to find out why the executeUpdate() method never returns?
                        • 9. Re: executeUpdate hangs
                          559302
                          Hi,
                          what was the problem? How did you solve it. I am having same problem and wondering if I can get some help from you?
                          • 10. Re: executeUpdate hangs
                            568103
                            I got the same problem too. Anyone has idea.

                            executeUpdate() hangs every times. It run good few days ago. After it hangs once, it hangs all the time. The statement is a Update SQL statement. If I run the same SQL in the same database from SQL Plus, it runs ok.
                            • 11. Re: executeUpdate hangs
                              566929
                              I'm having the same problem with inserts. I'm not even using a pool, just getting a connection, then a new connection.
                              • 12. Re: executeUpdate hangs
                                588492
                                In my case, I solved it by disconnecting any Oracle SQL Plus / SQL Cmd line processes that were running. Seems that they open the DB for "exclusive" use for some sort. Hope this helps you too...