2

My session is hanging when I execute this query in oracle 11g SQL*Plus:

SQL> 
 declare maxid number;

 begin

  delete from  measure_details
 where id in (select id from general_measures  where code in ('12345','12346'));

 delete from general_measures
 where code in (select code from general_measures  where code in ('12345','12346'));

 end;
 /
APC
  • 144,005
  • 19
  • 170
  • 281
jaiswal
  • 21
  • 2
  • By getting hang you mean the whole thing deadlock? If not, what is the error you are getting? – Anthony Accioly Aug 22 '11 at 14:42
  • 1
    How many records do you have in general_measures/measure_details tables? Do you have any references on/to it? Could you please provide an explain plan? – HamoriZ Aug 22 '11 at 14:43
  • yes Anthony its get Hang cursor is blinking , i thing its trying to execute and taking much time – jaiswal Aug 22 '11 at 14:51
  • hi Zoltan Hamori , on this table only 2 record are there that i tried to delete . and "general_measures" having pk which is in FK "measure_details" .. its executing good in "oracle SQL DEVELOPER" – jaiswal Aug 22 '11 at 14:53
  • 2
    in the 2nd delete, why bother with the sub-query 'select code where code in code' ? just 'delete where code in ('12345','123456')' – Kevin Burton Aug 22 '11 at 14:54
  • "it's executing good in SQL Developer" - as Tondy said, did you rollback (or commit) in SQL Developer before trying to execute this in SQL*Plus? It sounds like SQL Developer still has the rows locked. – Alex Poole Aug 23 '11 at 07:26

2 Answers2

2

If it "hangs" then that probably means that one or more of the rows you are trying to delete are locked by an uncommitted transaction in another session - perhaps even another session of your own?

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • hi i executed Commit; before executing that query still same – jaiswal Aug 22 '11 at 14:48
  • 1
    If my guess is correct, it would be another session in another window or tool or PC, not the same session. – Tony Andrews Aug 22 '11 at 14:51
  • You can try to issue a [SELECT FOR UPDATE statement](http://www.techonthenet.com/oracle/cursors/for_update.php) for testing purposes. And also change your DELETE statements to use [WHERE CURRENT OF](http://www.techonthenet.com/oracle/cursors/current_of.php). – Anthony Accioly Aug 22 '11 at 15:14
1

If you are facing deadlocks in this kind of DML, my first suspicion would be non indexed foreign keys. Take a look at this article from Ask Tom. If that is the case. All you need to do is create the appropriate indexes. But then again, maybe your tables are just huge and it is taking a while to complete.

Anthony Accioly
  • 21,918
  • 9
  • 70
  • 118
  • A blocking lock would cause the session to hang. A deadlock would cause the script to fail with a `ORA-00060: Deadlock detected.` error. – Allan Aug 22 '11 at 18:34
  • @Allan. You are right. I posted the answer before the OP clarified that it had no errors and that it runs fine within SQL Developer. Still, I feel like leaving the answer here for further reference (someone with a deadlock problem may see this page and index it's foreign key accordingly in the future). – Anthony Accioly Aug 22 '11 at 18:40