Re: how to lock a table?

Hello.

If you need to do a select of some records that you want to lock, so that you can complete a series of transactions as a logical unit, use the Select for update syntax as follows.

Do the select as normal
The last phrase in the select should be
FOR UPDATE OF update_list NOWAIT

This represents the command in the longest form, if you leave off the OF update_list, it locks each entire row that is selected. The NOWAIT is also optional and causes the transaction to terminate immediately if the rows selected are already locked.

You should look up the full details of the command in the SQL language reference as there are some group functions and things like DISTINCT that, if I remember correctly, you can't use with the FOR UPDATE clause of a select statement.

Lots of luck. :)

-- 
Kenneth E. Murphy
Unverisity of Missouri - Columbia
Office of Social & Economic Data Analysis
kenneth_at_oseda.missouri.edu

Sascha Bohnenkamp <bonito_at_informatik.uni-bremen.de> wrote in article
<6c1cj5$g47$1_at_kohl.informatik.uni-bremen.de>...


> 

> Helo,

> 

> I use pl/sql on oracle7.x and have the following problem:

> 

> I want to update a set, if it exists, or else insert it into one table

 ..


> 

> I tried

> 

> lock table in exclusive mode

> 

> select from that table

> if(exists)  

> 	update table

> else

>  	insert into table

> 

> 

> well it does NOT work, because the lock does NOT lock the select, but

> the update-insert ... 

> 

> I solved it with something like 

> set transaction mode serializable

> 

> but this seems to me like a hammer ... what is the REAL solution, please

 !!!!


>