-
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!"
-
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
-
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!"
-
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
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|