executeUpdate() in java takes long time to execute

Question

I am using a simple piece of code to update a single row in Oracle DB, the query updates just a single row but still the execution hangs on stmt.executeUpdate().

It does not throw any exception, but the control just hangs there.

I am lost here as the same piece of code worked fine earlier.

  try {
      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(url,username,pwd);
      stmt = conn.createStatement();
      String sql = "update report set status =" +"'"+NO+"'"+ " where name=" +"'"+ name+"'"+ " and user_id=" +"'"+sub+"'"; 
      System.out.println(sql);          
      stmt.executeUpdate(sql)

Answer 1

You should never create statements by concatenating variables like this. This leaves you open to SQL injection. Furthermore, each statement will be seen by Oracle as a new statement which will need a hard parse, which can lead to performance problems. Instead, use preparedStatement.

However, I think that in your case it's not the cause of your problem. When a simple update hangs, it is almost always one of these reasons:

1. The row is locked by another transaction.
2. You are updating a key that is referenced by another table as an unindexed foreign key.
3. The table has an ON UPDATE trigger that does lots of works.

I'm pretty sure that you're in the first case. Oracle prevents multiple users from updating the same row at the same time, for consistency reasons mostly. This means that a DML statement will wait upon another transaction if the modified row is locked. It will wait until the blocking transaction commits or rollbacks.

I advise you to always lock a row before trying to update it by doing a SELECT ... FOR UPDATE NOWAIT before. This will fail if the row is already locked, and you can exit gracefully by telling the user that this row is currently being locked by another session.

You also have to make sure that no transaction ends in an uncommited state. All sessions should commit or rollback their changes before exiting (don't use autocommit though).

Answer 2

Use JDBC PreparedStatement java docs

  Class.forName("org.apache.derby.jdbc.ClientDriver");
  Connection con = DriverManager.getConnection
  ("jdbc:derby://localhost:1527/testDb","name","pass");
  PreparedStatement updateemp = con.prepareStatement
  ("insert into emp values(?,?,?)");
  updateemp.setInt(1,23);
  updateemp.setString(2,"Roshan");
  updateemp.setString(3, "CEO");
  updateemp.executeUpdate();