cx_Oracle python delete statements in multiprocess environment

Question

I am using python cx_Oracle and multiprocessing. I am spinning 8 processes to delete data from chain of tables. Delete statements in all 8 processes are same. But the data they are deleting is mutually exclusive. Each process creates its own db connection runs and commits the delete statements. With this setup currently I am getting below error

ORA-00060: deadlock detected while waiting for resource

Is there any way to avoid this error?

Answer 1

Without providing additional information about the code you are using to perform the deletes and the table definition I can only provide general advice:

• look at the indexes set up on the table; if you are deleting rows without the use of an index you are going to be requiring a lock on the entire table instead of just on the rows being deleted
• look at the foreign keys referencing the table; by deleting these rows locks may be acquired on the child tables as well; if there are no indexes on those columns you may be locking the entire table
• examine the dba_lock table to see what locks are in fact being held
• examine your code; a deadlock can only occur if process A has acquired some locks, process B has acquired some other locks and then wants the locks acquired by process A and process A then wants to acquire the locks held by process B; it doesn't have to be only two processes as well; an entire chain of locks held by multiple processes can be the culprit