Re: MUTEXes in PLSQL?

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 16 Feb 2008 08:49:07 -0500
Message-ID: <61o7urF1rooanU1@mid.individual.net>

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

Do you just use the COUNT() because you don't want the values? You could pump the values into an array instead (BULK COLLECT)

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Sat Feb 16 2008 - 07:49:07 CST