Q: elementary transaction question

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. Received on Tue Dec 14 2004 - 12:46:51 CST