2 sessions executing same procedure same time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: 2 sessions executing same procedure same time

  1. #1
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    dont get your question, sounds contradictory

  3. #3
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    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?

  4. #4
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    By proc, i mean stored procedure, also in 1st posting, i mentioned package, but take it as procedure....

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm sure that they don't survive. (yeah,i see where you're going with this, Mr Smarty Pants)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  



Click Here to Expand Forum to Full Width