Re: Transation to pretect verification and update

Zhao wrote:

>I am not sure if this the right forum to ask/discuss.
>
>I have some an item/row (in an order) need to insert into table A (the
>order table) and also need to remove the item from table B (the
>inventory table).
>But before I can do that I need to verify if B have the item available
>to be
>removed.
>
>I questions are:
>
> - if I include the verification in the transaction, will it looks the
> item in B so that no other thread can not remove it before I
>commit?
>
> - I can either handle the transaction in the caller (a servlet) or
>from
> PL/SQL. Is there any advantages doing it in PL/SQL besides
>bandwidth
> and efficiency?
>
>thanks
>
>Zhao
>
>

You will need to use the SELECT FOR UPDATE syntax to lock the record to make sure another session does not delete it.

Use PL/SQL not a servlet.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)