Re: Avoiding "ORA-04091 table string.string is mutating"

>
> And, I'll say it, since Connor implied it, but didn't spell it out:
> I hate this design, it screams of serialization problems. That is,
> the "dbms_lock or a sentinel table" that Connor mentions is likely
> to really hurt you in terms of scalability. If you expect to
> support a significant number of concurrent users, now is the time
> to reconsider your design, if possible.
>
> -Mark

i don't think the potential serialization is a problem with the design, i think it's a natural consequence of the business rule.

anytime a business rule has to be enforced in something more complex than UNIQUE or PK constraint (which Oracle can validate before transactions are committed), the scope of the rule validation must be protected. and since Oracle provides statement level read consistency by default, the implication is that something needs to be locked to protect the scope at the beginning of the operation (i.e., to prevent DML that could affect the scope until the validation is complete).

for instance, if i'm checking a customer's credit limit for approving an order, and want to be totally accurate, i need to prevent any other orders from being approved while mine is being validated against all appropriate orders (otherwise, i've got a moving target and can't guarantee that the rule was enforced with all available data)

one alternative design to minimize serialization is to do all DML processing but somehow indicate that the affected records are not validated (i.e. the customer credit order status is 'pending') then set the appropriate lock (ie. at the customer level for the credit order validation operation) during the validation process -- potentially shortening the serialization window.

another alternative is to do the validation in batch (ie., every xx seconds/minutes validate all submitted customer credit orders)

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048