DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: one procedure can be used by pultiple users/seesion?

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi All, I have one doubt, could anyone please tell me whether one procedure can be used by pultiple users/seesion at a time. Like for table we have this locking mechanism so two users can't access the table at the same time. Do we have same for procedures, functions, packages also?
    Pls tell me. Thanks in advance...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    yes, it can. Each session has its own "instance" of a stored proc/package including variables.

    But why do you think two users can't access a table at the same time?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    If a table is been locked by one sessoin, how other session can use the same table. Only after all the locks are released on that table, the other session can use the same.
    Plz correct me...
    "Greatest Rewards come only with Greatest Commitments!"

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Now I feel I didn't undertstand well.
    Is your intention to "lock" a procedure, restrict access to only one session at time?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Be more specific with the term "use". If one session locks a table, no other session is prevented from "using" that table, ie they can *select* from that table without any limitations(but they can not modify it, of course, if that was a table lock).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Multiple people can access the procedures at the same time unless you write something to prevent them. You would have to involve a database table, since package variables cannot be shared between sessions.

    To do this use a control table and lock a specific row in it at the start of the procedure. If someone is already locking that row your procedure will wait until it is free. This way the procedure becomes serialized.

    This sort of thing is fine for specific cases, but it's a bad idea to start doing this alot since the server becomes a single threaded process and hence very slow. In the same way, issuing table level locks is really bad karma.

    These sort of processes will impact on performance and scalability in a massive way. It's far better to write good code in the first place.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by ales
    Hi,
    yes, it can. Each session has its own "instance" of a stored proc/package including variables.
    More exactly:
    Each stored procedure/function/package has ONE code instance for all sessions in PUBLIC sql area and
    set of areas for variables (ONE per session) in PRIVATE sql area.

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