Lock Duration

[sjadhavdba]

I'm havin one pl/sql script where i'm firing an update statement. What i want is if the script is not completed its execution within a certain duration of time ( say within 60secs) i need to rollback it. Cause i'm not affording the locks for such a long duration.

[julian]

Originally posted by sjadhavdba
I'm havin one pl/sql script where i'm firing an update statement. What i want is if the script is not completed its execution within a certain duration of time ( say within 60secs) i need to rollback it. Cause i'm not affording the locks for such a long duration.

You don't rollback scripts, you rollback transactions. Do you want to abort the script after 30' if it has not finished by then? It's a bit risky to do such things.

[sjadhavdba]

thanx julian

i know we rollback transactions. thats why i've mentioned "update statement".

and thats exactly what i want . do u have any answer

[julian]

Is the version 9i? Then you may use the WAIT N clause.
If V < 9i, then tell me do you run it as a job or do you start the scrip manually.

[pando]

wait N is for select for update isnt it? wait N seconds if you cant get the row then return error...

[jmodic]

Yes, WAIT N does tell how long should you wait to obtain a lock on a row that is currently locked by some other transaction, you can not use it to limit for how long you should keep the lock on a row.

Nevertheless, it is a very strange requirenment to limit the time in which a particular transaction should execute and to roll it back if this time is exceeded. I think the closest think to meet this requirement would be to limit the CPU_PER_CALL in the calling user's profile. And yes, I know it is not the same, it is just the closest thing I could think of.

[julian]

He can set the resource plan directive parameter MAX_ESTIMATED_EXEC_TIME. If more than 30', then the operation will not start.