Re: MUTEXes in PLSQL?
From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 17 Feb 2008 12:48:09 -0800
Message-ID: <1203281263.450982@bubbleator.drizzle.com>
>> 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.
Date: Sun, 17 Feb 2008 12:48:09 -0800
Message-ID: <1203281263.450982@bubbleator.drizzle.com>
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.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Feb 17 2008 - 14:48:09 CST