db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francois Orsini <francois.ors...@gmail.com>
Subject Re: locking problem
Date Fri, 03 Jul 2009 00:05:31 GMT
Hi Robert,

Your application is not setting some particular transaction isolation level
_explicitly_ by any chance?

http://db.apache.org/derby/docs/dev/devguide/cdevconcepts15366.html

The default *value for derby.locks.waitTimeout* property is 60 seconds.
http://db.apache.org/derby/docs/dev/devguide/cdevconcepts16400.html

--francois

On Thu, Jul 2, 2009 at 11:08 AM, Robert J. Carr <rjcarr@gmail.com> wrote:

> Hi Jeff-
>
> It initially happened in 10.3.2.1, but I just updated to 10.5.1.1 and
> the same problem exists.
>
> And I may have mentioned in the original post it was a table lock, and
> I don't know if I misread it initially or if something changed when I
> updated derby, but it seems to be a row lock.  Here's a condensed
> version of the lock trace:
>
> java.sql.SQLException: A lock could not be obtained within the time
> requested.  The lockTable dump is:
> XID       |TYPE   |MODE |LOCKCOUNT |LOCKNAME   |STATE |TABLENAME
> ----------------------------------------------------------------
> *** The following row is the victim ***
> 5222      |ROW    |X    |0         |(2,38)     |WAIT  |SG_TRACKS
> *** The above row is the victim ***
> 5104      |ROW    |S    |1         |(2,38)     |GRANT |SG_TRACKS
> 5104      |TABLE  |IS   |1         |Tablelock  |GRANT |SG_TRACKS
> 5222      |TABLE  |IX   |2         |Tablelock  |GRANT |SG_TRACKS
> ----------------------------------------------------------------
>        at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
>        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
>        at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
>        at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
>        at
> org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
>        at
> org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
>        at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>        at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
> Source)
>        at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
> Source)
>
> Thanks-
> Robert
>
> On Thu, Jul 2, 2009 at 11:02 AM, Jeff Stuckman<stuckman@umd.edu> wrote:
> > Hello Robert,
> >
> > What version of Derby are you using?
> >
> > Jeff
> >
> > -----Original Message-----
> > From: Robert J. Carr [mailto:rjcarr@gmail.com]
> > Sent: Wednesday, July 01, 2009 11:56 PM
> > To: Derby Discussion
> > Subject: locking problem
> >
> > 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