Home » SQL & PL/SQL » SQL & PL/SQL » Autonomous transaction on trigger
Autonomous transaction on trigger [message #355960] Tue, 28 October 2008 13:53 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
I have a scenario explained below.

I have table A which has trigger on it. Insert or update trigger on each row. When any records get inserted or updated in table A, id will be logged into table B. There is a job running every 15 mins which does some process based on records in table B and it updates the processed records in table B once finishing the job. The update (job) puts lock on table B and it appears that during insert on table A the triggers fires and it goes on waiting mode.

I think of below solution –

If I have the trigger as Autonomous transaction, will that help? If not can anyone have better answer?

Thanks
Nav

[Updated on: Tue, 28 October 2008 13:56]

Report message to a moderator

Re: Autonomous transaction on trigger [message #355962 is a reply to message #355960] Tue, 28 October 2008 14:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If the job on table B is doing the locking, then trying to do an autonomous transaction in the trigger on A will not help.

But, if the trigger is only INSERTING in B, then the trigger should never go into waiting mode anyway, since it is not possible that a record that isn't there yet is locked.

( Unless the process is explicitly locking the entire table, in which case you would have to take a look at the process and figure out if that could be changed. )

[Updated on: Tue, 28 October 2008 14:11]

Report message to a moderator

Re: Autonomous transaction on trigger [message #355963 is a reply to message #355962] Tue, 28 October 2008 14:16 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

trigger does an update...
something like .. when there is insert or update on table A, trigger will be fired and inside trigger function logic is - If already id exists in table B update else insert.

[Updated on: Tue, 28 October 2008 14:21]

Report message to a moderator

Re: Autonomous transaction on trigger [message #355965 is a reply to message #355963] Tue, 28 October 2008 14:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So when the trigger fires and wants to do an update while the job is still running and has already locked the row for another update on the same row, what should happen?

The way it looks now, either commit in the job more often that it doesn't lock the rows that long, or skip the locked rows when you try the update in the trigger. Depending on what would be more "correct" data-wise.

Re: Autonomous transaction on trigger [message #355967 is a reply to message #355960] Tue, 28 October 2008 14:36 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

thanks Thomas,
looks like the transaction in UI keeps waiting... I want transaction in application to go through fine and its okay if update by trigger waits. So i was thinking to call an autonomous package from the trigger function. this autonomous package will look for id in table B and do insert or update.

will this work? i know i have to test it and will do.. but wanted to know if this will cause any other issue...

[Updated on: Tue, 28 October 2008 14:37]

Report message to a moderator

Re: Autonomous transaction on trigger [message #355970 is a reply to message #355967] Tue, 28 October 2008 15:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, it will not work with an autonomous transaction in the trigger.

An autonomous transaction in the trigger would allow you to COMMIT earlier in the trigger, but it will be of no help when the other table is already locked by something else.

The solution would depend on whether you want the UI or the background job to have "the last word" on what ends up in table B. Then either change the job to commit more often, or skip the locked rows in the trigger.

In general:

Autonomous transactions should only be used when you know EXACTLY who is locking who and why, and what the consequences will be when a already executed autonomous transaction doesn't get rolled back when the client issues a rollback for some reason.

(e.g. you could wind up with an updated table B, even though the transaction that started it was rolled back. Throwing around autonomous transactions because of not really hunted down locking problems will lead to disaster sooner or later.)
Re: Autonomous transaction on trigger [message #355972 is a reply to message #355960] Tue, 28 October 2008 15:36 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

thanks Thomas...
Re: Autonomous transaction on trigger [message #355973 is a reply to message #355960] Tue, 28 October 2008 15:39 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

autonomous transaction in the trigger did not worked ....
Re: Autonomous transaction on trigger [message #355975 is a reply to message #355973] Tue, 28 October 2008 15:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
navkrish wrote on Tue, 28 October 2008 21:39
autonomous transaction in the trigger did not worked ....


Heheh. Told you so? Wink

The problem is a logical one. When this happens in chronological order:

1. the job starts and updates a row in Table B
..... one minute later
2. the trigger fires and tries to update the same row in Table B
..... one minute later
3. the job commits his update.

then the UI will hang for one minute between 2) and 3)

So either commit the updates in the job right after they happen, or skip the updates in the trigger if the row is still locked by the job.

Those are the two options you have. So first you would have to figure out which of the two options would be more "right".

Or another solution :

Don't let the trigger change anything in Table B directly, but write the things the trigger should do into an intermediate table, that gets processed by the batch job prior to what it's doing now in Table B.
Re: Autonomous transaction on trigger [message #355977 is a reply to message #355960] Tue, 28 October 2008 17:02 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

I thought of intermediate table but one issue is the batch job runs frequently for every 10 mins. So we have to schedule another job between. Will try anyway. thanks for the solution.

Another solution I thought of is to insert always even if its insert or update. This how application will not hang.

Thanks again
Previous Topic: sql query - taking long time
Next Topic: Query returning 6 Records
Goto Forum:
  


Current Time: Fri Dec 02 12:38:01 CST 2016

Total time taken to generate the page: 0.08854 seconds