Dear Team ,

We are using mysql in our oraganisation. We are
anlysing the migration to firebird. But one blocking
factor is meta data update. As of now when we have
multiple connection and when we try to create table
with "foreign key" it throws meta data error.
On seraching the knowledge base we found the
below link,


http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='6'

Can anyone please let us know why Firebird has
such a behavior?

Steps we did ,

1) started the server (super server)
2) I created a table say "A" .
3) Then shut the firebirdserver and started again.
4) Then opened two connection.
5) Via one connection we created table B having FK
with table A. we got a below error , Then we got a
below error.
Statement failed, SQLCODE = -607
unsuccessful metadata update
-object COMPANY is in use

as soon a connection is created, it seems the Firebird
locks the meta data information. Even if the concerned
table is not being accessed.

On searching the net, got the below information,

From Claudio, 14-mar-2002:

"Object in use is different than exclusive access to
db is need or secondary
attachments cannot validate db.
Object in use means that in the same session, you did
a select (probably an
update, delete, insert as the same effect) on a table
or procedure that's
going to be altered. There are two solutions:
- You do a hard, explicit COMMIT. Autocommit and
commit retaining don't
work. Most of the time a hard commit works.
- You play safe and detach. If there's no other
attachment, this action
ensures the engine itself unloads the db from memory
and frees the file
handles associated with it. Next time you connect,
your metadata change
should succeed immediately."

From Ann, 20-Dec-2001:

"The two errors["unsuccesfule metadata update" and
"object in use"]
you're seeing are really the same - object in use is a
secondary error
that clarifies the more general "unsuccessful metadata
update". The
error arises when some connection has an "interest"
lock on the object
in question. "Interest" locks don't interfere with
reading or writing
instance of the object, but prevent deletion or
modification of the
object itself. A prepared request gets "interest"
locks on the tables
and indexes it uses."


I know some improvements are supposed to be made for
FB 2.0 and some
has been made in FB 1.5. And, I think the only way for
this type of
error to be totally eliminated might require that the
DDL statement
execution be deferred to a later time when the objects
that it
requires are no longer in use. And, this could mean
that if the
object(that the DDL statement needs) be in use for 72
hours, as an
example, the DDL statement wouldn't be executed before
then. Also, I
think this could(and most likely would) generate
support questions as
to why the changes are not made immediately after the
DDL statement
was sent to the server.


But still I'm unable to understand the exact reason
for the Firebird to have this constraint. The other
databases we used like MYSQL , Oracle supports this
functionality. I believe this will add more value to
firebird to support this behavior. Firebird team: is
there any plan to support this or is there any
workaround to support multiconnection meta data
update?.

I'd appreciate any links/document of firebird
architecture/design that explains this behavior.

with thanks & regards,
S.Karthick

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com