statement.executeUpdate() hangs

[Rajesh MadhanGopal]

Hi,
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);
						  

[Paul Sturrock]

Sounds like something may be blocking your update. Have a look at DBA_BLOCKERS, see is that is it.

[Scott Selikoff]

Do you really need to make a compile-time reference to an Oracle JDBC class? Usually, something like "Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();" is more common since it reduces compile-time dependencies on drivers that won't be determined until runtime. Also, use PreparedStatements to set your variables.

[Rajesh MadhanGopal]

Thanks for the replies..

@Scott: I tried both the above mentioned steps before posting the question here and they were not of much help. I finally resolved the issue by indexing the 2 columns in my Where clause and now the update works like a charm.

I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!

[Vijitha Kumara]

Rajesh MadhanGopal wrote:I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

I don't think indexing made the difference there, since you have only around 100 records in it. Indexing makes the searching for records faster..

[Jeunne Seunne]

Rajesh MadhanGopal wrote:Thanks for the replies..

@Scott: I tried both the above mentioned steps before posting the question here and they were not of much help. I finally resolved the issue by indexing the 2 columns in my Where clause and now the update works like a charm.

I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!

Hi,

I am having the same problem. Can you post your solution? How did you resolved it? I've been working on that problem for weeks now trying to make it work but it just hangs up. Thanks for your help in adv.

[Fatih Keles]

There may be an other database session that has locked the row or table. You may check it with the below query. Index solution does not make any sense since the number of records in the table is very small in the former problem. Can you update the row with any sql editor?

select vs.sid Session_id, vp.spid Unix_id, vs.username Dbase, vs.osuser Win95_username, vs.program,
       do.owner locked_object_owner,do.object_name locked_object,
       do.object_type locked_object_type,do.status locked_object_status
       ,lo.locked_mode
       ,decode(lo.locked_mode,1,'null',2,'row share',3,'row exclusive',4,'share',
                5,'share row exclusive',6,'exclusive','**unknown**') locked_mode_desc
  from v$locked_object lo,dba_objects do,v$session vs, v$lock vl, v$process vp
 where lo.object_id  = do.object_id
   and lo.session_id = vs.sid
   and vs.sid        = vl.sid
   and vs.paddr      = vp.addr
   and do.object_name = '<YOUR TABLE NAME HERE>'

Regards,

Fatih.

[Paul Sturrock]

Rajesh MadhanGopal wrote:
I always thought that indexing would help in select queries and in complex joins and did not think that indexing would be needed for such a simple query. There is no question of too much load as there are less than 100 rows in the table and the update query will never update more than 1 row at a time.

Any thoughts are welcome !!

Indices will potentially slow down updates, not speed them up. Are you sure this change was not just coincidental?

[Jeanne Boyarsky]

Paul Sturrock wrote:Indices will potentially slow down updates, not speed them up.

Depends on the query of course. If you have a million rows in the table and need to find the one to update, an index could help.

Paul Sturrock wrote: Are you sure this change was not just coincidental?

That said, I think coincidence is more likely as 100 rows is too small for an index to matter much.

[Rajesh MadhanGopal]

Hi all,

I want to beleive this is just a coincidence as it beats any logic. I also want to mention that the update worked fine all through from an SQL editor while it was failing from the code, this also means that there is no blocking on the table.

I am in a situation where the issue is resolved and not completely sure how it was resolved

[Scott Selikoff]

It's also possible your database was corrupt. It does happen, more commonly with MySQL in my experience. As Jeanne said, an index can speed up UPDATE statements if you have a large enough record set but 100 isn't very big.

[Jeunne Seunne]

So if the resolution for this problem was just coincidental, it looks like i need to find my own solution then. So hopeless