CODEWITHSUNDEEP
pythonpython-multiprocessingdeadlocksql-deletecx-oracle
Vrishank
Vrishank

Reputation: 364

cx_Oracle python delete statements in multiprocess environment

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?

Upvotes: 0

Views: 190

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

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

Upvotes: 2

Related Questions