db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert J. Carr" <rjc...@gmail.com>
Subject locking problem
Date Thu, 02 Jul 2009 03:55:46 GMT
super short summary:

I'm getting this exception when trying to run an update:

"a lock could not be obtained within the time requested"

Looking around various forums it seems I might have a transaction that
didn't complete, but I can't track it down.  Any suggestion on how to
do so?  Is there a way to get a list of all the (preferably pending)
transactions?

slightly more details:

I've used this page quite a bit for help, and it gave me some hints,
but nothing conclusive:

http://wiki.apache.org/db-derby/LockDebugging

Derby is being used in an embedded environment and it is single-threaded.

Using the derby.locks.deadlockTrace it says there is a WAIT lock on a
table.  After the wait time it seems to resolve itself and continues
to work as expected.  After it finishes, checking the lock table again
(using select * from syscs_diag.lock_table) there are only GRANT locks
(however, checking the table before the problem there are no entries
in the table).

gory details:

I have an application where I parse a number of files in a folder and
put the contents into a database.  As time goes on new files may be
added to this folder, and some time later I rescan the folder and
reenter the data (relying on uniqueness constraints to avoid duplicate
data).  If I get a uniqueness exception I do an update on the row that
was already in the database.  Here's what's happening ...

If my folder initially has 10 files all of the data goes in fine.
Some time later, my folder might have 20 files, and it is rescanned.
The first 9 files are rescanned and then updated when there's a
duplicate without problem.  Then, the 10th file blocks on this lock, a
minute later I get the exception, and then the remaining 10 files are
successfully entered into the database.

If I instead had 5 files initially and 10 files later, it would
instead block on rescanning the 5th file.  It *always* blocks on the
*last* previously scanned file.  All previously scanned files are
updated without a problem, and all unscanned files are then
successfully scanned.

However, after the initial scan the lock table is empty ... there are
no lingering locks whatsoever.

The failed insert and subsequent update (attempt) are using the same
connection.  Auto commit is set to true.

Any ideas?

Thanks-
Robert

Mime
View raw message