db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: can't delete my DB directory
Date Thu, 10 Jun 2010 07:12:51 GMT
AFAIK, when auto-commit is off, transaction demarcation is left (almost)
entirely to the user.  You should commit() the connection, or close() any
Statements/ResultSets.  What a database does to handle "unfinished"
transactions (even read-only ones) at shutdown is likely highly DB-specific
and not part of the spec.  While I think Derby should not hang or leave open
resources after shutdown, I wouldn't count on it.

In the same way that an OS will usually close all open file handles owned by
a process when that process exits, it is better if your program cleans up
after itself and closes resources explicitly rather than relying on some
implicit behavior.

In that vein, given that you have turned auto-commit off, the last thing
your program should do before shutdown is commit() or rollback().  In
auto-commit mode, the end of one transaction implies the beginning of the
next.  So if you've run *any* SQL on a connection, that connection should be
rolled back or committed at some point.

-Brett

On Thu, Jun 10, 2010 at 3:48 PM, oldmhe <mhe1943-oldnabble@yahoo.com> wrote:

>
> Thanks for the info.
>
> Though this is my first Java DB program.  I've written many C programs
> using
> ESQL, and have never needed to issue a commit after a read or query
> operation.
>
> Is this "commit after query" requirement an SQL concept, or is it just
> related to the Java JDBC API?
>
>
> Brett Wooldridge-2 wrote:
> >
> > Even read operations create transactions (and locks).  Because you set
> > autocommit to false, you must manually commit.
> >
> > Having said that, I would expect shutdown to automatically rollback
> > any open transactions at the time of shutdown.
> >
> > Brett
> >
> > Sent from my iPhone
> >
> > On Jun 9, 2010, at 10:32, oldmhe <mhe1943-oldnabble@yahoo.com> wrote:
> >
> >>
> >> The OS is Windows XP.
> >>
> >> Since I'm using the embedded driver, I don't think it's possible to
> >> check
> >> what process is hanging on to the file.  I.e., I believe it's the same
> >> process as my program.  When my program exits, I'm able to delete the
> >> directory manually.
> >>
> >> Regarding your last question, my program does shutdown explicitly
> >> (as shown
> >> in my original post).
> >>
> >> Since posting, I found a solution, but I don't understand why it
> >> works.
> >>
> >> Below is a simplification of what the program does:
> >>
> >> 1.     load the embedded driver
> >>        set AutoCommit to false
> >>        connect to the DB engine, and create a DB
> >>        create tables
> >>        load the tables with data, and commit all work
> >>
> >> 2.     using SELECT, read some data records, and create an output
> >> file.
> >>
> >> 3.     shutdown the DB engine
> >>
> >> 4.     try to delete the DB directory (and all files and
> >> subdirectories)
> >>        exit
> >>
> >> With regard to my initial post, Step 4 fails to delete all the files
> >> and
> >> directories (it's able to delete most of them).
> >>
> >> However:
> >>
> >> 1.  If I omit Step 2 (the reading of the DB), Step 4 succeeds.
> >>
> >> 2.  Or, if I add "xxx.commit()" between Step 2 and Step 3, then Step 4
> >> succeeds.
> >>
> >> It seems that a commit() is needed even though Step 2 makes no
> >> change to the
> >> DB.  This is contrary to my expectations for two reasons:
> >>
> >> a)  Since Step 2 is a read-only operation, I don't see why commit() is
> >> needed.
> >>
> >> b)  Even if a commit() is needed, the shutdown should release all DB
> >> resources (and not hang on to any files).
> >>
> >> Any thoughts?
> >>
> >>
> >> Kristian Waagan-4 wrote:
> >>>
> >>> Hello,
> >>>
> >>> What operating system are you using?
> >>> Are you able to use the operation system's proper tool to check which
> >>> process (if any) is hanging on to the file?
> >>> (i.e. pfiles or lsof)
> >>>
> >>> Also, do you see the same behavior if you in addition shut down the
> >>> database explicitly?
> >>> (i.e. 'DriverManager.getConnection
> >>> ("jdbc:derby:myDB;shutdown=true");')
> >>>
> >>>
> >>> Regards,
> >>> --
> >>> Kristian
> >>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >> --
> >> View this message in context:
> >>
> http://old.nabble.com/can%27t-delete-my-DB-directory-tp28782490p28825037.html
> >> Sent from the Apache Derby Users mailing list archive at Nabble.com.
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/can%27t-delete-my-DB-directory-tp28782490p28839396.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Mime
View raw message