How do I get an exclusive lock on a firebird datab

Question

To be more specific, I am using firebird 2.1 and the DDEX Provider for visual studio, and I'm working in c#.

I have a situation where I'm trying to apply schema changes to the database from c# in an effort to "update" my database. During this process I get the following exception from firebird :

FirebirdSql.Data.FirebirdClient.FbException: unsuccessful metadata update object INDEX is in use

I've interpreted this as a concurency issue where there is another processess accessing the database at the same time. I do not know this is the cause for certian, but it "seems" the most likely case. I thought it might be related to the deleting and adding of constraints, as in they are not addable because the constraint is not correct, but I am able to run the commands on my local system without error, just not on the client site. At any rate, I currently have a number of commands wrapped into one transaction using isolation level "Serializable", and commit them all at once. As this is an upgrade, the thinking is it can block all other users as needed.

Example:

// note connection is pre-defined as a FbConnection, connected to the Database in question

FbTransaction transaction = Connection.BeginTransaction( IsolationLevel.Serializable );

// quite a bit of stuff gets done here, this is a sample
// I can run all the commands in this section in the isql tool and commit them all at once
// without error.  NOTE: I have not tried to run them all on the client enviroment, and likely can't

string commandString = "ALTER TABLE Product DROP CONSTRAINT ProductType;";
FbCommand command = new FbCommand(commandString, Connection, transaction);
command.ExecuteNonQuery();

commandString = "ALTER TABLE Product ADD CONSTRAINT ProductType " +
                "FOREIGN KEY ( TypeID ) REFERENCES Type ( TypeID ) " +
                "ON UPDATE CASCADE ON DELETE NO ACTION;";
command.CommandText = commandString;
command.ExecuteNonQuery();

// other commands include: 
// creating a new table
// creating 3 triggers for the new table

// commit the transaction
// this particular line actually "seems" to throw the exception mentioned

transaction.Commit();

My thought was to try and use the "manual" way of specifying a transaction to perhaps get more exclusive access to the tables, but I can't seem to get it to work as I don't understand what will and will not work together.

Example:

// Try to use FbTransactionOptions instead
// this statement complains about invalid options block when executing
FbTransaction transaction = Connection.BeginTransaction(
            FbTransactionOptions.Consistency |
            FbTransactionOptions.Exclusive |
            FbTransactionOptions.Wait |
            FbTransactionOptions.Write |
            FbTransactionOptions.LockWrite |
            FbTransactionOptions.NoRecVersion
            );

Anyway, my question is, how do I get exclusive access to the db to perform these updates? I'd almost like to be able to kick everyone off, and do them. Help and suggestions are much appricated!!!

---

New information: I was able to bring the data to my local, and now the error is showing as:

FirebirdSql.Data.FirebirdClient.FbException: violation of FOREIGN KEY constraint "INTEG_72" on table "TYPE"

Which is clear, so I'll fix this one and try it on site.

---

That seems to have fixed it.
So, in summary, I got an exception on a client system say:

FirebirdSql.Data.FirebirdClient.FbException: unsuccessful metadata update object INDEX is in use

I brought the data to my local system and got a different exception:

FirebirdSql.Data.FirebirdClient.FbException: violation of FOREIGN KEY constraint "INTEG_72" on table "TYPE"

Which was indeed a foreign key constraint violation. I was able to correct the update program to include a correction to the data and the client site updated properly. For some reason, I seemed to recieve an incorrect inital exception on the client site.

---

Note: I also accepted an answer in this thread by jachguate as he provided what I believe is the correct answer to my original question.

Answer 1

1. Unplug the network cable
2. Execute the transaction
3. Plug in the network cable

Answer 2

You can get exclusive access on the database for maintenance by shutting it down using the gfix command line tool (you can call it from your c# program, or perform all the maintenance using another tool, for example a batch script executed on the server).

From Database Startup and Shutdown

Database Shutdown If there is maintenance work required on a database, you may wish to close down that database under certain circumstances. This is different from stopping the Firebird server as the server may well be running other databases which you do not wish to affect. The command to close a database is:

gfix -shut OPTION TIMEOUT database_name

The TIMEOUT parameter is the time, in seconds, that the shutdown must complete in. If the command cannot complete in the specified time, the shutdown is aborted. There are various reasons why the shutdown may not complete in the given time and these vary with the mode of the shutdown and are described below. The OPTION parameter is one of the following: * -at[tach] - prevents new connections. * -tr[an] - prevents new transactions. * -f[orce] - simply aborts all connections and transactions. When a database is closed, the SYSDBA or the database owner can still connect to perform maintenance operations or even query and update the database tables.

Since firebird 2.0, you can also specify the stateof the database after shutdown:

For example

gfix -shut single -force 60 mydatabase.fdb

will disconnect all active users after 60 seconds, afterwards the database will allow only one connection for sysdba or database owner.