Hi Helen

>
> At 10:46 AM 3/03/2006, you wrote:
> > > > WRT Classic gotchas - We (and another contributor to this ng)
> > > > found
> > > that we
> > > > could not make changes to stored procedures in classic without
> > > shutting down
> > > > and clean starting - got an 'object in use' error.
> > >
> >
> >Paul,
> >
> >I have just completed testing this, and do not have the same problem.
> >I suspect you may have had a lingering connection with a transaction
> >that had used this stored procedure. The same thing would have
> >happenned under Superserver.
>
> Yes and no. Classic and SS lock the BLR for stored
> procedures in different ways. As I understand it (though Ann
> will surely correct me if I've got it wrong!) under SS,
> changes to SPs are deferred if the BLR is locked due to
> presence of the old version in the cache. Once it has
> released, the lock level will be reduced and the BLR will be
> silently updated. In practice, it generally won't occur
> until the database goes off-line (all connections detached).
>
> Under Classic, for which lock management is different, the
> lock on the BLR will persist until the last process that used
> the SP has ended, regardless of whether the SP is still in
> use or not. AFAIR, deferral is thus not possible, hence the
> "Object in use" exception is almost certain to occur if you
> try to change the BLR while the system is busy.
>

This very neatly explains my experience. However the update in SS always
does occur apparently immediately which must be as soon as the locks are
released.

> So, while Paul's observation is correct from a practical POV,
> it's not from a theoretical POV. And that's without going
> into the wisdom or otherwise of updating metadata on a busy system...
>
> ./heLen
>

I cant envisage an alternative to being able to update a system while it is
in use. As for the wisdom of it - well we have been doing it for 11 years
and have never caused a crash or corruption by doing so. In fact we would
have caused many hours of downtime if we had shut down to make changes.

Regards
Paul