| Autonomous transaction on trigger [message #355960] |
Tue, 28 October 2008 13:53  |
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  |
ThomasG
Messages: 3212
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 #355965 is a reply to message #355963] |
Tue, 28 October 2008 14:28 |
ThomasG
Messages: 3212
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 |
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 |
ThomasG
Messages: 3212
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 #355977 is a reply to message #355960] |
Tue, 28 October 2008 17:02 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
