CODEWITHSUNDEEP
oracle-databaseplsqldatabase-deadlocks
Vidhan Jadhav
Vidhan Jadhav

Reputation: 21

Deadlock detected due to Pragma Autonomous Transaction Procedure

I have the below procedure which has pragma autonomous_transaction clause.Now this procedure is being called in Java code after validating the come business logic. After an execution of this proc, It starts with some java stuffs...

create or replace procedure UPDATE_INSTRUMENT 
is
  pragma autonomous_transaction;

begin

  begin
    update abc
       set AUTHSTATUS   = p_AUTHSTATUS,
           STATUS       = p_STATUS,
           USERID       = p_USERID,
           LASTUPDATED  = TO_DATE(p_LASTUPDATED, 'DD/MM/YYYY'),
           USERDATETIME = TO_DATE(p_USERDATETIME, 'DD/MM/YYYY')
    where  TRANSACNO = p_TRANSACNO;
    commit;
  end;

  begin
    update xyz
       set AUTHSTATUS = p_AUTHSTATUS,
           USERID      = p_USERID,
           AUTHDATE    = TO_DATE(SYSDATE, 'DD/MM/YYYY'),
           LASTUPDATED = TO_DATE(SYSDATE, 'DD/MM/YYYY'),
    where  TRANSACNO = p_TRANSACNO;
    commit;
  end;

end UPDATE_INSTRUMENT;

Table 'xyz' has three triggers and out of that 1 is on Insert and 2 are on Before update event.

P.N:- Table 'xyz' is not updated or locked anywhere before calling to this Procedure.

I am getting the below errors.

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "ADTTRG_xyz", line 277

ORA-04088: error during execution of trigger 'ADTTRG_xyz'

The table abc is getting updated properly, but it is failing to update table xyz.

Please explain why this deadlock is occurring.

Upvotes: 2

Views: 5509

Answers (1)

APC
APC

Reputation: 146249

"How deadlock is occurring."

Deadlocks occur when two sessions simultaneously attempt to change a common resource - such as a table or unique index - in such a way that neither session can commit without the other committing first. This is always an application design flaw, in that deadlocks are the result of complicated flow and poorly-implemented logic strategies.

There are a few clues that this is the case here.

ORA-06512: at "ADTTRG_xyz", line 277

For a start, a trigger with several hundred lines of code is a code smell. That's a lot of code to have in a trigger. It seems like there's a opportunity for competition there. Especially as...

Table 'xyz' has three triggers and ... 2 are on Before update event.

You have two BEFORE UPDATE triggers on the 'xyz' table and the event which generates the deadlock is an update of 'xyz'. This may not be a coincidence.

You must investigate these two triggers and establish which tables and indexes they need, so that you can spot whether they are in contention.

pragma autonomous_transaction;

The PL/SQL documentation says "If an autonomous transaction tries to access a resource held by the main transaction, a deadlock can occur." Autonomous transactions are another code smell. There are very few valid use cases for autonomous transactions; more often they are used to wrangle a bad data model into submission.

So you have a lot of things to investigate. Oracle offers diagnostics to help with this.

When deadlocks happen Oracle produces a tracefile. This file will be written to an OS directory. If you don't know where that is you can query the V$DIAG_INFO view. Find out more. The tracefile will tell you the two rowids which generated the deadlock; you can find out the object id using dbms_rowid.rowid_object() and plug that into select object_name from all_objects where object_id = :oid;. Depending on how your organisation arranges things you may not have access to the tracefile directory, in which case you will need to ask a DBA for help.

Once you know what table is in deadlock you know what you must change in your application logic. Potentially that's quite a big change, as your code has a number of red flags (long trigger bodies, two triggers on same event, autonomous transaction). Good luck!

Upvotes: 4

Related Questions