Re: Q: elementary transaction question

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:

>Such a simple question, but nothing I am reading makes me feel confident I
>have this right.
>
>A single plsql procedure needs to select from one table, and then insert
>and update other tables. The inserts and updates have to be in sync with
>the selected table, i.e. the table being read mustn't change in ways that
>would alter what my routine decides to put into the other tables.
>
>In theory, another process might update all the tables as a single
>transaciton, and what I don't want to be able to happen is that table 1 is
>updated after I read it, and before I get to the point of updating or
>inserting the other tables.
>
>What IS the correct way to prevent this? Will savepoint do this, do I even
>need to do anything? The oracle manual has lots of examples of multiple
>updates , or read-only consistancy, but I don't see this.
>
>
>
>(not correct plsql, just outline)
>
>procedure maybe_update()
>
> cursur c1 select the_number from table1;
> cursor c2 select * from table 2 for update of some_thing;
>
>begin
>
> open c1
> loop until certain record found
> end loop
>
> open c2
> loop
> if interesting record is found
> then update it; end if;
> end loop
>
> insert into table3 values based on table 1 and table 2;
>
>end
>
>Feedback welcome, thanks.

Oracle will handle this by default..Once the cursor is opened the data is 'frozen' in time..The data read will not be affected by any changes that take place while the cursor is open ( Read consistency is an Oracle guarantee ). NOTE: The cursor must remain open to be sure of this..closing and re-opening may 'void' that guarantee..

The target tables would only be 'frozen' if you use the 'select for update' type cursor as you have done.   Received on Tue Dec 14 2004 - 13:06:33 CST