Re: MUTEXes in PLSQL?
Date: Sun, 17 Feb 2008 22:38:26 -0500
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
>>>> any unprocessed orders that pre-reserved the same items from the
>>>> 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
>>> SELECT *
>>> FROM second_table
>>> WHERE some_condition
>>> FOR UPDATE;
>>> Read the docs before you do this so you fully understand what is
>> 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. Received on Sun Feb 17 2008 - 21:38:26 CST