Peter Ypenburg wrote:

>
>
> The problem is that sometimes from creating the stored proc to selecting
> data from it something goes wrong on the client side (e.g. user lost
> connection). Now when he opens the program again and redoes the process and
> at the point the code tries to create the stored proc again the following
> error gets thrown:
> Lock conflict on no wait transaction unsuccessful metadata update object
> OPEN_ITEMS_7 is in use.

OK. That's a bad (well, misunderstood) error message, and quite a
different problem from two users trying to change a normal record.
You've created a metadata object and want to replace it, but it's
visible to other users. That's one reason why creating metadata objects
as part of normal application processing is a bad idea.

>
> This error (I think) is because there is still a transaction alive
> implicating the stored proc and thus the error occurs. The question is about
> how do we fix this.

One possibility is to recognize the error and create a new procedure
with a different name OPEN_ITEMS_7A for example. Firebird has no
concept of "private" metadata - which is what you're tying to create -
and any change to let you drop procedures that others can see is going
to allow other, more serious problems.

> To answer your question, yes we are seeing some other behavior. I read on
> the FB list that there is a timeout for transactions build into FB 2, and
> that's the kind of thing that will really make a big difference in our
> lives.

Maybe, maybe not. The problem of metadata visibility and the need for
persistence is not going away. Though we might try to add a better message.

Regards,


Ann