| Subject | Re: [IBO] Receiving an Object In Use error when adding an index to a table |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-05-07T14:16:44Z |
At 08:10 AM 7/05/2009, you wrote:
While we're about it, don't use TIBOTable, either. It's there for compatibility only, a place for people to "park" their TTable objects in a conversion from Paradox, Access or whatever and the BDE. For a transactional database, you must learn SQL.
Although it's not the issue here, in real databases, there is a thing called SQL privileges. You write user apps for ordinary users, i.e., those which don't have privileges to change the metadata of objects. You elect a specialised user to be the Owner of the database and you create both the database and all the objects in it as that owner-user. All other users are ordinary users and they can't change the owner's metadata.
"Unprepare" belongs to a statement (in your example, the first statement is SELECT * FROM YOURTABLE which has a linked INSERT INTO YOURTABLE (<COLUMNLIST>) VALUES (<VALUELIST>) while the second is CREATE INDEX WHATEVER ON YOURTABLE(SOMETHING). All statements have to be prepared; unpreparing is not commonly something you need to do (although you can and there are situations where you might want to). But Unprepare has no effect on the transaction: the work has already been posted and remains pending until the transaction is completed.
Helen
>Hi Everyone. I'm hoping someone can help me understand why I'mDon't use AddIndex() for real databases! It was designed for desktop databases that don't have transaction control over metadata.
>getting a "lock conflict on no wait transaction.....object XXX is in
>use" error when I'm trying to add an index to a table.
While we're about it, don't use TIBOTable, either. It's there for compatibility only, a place for people to "park" their TTable objects in a conversion from Paradox, Access or whatever and the BDE. For a transactional database, you must learn SQL.
> I have aThe "lock conflict on no wait transaction" happens because the first transaction has posted changes that were not committed when the second transaction started. "Object in Use" errors appear when you try to alter the metadata of an object that is in use. ;-) In this case, adding an index alters the metadata of the owning table.
>short contrived example that demonstrates this issue. Basically...I
>have a TIBODatabase object that is shared by two TIBOTable objects
>which reference the same table in my database. I insert a record
>using the first table object and free the object. Then...in a
>separate transaction, I attempt to create an index on the same table
>using my second TIBOTable object. When I try to commit the
>transaction I get the error "lock conflict on no wait transaction
>unsuccessful metadata update object XXXXX is in use".
>If I use anLook at the default transaction settings in each case: Isolation, Wait/NoWait, and also RecVersion if you are using ReadCommitted isolation. Autocommit is also important: you get it by default with the TDataset model (TIBODatabase) and with it a dreadful trick called CommitRetaining, a dreadful legacy from Borland.
>TIB_Connection object instead of a TIBODatabase object this error
>does not occur.
>As I understand it...we should never mix DDL and DMLAmong other horrors, CommitRetaining starts a new transaction immediately on the Commit (the AutoCommit in this case) and your entire table remains an object in use. By default, the default transaction created internally by TIB_Connection is not Committed with CommitRetaining. Also, StartTransaction starts the transaction *explicitly* and overrides Autocommit, even if it is set.
>in the same transaction to avoid issues of this sort. I believe I am
>following that convention here...but...still getting errors.
Although it's not the issue here, in real databases, there is a thing called SQL privileges. You write user apps for ordinary users, i.e., those which don't have privileges to change the metadata of objects. You elect a specialised user to be the Owner of the database and you create both the database and all the objects in it as that owner-user. All other users are ordinary users and they can't change the owner's metadata.
>I have also tried calling the table1 "Unprepare" and "FreeServerResources"Once you have posted changes to the database, they remain pending until you either commit their transaction or roll it back. All you've done is abandoned uncommitted work. That remains true for about 4 hours (depending on how your network is configured), after which the database engine will try to rollback any abandoned transactions.
>methods before freeing it but they have not helped in this case.
"Unprepare" belongs to a statement (in your example, the first statement is SELECT * FROM YOURTABLE which has a linked INSERT INTO YOURTABLE (<COLUMNLIST>) VALUES (<VALUELIST>) while the second is CREATE INDEX WHATEVER ON YOURTABLE(SOMETHING). All statements have to be prepared; unpreparing is not commonly something you need to do (although you can and there are situations where you might want to). But Unprepare has no effect on the transaction: the work has already been posted and remains pending until the transaction is completed.
>Can anyone shine some light on what I'm doing wrong ?If you're going to work with Firebird or InterBase it's really important to understand transactions and how to manage them; it's absolutely essential to learn SQL and the importance of parameters; and you need to throw out any illusions you have that IB/Fb are in any way like Paradox, Access, dBase, MySQL, etc. Hit the Tech Info sheets at the IBO website; and also avail yourself of the examples in your IBO installation. There's also a concept book available, the Getting Started guide, a.k.a. GSG. It's quite old; but the concepts haven't changed for 22 years. ;-)
Helen