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

From: Mark J. Bobak <mark_at_bobak.net>
Date: Tue, 04 Nov 2003 09:47:53 -0500
Message-Id: <pan.2003.11.04.14.47.50.524633@bobak.net>

On Tue, 04 Nov 2003 19:22:51 +0800, Connor McDonald wrote:

> Carlo Sirna wrote:

>>
>> Hi, maybe this is a faq but I didn't find any solution...
>>
>> I have a table like this one, which describes some time intervals in
>> the form [startdate,enddate].
>>
>> create table mytab
>> (
>> startdate date not null,
>> enddate date not null
>> )
>>
>> I don't want this table to contain overlapping intervals: it must be
>> impossible to insert a record that overlaps with an already existing
>> one or to update an existing record in order to make it overlap over
>> onto another one.
>>
>> I had no problems in writing a before insert trigger that performs the
>> required test, but I don't know how this check could be performed in a
>> before update trigger.
>>
>> p.s: this table contains about one hundred thousands of records: I
>> can't keep it all in package variable...
>>
>> Any Ideas?
>>
>> Thanks in advance,
>>
>> Carlo Sirna

> 
> 
> The basic mutating table avoidance is:
> 
> before-statement-trigger:
>   clear a PL/SQL table
> 
> after-row:
>   add rowid for this row to PL/SQL table
> 
> after-statement-trigger:
>   use each rowid and do the work you would have liked to have done
> within the row level trigger
> 
> 
> Easy enough - but if you have concurrent users modifying this table, you
> will need to use something like dbms_lock or a sentinel table to control
> access to the table whilst you do your work.
> 
> Connor

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 Received on Tue Nov 04 2003 - 08:47:53 CST