| Subject | Re: [firebird-support] Lock conflict on no wait transaction |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-10-14T12:39:11Z |
At 11:50 AM 14/10/2004 +0100, you wrote:
down the server?
You do need to have exclusive access in order to create a foreign key. To
get that, you have to put the database into a "shutdown state". That
doesn't mean shutting down the server.
As SYSDBA, apply gfix -shut to the database, with the required switch
option and timeout (read up on these in the Ops Guide or The Firebird Book)
to get everyone to log out. Two softer options are available to use if the
users are cooperative and you don't have any rogue queries
running; otherwise you can use the -force switch to kick them off after a
timeout period. Once the database is in the shutdown state, the owner or
sysdba can then log in and create the foreign key. Once it's done, use
gfix -online to once again enable other users to log in.
./heLen
>Hi,Are you getting confused between shutting down the database and shutting
>
>We currently have a problem altering the database structure in our
>firebird databases. We're running Firebird 1.5 classic on a Redhat
>Enterprise 3 server.
>
>More specifically, the problem occurs when attempting to add foreign
>key constraints to a table - even when the table has just been created
>- the server complains that the table is in use. In the past we've
>dealt with the problem by shutting down all database clients and
>performing the task with only the SYSDBA user logged in, but yesterday
>we found ourselves in a situation where we were not able to do this.
>This *may* have been due to some client remaining logged in, but due
>to an apparent shortcoming in the server we were unable to confirm (or
>disprove) this.
>
>The database we're administering is critical to our business and is in
>constant use by around 10 clients, so shutting it down is not
>something we're keen to do. Ideally we'd like a solution which allows
>us to add and edit new tables while other clients are using other
>parts of the database. Failing that, is there any way that we can get
>the database into a state where we can be sure that only one user is
>logged in, so that we can make changes?
down the server?
You do need to have exclusive access in order to create a foreign key. To
get that, you have to put the database into a "shutdown state". That
doesn't mean shutting down the server.
As SYSDBA, apply gfix -shut to the database, with the required switch
option and timeout (read up on these in the Ops Guide or The Firebird Book)
to get everyone to log out. Two softer options are available to use if the
users are cooperative and you don't have any rogue queries
running; otherwise you can use the -force switch to kick them off after a
timeout period. Once the database is in the shutdown state, the owner or
sysdba can then log in and create the foreign key. Once it's done, use
gfix -online to once again enable other users to log in.
./heLen