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.

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