Re: MUTEXes in PLSQL?
From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 20 Feb 2008 15:36:17 -0800
Message-ID: <1203550552.667175@bubbleator.drizzle.com>
>
> 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.
Date: Wed, 20 Feb 2008 15:36:17 -0800
Message-ID: <1203550552.667175@bubbleator.drizzle.com>
DJH 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.
Follow Ed's advice but I want to point out to you that you are your own worst enemy in this.
I asked you to post the query ... you sort of did so but rather than posting a real query you made something up.
Now you post "throw an exception" and seem to think that someone trying to help you doesn't need to know what that exception is.
Is there some reason why you are making it hard for people trying to help you?
-- 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 Wed Feb 20 2008 - 17:36:17 CST