Any reason why this sql might cause problems?

update tableA
set tableA.column1 = 'someValue'
where tableA.column2 in 
( select column3 from tableB where tableB.column4 in 
(
'valueA',
'valueB',
-- about 50 more records go here
)
)

When I run this, the database seems to hang. I am pretty new to sql, so I want to rule out (...or more likely rule in) the possibility that the problem is with my statement. I开发者_如何学C am running this on an oracle database using sqlDeveloper.

While this update is running (or "hanging"), open up another session and issue:

select event
  from v$session
 where sid = [sid_from_session_running_update]

to see what it is doing. You can find the sid by issuing this query (before the update "hangs", of course):

select distinct sid from v$mystat

Only if you see something like this:

SQL> select event
  2    from v$session
  3   where sid = 148
  4  /

EVENT
----------------------------------------------------------------
enq: TX - row lock contention

1 row selected.

, is it a case of a row lock. You can use some other v$ views as well to determine what further what is happening. For example, you can check out v$sess_io repeatedly to see if any progress is being made. Or v$session_longops or v$sql_plan_statistics might give you clues as to what is happening. Look and you shall find :-)

Hope this helps.

Regards,
Rob.

If the DB is appearing to "hang" it may well be that there are modifications to the data in another, uncommited session.

Try...

SELECT *
FROM   tableA
WHERE  tableA.column2 IN ( select .... )
FOR UPDATE NOWAIT;

and see if you get an:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Make sure you issue a ROLLBACK after that test.

If you do get the error, it indicates that another session has a lock on the data.

Looks OK to me, to see how many updates you are about to perform do:

SELECT COUNT(1) FROM tableA WHERE tableA.column2 IN ( select .... )

(where ... is from you code snippet above). If you get very many hits, then that might be the problem.