Home » SQL & PL/SQL » SQL & PL/SQL » Deadlock Issue in the trigger .......please do help (oracle 9i)
Deadlock Issue in the trigger .......please do help [message #364566] Tue, 09 December 2008 02:10 Go to next message
jaysri
Messages: 2
Registered: December 2008
Junior Member
Hi all,

I do have four attibutes start_date ,end_date ,fiscal_start_yr,
fiscal_end_yr
start_date,end_date in table1
fiscal_start_yr,fiscal_end_yr in table2.

We do use java base application GUI.

Requirement: When project start and end dates are changed from GUI ,DB trigger has to fire and fiscal years are set accordingly.

and when fiscal year are changed from GUI ,Db trigger has to fire and project dates should set accordingly.

so to achive this i creared the following two triggers.
This causes deadlock as first triggers update action causes second trigger to fire and vice versa.

Please do help if this can be achived in any way.

Thanks in advance.

CREATE OR REPLACE TRIGGER table1_723
AFTER UPDATE OF start_date,finish_date
ON table1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
update table2 a
set fiscal_start_yr = XXX,fiscal_end_yr= XXXX
where a.id = :NEW.id;
END table1_723;


CREATE OR REPLACE TRIGGER table2_723
AFTER UPDATE OF fiscal_start_yr,fiscal_end_yr
ON table2
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
Update table1 a
set finish_date = xxx,a.start_date = xxx
where a.id =:NEW.id;
END table2_723;
Re: Deadlock Issue in the trigger .......please do help [message #364574 is a reply to message #364566] Tue, 09 December 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
This causes deadlock as first triggers update action causes second trigger to fire and vice versa.

Set/clear a package variable in your triggers to flag that the other trigger is currently running.

Regards
Michel

Re: Deadlock Issue in the trigger .......please do help [message #364575 is a reply to message #364574] Tue, 09 December 2008 02:42 Go to previous messageGo to next message
jaysri
Messages: 2
Registered: December 2008
Junior Member
Michel,
Thanks for your reply.
Can you elaborate please.

I am not using any package or procedure.
Just a simple update statement in the triggers.

Thanks
Re: Deadlock Issue in the trigger .......please do help [message #364576 is a reply to message #364566] Tue, 09 December 2008 02:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It seems unlikely to me that you'd get a deadlock off this, as both triggers will fire from the same session (unless you're doing something stupid with Autonomous Transactions), and your locks don't prevent you from re-altering the changes that you've made.

What is the actual error message that you're getting?

I'd guess it's some sort of maximum recursion level thing, or the session is just hanging as your triggers keep firing each other.

You will need to describe what you think should happen.
Given the logic you have described, an infinite loop is a reasonable outcome.

I suspect that the solution will be an additional check in trigger on Table 1 to check if the new financial year would be different to the current financial year, and to only perform the update if that is the case.
Re: Deadlock Issue in the trigger .......please do help [message #364578 is a reply to message #364575] Tue, 09 December 2008 02:45 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I am not using any package or procedure

Create a package with just a variable to set and clear in the triggers.
Carefully read JRowbottom's comments.

Regards
Michel
Previous Topic: Request for a specefic date?
Next Topic: Need Help on Loop Program in PL/Sql
Goto Forum:
  


Current Time: Mon Nov 11 01:03:04 CST 2024