Subject Re: [firebird-support] Lock conflict on no wait transaction
Author Dan Taylor
Wasn't confused about shutting down the server, I didn't expect to be
able to maintain a database with the power turned off ;o) My point
was that shutting down the database and shutting down the server, in
our case, have the exact same effect on our business, as both make the
database unavailable! Not to worry though, if we have to shut down
the database in order to do what we want to do, then we'll just have
to schedule that in and get on with it.

Anyway, thanks for your comments. We managed to shut down the
database with gfix, something we've tried already, and ended up in a
state where we were unable to connect to the database because it is
shut down. Both IBO Console and isql on the local machine (logging in
as SYSDBA) refuse to allow access to the database because it is shut
down. Are we using the wrong tools?


On Thu, 14 Oct 2004 22:39:11 +1000, Helen Borrie <helebor@...> wrote:
> At 11:50 AM 14/10/2004 +0100, you wrote:
> >Hi,
> >
> >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?
> Are you getting confused between shutting down the database and shutting
> 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
> Yahoo! Groups Links

Dan Taylor

Software Development Engineer, JTL Systems Ltd
PhD Student, Reading University, UK