Hi,

At April 28, 2004, 08:52, Martijn Tonies wrote:

> Hi,

>> >> Thanks once again for your reply. I'm still unable to understand the
>> >> rational behind the behavior. Let me try with the direct firebird
>> >> ("paid") support and see if I can gather more details in this regard.
>>
>> > It was some kind of design decision. It undoubtely has to do
>> > with multi-versioning and concurrency etc etc of records. And
>> > therefor, simply: safety reasons.
>>
>> Here's what I found by doing a search in the Firebird Lists on IB
>> Phoenix:
>>
>> >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."

> Disagreed -- when creating a FK, you will get the "object in use"
> as well, if there are more than 1 connections to the db. Even without
> a SELECT on the tables involved.

I agree with you. One of my findings(that I just remembered) was that
I got the error "object in use" when creating a second FK on a second
table. Here's the example:

ALTER TABLE1
ADD CONSTRAINT FOREIGN KEY (FK_Field)
REFERENCES MASTER_TABLE(PK_Field);
ALTER TABLE2
ADD CONSTRAINT FOREIGN KEY (FK_Field)
REFERENCES MASTER_TABLE(PK_Field);

Trying to execute these 2 statements in the same transaction will
generate the "object in use" error. I know that with FB 1.0(haven't
tried it with FB 1.5), that even committing the first statement before
executing the second would cause an "object in use" error when
executing the second statement(although it is in another transaction).
Here I needed to disconnect and then reconnect to execute these 2 DDL
statements properly(and there is only one connection at a time,
otherwise it might likely fail). The same thing happens when trying to
drop foreign keys(and here too I don't know if it's better in FB 1.5).

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)