Re: stored procedures in Oracle

A copy of this was sent to paul cluiss <paul_cluiss_at_intervoice.com> (if that email address didn't require changing) On Tue, 21 Sep 1999 09:28:25 -0500, you wrote:

>All,
>
>I need to know if Oracle stored procedures and functions have built-in
>concurency control, so that in a multi-user environment at most only one

Oracle the database has builtin concurrency control.

>user can execute the stored object at a time. My Oracle PL/SQL book

No way -- it allows as many people as your machine can handle to simultaneously run the procedures. Databases are built to support concurrency -- not limit it!

>hints at this but I would like confirmation from y'all. To put it
>another way, if my system has 5 users and I write a stored procedure to
>insert some records, and this procedure is the only way my users insert
>the records, then will Oracle do the appropriate locking on the tables
>for my users thereby maintaining data integrity?
>

Yes it will -- but it will do it concurrently, not serially.

If you have a primary key on the table and 2 processes attempt to insert the same exact value -- one will get blocked and possibly fail. It preserves integrity.

If you insert 2 different values -- they will happen concurrently.

>Furthermore, will Oracle automatically rollback the transaction if it
>should fail? I haven't had much success trying to code BEGIN
>TRANSACTION; ... END TRANSACTION; statements in PL/SQL.
>

No, rollbacks are never automatic. Statements are automatic though, so if you issue:

INSERT INTO T values ( 1 );

and there is a trigger on T that inserts into another table X and the INSERT into T fails then all of the work done by the insert into T will be undone (including any work done by the trigger on table X that may or may not have fired).

A stored procedure works in the same manner. if you have:

create procedure p
as
begin

   insert into t1 values (1);
   insert into t2 values (2);
   insert into t3 values (3);

either ALL three inserts will succeed or NONE of them will when you execute that procedure. If I had error handling in the procedure -- that might change that statement somewhat as I can catch an error and ignore it if I want to. If an error propagates out of that procedure tho -- I can be certain that the procedure did no work at all (well, in Oracle8i release 8.1 there are autonomous transactions but thats outside the scope of this particular discussion)

bottom line -- you want to rollback issue:

   ROLLBACK; if you want to commit, issue:

   COMMIT; transactions do not have to be begun in Oracle -- they start with the first statement you submit implicity.

You might consider giving the server concepts a good read through -- it contains Lots of valuable information such as the above on how this all works in Oracle.

>Thanks a bunch,
>
>Paul

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 21 1999 - 10:24:37 CDT