nerometal.blogg.se - Ora 00060 deadlock detected while waiting for resource

#Ora 00060 deadlock detected while waiting for resource update#
#Ora 00060 deadlock detected while waiting for resource archive#
#Ora 00060 deadlock detected while waiting for resource code#

Tail /u01/app/oracle/diag/rdbms/avdb/AVDB/trace/alert_AVDB.log SQL> alter system set events 'deadlock trace name hanganalyze level 4' This setting will disappear when you restart the database. This will only produce a trace file if and when when the ORA-60 happens and does not have any other effect on the database. At least that way, you implement the controls you require and everything is a lot simpler and (more importantly) transactionally sound.In order to know if there was some sort of hang situation involved to create the ORA-60, you can enable an event in the database that will produce more information to analyze.

#Ora 00060 deadlock detected while waiting for resource update#

My advice is to use a PL/SQL procedure to perform this type of data manipulation and disallow direct update statements against the table. Generally speaking if you start to hit deadlock or mutation issues, the approach is wrong. I'm generally not an advocate of having this kind of application logic in triggers - for the reason illustrated above and others.

The first trigger cannot complete until the second one completes - but the second one will never complete because the originating update statement is blocking it.

The insert into the proj_archive_tbl table causes the trigger mark_proj_deleted to fire - this cannot update the row in project_tbl because the original transaction is still holding its row lock from when you updated the status.

Your trigger arch_proj_trigger is then firing after the update and is inserting a row into the table proj_archive_tbl.

When you update a row in project_tbl, your transaction will obtain a row lock and apply the changes.

Here's what's happening to cause the deadlock: I hope I am using the correct forum and that sufficient information was supplied. Update project_tbl set delete_status = 'X' If upper(:new.proj_status) = UPPER('completed') then

#Ora 00060 deadlock detected while waiting for resource code#

Here is the code if anyone can please help me or direct me in the right way it will be greatly appreciated: create or replaceĪfter update of proj_status on project_tbl I have tried created a package with array but I struggle since I am a newbie with oracle and have also attempted to follow asktom recommendations, compound triggers and antonomous_transactions but all to no avail.

#Ora 00060 deadlock detected while waiting for resource archive#

Now I have create 2 triggers, one to copy data to archive table when status change to completed on project table and the other trigger, when new row is inserted into proj_archive_tbl, update project delete_status to 'X' but get the "deadlock detected while waiting for resource". My 1st attempt was a trigger that perform and insert into proj_archive_tbl and than update the project_tbl delete_status = 'X' but I get mutation error. My logic is, when the column in project_tbl called proj_status change to completed, it must insert the whole row into proj_archive_tbl and than change the column deleted_status in project_tbl to 'X'. Basically I have a table called project_tbl and another table which is a copy of this table called proj_archive_tbl. My initial error was table mutation error and I understand that oracle is still busy with the table and therefore could not perform my 2nd task.

I have been struggle to get this query up and running but keep on running into dead ends, I have read through quite a few forums but I cannot find a solution for my issue.