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 Re: locking problem
Date Fri, 03 Jul 2009 06:54:58 GMT
Hi Francois-

Nope, there are no explicit isolation levels.  Everything is about as
vanilla (basic, default) as possible.

I'm not sure why you mention the wait timeout, but yes, it takes 60
seconds to raise the exception, then it finishes the rest of the work
normally and without problem.

I should also mention there are no locking issues in mysql.  It
doesn't mean it is a derby bug, in fact, I'm reasonably sure derby is
showing me a symptom of a problem, just that mysql never gets stuck on
a lock.

Thanks-
Robert

On Thu, Jul 2, 2009 at 5:05 PM, Francois
Orsini<francois.orsini@gmail.com> wrote:
> 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