0

I have a stored procedure in oracle to delete rows from some 8-10 tables. I have to delete a row from say table "Person". It has lot of relations like "Person_Health", "Person_Claims", "Person_Bills" etc. Again, the child tables has relations with other tables.

I have written a stored procedure, and looped through the children tables to delete the child records, and then finally deleted the record in parent table. I'm not using any kind of lock/wait mechanisms (I'm a beginner, not sure about which one to use)

Sometimes, this procedure is failing. Translating SQLException with SQL state '61000', error code '60', message [ORA-00060: deadlock detected while waiting for resource

Problem is, it is failing frequently because of the (select/insert/update)requests to access these tables from different parts of application while this stored procedure is executing.

Is there a way to lock the tables individually while deleting? Or is there any other better way to do this.

Note: one way might be to put "ON DELETE CASCADE", for these tables, which will automatically delete the child records, but that is not possible.

sanjay Kumar
  • 140
  • 1
  • 14
  • Read about this [lock_table](https://www.techonthenet.com/oracle/transactions/lock_table.php) , anyway in your procedure , before update you can check **V$Locked_Object** and decide to update or not – Frank Jul 19 '17 at 15:13
  • What do you mean by "failing"? Are you trying to select for update with NOWAIT and getting "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired"? Or something else? If you were to just delete child1, delete child2, ... then there would be no error just a long wait while you waited for the other session to commit. – Tony Andrews Jul 19 '17 at 15:18
  • `it is failing frequently because of the (select/insert/update)requests to access these tables from different parts of application` - well, it you lock tables for a long time, then your application start failing, or at last it hangs.Usesr probably start to compalain.. – krokodilko Jul 19 '17 at 16:47
  • why is on delete cascade not possible? – tbone Jul 19 '17 at 17:07
  • @tbone because DBA said so – sanjay Kumar Jul 19 '17 at 17:47
  • @TonyAndrews Updated the Question. Deadlock exception. I'm not using any kind of lock/wait mechanisms as i'm a beginner and no idea about them. Please suggest one. – sanjay Kumar Jul 19 '17 at 17:55
  • Have your dba get you the trace files created on a deadlock. Locking an entire table is a very, very, very bad idea. – OldProgrammer Jul 19 '17 at 17:58
  • Are your constraints deferrable? They probably should be. Another approach is to do a soft delete (set a status flag in the parent as Inactive or similar). – tbone Jul 19 '17 at 18:29
  • @tbone sorry didn't get you. Can you please elaborate a bit – sanjay Kumar Jul 20 '17 at 02:16

0 Answers0