Hi,
How can i prevent 2 sessions executing a procedure at a time? Requirement is thet when the package is still running, it should be run again.
Printable View
Hi,
How can i prevent 2 sessions executing a procedure at a time? Requirement is thet when the package is still running, it should be run again.
dont get your question, sounds contradictory
Will exlpain more clearly...
Well, one of my procs writes to a OS file using utl_file and will rename the file at the end. But if the proc is called again while it is still running, the file gets overwritten.
I am wondering if there is any way to ensure that a particular proc will not be run while it is still running.
One more question, Is there any BEFORE EXECUTE trigger?
By proc, i mean stored procedure, also in 1st posting, i mentioned package, but take it as procedure....
One way to allow everyone to execute at the same time could be to use unique file names e.g. generated fom the audit sid (sys_context('USERENV','SESSIONID')). These could then be concatenated at the OS level.
If that can't be arranged then you need to create a 'resource' that you can 'enqueue' on - this could be just a row in a table with values 'BUSY'/'FREE' that is tested (with locking) and updated at the start and then reset at the end of your proc.
I don't like that use of tables to serialize access to a resource -- this is exectly what the DBMS_LOCK package is intended for.
Have a look at the Supplied PL/SQL Packages and Types Reference for details.
That's the way to do it. I guess I have seen it before, tahiti search on "enqueue resource" only finds the DML_LOCKS parameter . . . (lame excuse).
slimdave (since you seem to know about these things), am I right in assuming that locks set by DBMS_LOCK don't survive shutdown/startup or crash/recovery? (couldn't find it explicitly stated in the doc). TIA
I'm sure that they don't survive. (yeah,i see where you're going with this, Mr Smarty Pants)
HO HO HO !
(To spell it out for anyone who doesn't use ESP v7.2) So if I use the DBMS_LOCK, I'll still want maintain a status table for "what the hell was going on" post-crash diagnosis. In which case . . . . why not enqueue on a row in that table?
slimdave, what don't you like about that method? (the European jury is out on this one - no axe to grind).
How are you going to distinguish between the two cases of "the procedure is already running", and "the procedure failed"? It seems like the table-based method provides no methodology for this.
how about this ...
Use DBMS_LOCK to serialize the procedure.
Use table-row to indicate that the procedure has been started and not finished.
The table part may not be required, if the procedure uses temporary tables and commits only after complete units of work have been completed, as it would be "self-cleaning" if the procedure failed, or instance shutdown/crashed.
I suppose after a while it starts depending on the complexity of the process. I'd still use DBMS_LOCK to indicate "process already running", though.
Point taken. Thanks.Quote:
Originally posted by slimdave
How are you going to distinguish between the two cases of "the procedure is already running", and "the procedure failed"? It seems like the table-based method provides no methodology for this.
(The process that is occupying my mind at the moment is a great usine Ã* gaz where a unit of work includes SQL, OS scripts and some Access - I'll keep on using the table as well.)
P.S. Come to think of it the orignal problem in this thread will need the table since UTL_FILE output won't be rolled back.
Maybe a virtue could be made out of necessity by having a table to log the procedure activity ... indicating start time, end time, input parameters, results (rows written etc).
That would probably be the kind of thing you'd want to include anyway, and it could also be checked at the start of the procedure to see if cleanup from a prior run is required.
My personal usine Ã* gaz was a wrapper for a c-based EDI extract program -- shell scripts invoking perl to check SQL-spooled results of PL/SQL-built tables to validate the output of the C program. Don't forget to not document anything -- this is potentially your retirement fund that you're working on.
Now you really are teaching gandpa to suck eggs :)Quote:
Originally posted by slimdave
Don't forget to not document anything -- this is potentially your retirement fund that you're working on.
Sorry -- just proselytising to the newbies.Quote:
Originally posted by DaPi
Now you really are teaching gandpa to suck eggs :)
Hi, back after weekend. Thanx for all the suggestions.
Procedural options always exist....like one of those interesting one slimdave had suggested! but i wonder why there is no before execute trigger? any ideas....
News::I am Certified Oracle 8i DBA from yesterday!!!