Re: MUTEXes in PLSQL?
Date: Sun, 17 Feb 2008 22:38:26 -0500
Message-ID: <13rhvdnh6uvladc@news.supernews.com>
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. Received on Sun Feb 17 2008 - 21:38:26 CST