Can a deadlock for a cursor for update be avoided?

Question

We have a procedure for setting a unique number to a contract appendice. If a user selects multiple appendices to assign a number to, this procedure is simultanously called for each one of them.

It works when called for a single appendice, but when called in parallel for multiple appendices, it fails with ORA-00060: deadlock detected while waiting for resource

Can this be solved somehow?

declare
  cursor c_data is
  select dcd.document_id
         d.regnumbervalue
  from D_CONTRACT_DATA dcd
  inner join DOCUMENTS d on dcd.document_id = d.id
  where dcd.contract_id = pi_contract_id
  for update;
begin
  
  -- log here: all calls reach here
  
  for r_document in c_data loop
    if r_document.document_id = pi_document_id then
        
      po_numberVal := case 
                        when r_document... > 0 
                          then nvl(r_document..., 0) + 1
                        when pi_pref_regnumval is not null 
                          then pi_pref_regnumval
                        else nvl(r_document...., 0) + 1
                      end;
             
      update D_CONTRACT_DATA dcd
      set dcd.regnumbervalue = po_numberVal
      where dcd.document_id = pi_document_id;
        
      exit;
    end if;
  end loop;
  
  -- log here: only one or two calls reach here
end;

Answer 1

Ensure that all your programs that lock rows do so in the same order. For example,

cursor c_data is
select dcd.document_id
       d.regnumbervalue
from D_CONTRACT_DATA dcd
inner join DOCUMENTS d on dcd.document_id = d.id
where dcd.contract_id = pi_contract_id
-- IMPORANT: everyone must access rows in order of document_id to avoid deadlocks!
order by dcd.document_id
for update;

If all code does this, then it is impossible for two concurrent transactions to have each locked a document_id that the other is requesting -- hence no deadlock.

If you have too much application code to fix like this, look at the trace file generated by your ORA-00060. It will indicate the processes that were involved in the deadlock and you can just start by fixing those processes.

Also, make sure there are no bitmap indexes on your table. Bitmap indexes can cause deadlocks and there is not a darn thing your application code can do to avoid it. (At best, your code can catch an exception mapped to ORA-00060 and restart the transaction). You should NOT be using bitmap indexes on a table that is being updated by multiple sessions concurrently.

If you are still having deadlock issues, I'd recommend you modify your question to include a trace file. There are other possible sources of deadlocks that are not related to your application code. E.g., transactions can deadlock trying to allocate ITL entries in database blocks. A trace would show that.

But given that your CURSOR was not ordered, I suspect your application code is to blame. Start with consistent ORDER BYs across your application code.