Re: MUTEXes in PLSQL?

On Feb 17, 10:38 pm, DJH <NOS..._at_NOSPAM.COM> wrote:
> DA Morgan wrote:
> > DJH wrote:
> >> DA Morgan wrote:
> >>> DJH wrote:
> >>>> I am writing a trigger to calculate the value of a column in a row
> >>>> in a
> >>>> another table in  processing the current trigger for a different table.
> >>>> I need to make sure that the row in the other table is not updated by
> >>>> other processes while I am processing this trigger.
>
> >>>> How may I do that?
>
> >>>> Also can you run a "select count(*) where .." on the current table you
> >>>> are processing in the trigger without getting "mutating.. type errors."
>
> >>>> I am using 10G.
>
> >>>> I need to check the inventory of the item before I can let the order
> >>>> through but also I need to check current order table to see if there
> >>>> are
> >>>>  any unprocessed orders that pre-reserved the same items from the
> >>>> inventory.
>
> >>>> So total available items in inventory  will be Inventory Items -
> >>>> Unprocessed Order PreReserved Items.
>
> >>>> If available items are not there to process current order I have to
> >>>> prevent it from going through. Meanwhile I cannot let anyone update
> >>>> the Inventory until I finish inserting the order with its pre-reserved
> >>>> items.
>
> >>>> Thanks
> >>> SELECT *
> >>> FROM second_table
> >>> WHERE some_condition
> >>> FOR UPDATE;
>
> >>> Read the docs before you do this so you fully understand what is
> >>> happening.
>
> >> Thanks DA but I get his error:
>
> >> I am using a select count() statement with 'for update' in a trigger.
>
> >>  ORA-01786: FOR UPDATE of this query expression is not allowed
>
> > What query? You've not posted a query.
>
> > Provide full version info. too.
>
> Re: Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
>
> DA I could not do a
>
> select sum(inv_total) into localvariable from  inventory where
> product_id = 'something' for update;  -- results in compile error
>
> I could do:
>
> select inv_total into localvariable from  inventory where product_id =
> 'something' for update;  -- this will lock those records but cannot get
> sum of inv_total for the product.
>
> But the above will also throw an exception since it will return many
> records for some products with multiple inventory records per product -
> some being reserve inventories etc.
>
> I would prefer to do the sum() operation to get the total from all
> inventory records for a product and perform a lock operation so that I
> can check and decrement while the record/row is locked.
>
> All of this in the trigger for the order being submitted.
>
> Again I want to get a sum and lock at the same time

do the sum yourself in a loop. Sounds like you would have 2 inventory rows (regular stock and reserved?) so the loop will be fast.

This seems like an odd table design, maintaining inventory in two places for the same stock.

  Ed Received on Mon Feb 18 2008 - 07:10:22 CST