2 sessions executing same procedure same time

[manodba]

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.

[pando]

dont get your question, sounds contradictory

[manodba]

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?

[manodba]

By proc, i mean stored procedure, also in 1st posting, i mentioned package, but take it as procedure....

[DaPi]

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.

[slimdave]

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.

[DaPi]

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).

[DaPi]

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

[slimdave]

I'm sure that they don't survive. (yeah,i see where you're going with this, Mr Smarty Pants)

[DaPi]

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).