Re: locking problem

> 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@...> 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@...> wrote:
>> > Hello Robert,
>> >
>> > What version of Derby are you using?
>> >
>> > Jeff
>> >
>> > -----Original Message-----
>> > From: Robert J. Carr [mailto:rjcarr@...]
>> > 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
>> >
>> >
>> >
>
>