-
2 sessions executing same procedure same time
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).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|