Copying an idle derby database that is currently open

[David Sitsky <si...@nuix.com>]

Hi all,

I know this scenario is not recommended, but a couple of our customers
had accidentally copied some Derby databases which were still opened
by a program, however they claim the program had been idle for quite
some time, so there should not have been any active transactions.

I am curious to understand if this is true, is it still possible for
database corruption to occur in the copied versions?  If so, why?  I
can only guess Derby might be doing some background maintenance tasks
(for example, a checkpoint?), and that somehow copying the files
produced inconsistencies since files were changing as they were being
copied, but I am only guessing.

I wish we had the option (at least under Windows) to set the files
open by Derby to be opened "exclusively", so that nobody can even open
them for reading (and thus copy them).  I guess if you "freeze" the
database, you'd have to remove these exclusive locks until the
database is thawed.  A shame we can't do this in Java, or does NIO2
provide this?

-- 
Cheers,
David

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia    Ph: +61 2 9280 0699
Web: http://www.nuix.com                            Fax: +61 2 9212 6902

Re: Copying an idle derby database that is currently open

[Knut Anders Hatlen <Kn...@Sun.COM>]

On 04/ 9/10 06:40 AM, David Sitsky wrote:
> Hi all,
>
> I know this scenario is not recommended, but a couple of our customers
> had accidentally copied some Derby databases which were still opened
> by a program, however they claim the program had been idle for quite
> some time, so there should not have been any active transactions.
>
> I am curious to understand if this is true, is it still possible for
> database corruption to occur in the copied versions?  If so, why?  I
> can only guess Derby might be doing some background maintenance tasks
> (for example, a checkpoint?), and that somehow copying the files
> produced inconsistencies since files were changing as they were being
> copied, but I am only guessing.
>   

If you know for a fact that there is no thread executing queries, and
that there is no checkpoint or background writer running (Derby's
checkpoints and background writers are triggered by write activity, not
by the clock, so after a sufficiently large idle period they shouldn't
be running), then the copied files should give you a consistent snapshot
of the database. However, the only way to enforce that a running
database is in a state where it's safe to copy it, is to call the
SYSCS_FREEZE_DATABASE system procedure first. Also, since copying a
database that's not frozen is not a supported or documented use case,
future changes in the implementation of the engine may change these
assumptions with no notice.

-- 
Knut Anders