At 11:06 PM 29/08/2004 -0700, you wrote:

>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?

The knowledgebase links you found explain it. You don't see it in MySQL
because MySQL doesn't have referential integrity support or transactions.
Its ability to protect itself from assault on its metadata is pretty flimsy.

To put it simply, you can't change the metadata of an object if it is in use.

[...]

>But still I'm unable to understand the exact reason
>for the Firebird to have this constraint.

Firebird metadata is stored in tables inside the database; changing
metadata changes the data stored in these tables. Since objects that are
using that metadata have to hold a consistent view of it during a
transaction, a metadata update will be refused if it affects any
"interesting" transactions involving the affected objects and their
dependencies. ("interesting" = any transaction that is in any state except
Committed).

You think that opening a table and looking at its data is not "accessing"
it. But, yes, it is "accessing" it. Everything you do in Firebird happens
in transactions. So, if one transaction has selected some data from an
object, that object, and any objects dependent on it, are in use. That
situation won't change until that transaction is committed.

Many of the third-party tools, by default, run their "browser-style"
queries in transactions that are never subjected to a "hard" commit. This
mechanism, known as "Commit Retaining", commits work but doesn't release
the objects involved.

>The other databases we used like MYSQL , Oracle supports this functionality.

Apples and oranges. MySQL doesn't implement transaction isolation or
referential integrity, so online metadata changes are possible (and so is
the resulting database corruption...) Oracle and Firebird do have
transactions and RI. Oracle supports deferred metadata changes under some
conditions and blocks new transactions involving objects that have pending
deferred metadata changes; Firebird (mostly) doesn't supported deferred
metadata changes (for stored procedures it can, under some conditions).

> I believe this will add more value to firebird to support this
> behavior. Firebird team: is there any plan to support this

Deferred metadata changes have been considered from time to time. It's not
a priority currently...nice to have for some, perhaps, but it's difficult
to see any great "value" in it. It's not in the plans for Firebird 2 or 3,
that I'm aware of.

> or is there any workaround to support multiconnection meta data
> update?.

No. Keep metadata updates separated from user work and all will be sweet.

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

Search the knowledgebases for "transaction isolation" and "referential
integrity"; also "multi-generational architecture", which will help
understanding of how it all hangs together. For someone not aware of them,
another keyword to search would be "dependencies".

./heLen