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.